Notifications
Mark all as read
Q&A

Is it possible in MySQL to require each row in a table have at least one foreign key record in a join table?

+4
−0

I have tables A and B and then I have a many to many join table with foreign keys to both called a_b. Neither foreign key can be null and the combinations for the foreign keys to A and B are unique.

In the business logic for the program, every record in the A table must have a relationship to at least one record in the B table.

Is it possible to enforce that constraint at the database level? I don't think it is, but thought I would ask.

Why does this post require moderator attention?
You might want to add some details to your flag.
Why should this post be closed?

0 comments

2 answers

+3
−0

If I understand your question correctly, i.e.

Is it possible to enforce that constraint at the database level?

Then the short answer is: No.

In the business logic for the program, every record in the A table must have a relationship to at least one record in the B table

Since you already a M:M relation between tables A and B through an associative table C, you can only require that an entry in C already has valid PKs in tables A and B. However, there's no way (AFAIK) to cause entry in A to depend on an entry in B or vice versa , unless the relation between A and B is set to be 1:M. In other words, you'd have to go from:

+---+      +---+      +---+
| A |1----*| C |*----1| B |
+---+      +---+      +---+

to something like:

+---+          +---+
| A |*-----1..*| B |
+---+          +---+

where * by itself means "0 or more" and 1..* means "1 or more".

If you want the above, but without changing the original M:M relation, you may need to look at either using database triggers (what I'd look into first) or implementing it in the actual application code (which I'd try to avoid).

If there's no choice but to write it in the application layer code, I'd at least put it in a library/module that can be easily reused across the entire application, to prevent this implementation issue from propagating uncontrollably; it is technical debt.

Why does this post require moderator attention?
You might want to add some details to your flag.

0 comments

+1
−0

ghost-in-the-zsh‭ provided a direct answer and I will try to offer a more general complementary one.

One possible thing to try is a trigger that checks the integrity, but it cannot work for inserts because the order must be something along the lines:

START TRANSACTION
INSERT INTO B
INSERT INTO A
INSERT INTO AxB
COMMIT

Since A's records must be checked, a trigger for A would make sense, but it would fire too early. A trigger on AxB would not prevent simple changes in A that do not connected to any B record.

For more complex validations one way is to ensure then from the application layer since it seems that the data is always changed by it. Integrity is ensured by the proper use of transactions and automatic testing.

As a safety net, a job might periodically check this integrity (this technique is used the data volume is big enough to make constraints checking quite expensive).

Why does this post require moderator attention?
You might want to add some details to your flag.

0 comments

Sign up to answer this question »