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

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.

posted about 4 years ago

CC BY-SA 4.0

ghost-in-the-zsh‭

703 reputation 8 12 88 45

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).

posted about 4 years ago

CC BY-SA 4.0

Alexei‭

5003 reputation 112 101 690 486