Communities

Writing
Writing
Codidact Meta
Codidact Meta
The Great Outdoors
The Great Outdoors
Photography & Video
Photography & Video
Scientific Speculation
Scientific Speculation
Cooking
Cooking
Electrical Engineering
Electrical Engineering
Judaism
Judaism
Languages & Linguistics
Languages & Linguistics
Software Development
Software Development
Mathematics
Mathematics
Christianity
Christianity
Code Golf
Code Golf
Music
Music
Physics
Physics
Linux Systems
Linux Systems
Power Users
Power Users
Tabletop RPGs
Tabletop RPGs
Community Proposals
Community Proposals
tag:snake search within a tag
answers:0 unanswered questions
user:xxxx search by author id
score:0.5 posts with 0.5+ score
"snake oil" exact phrase
votes:4 posts with 4+ votes
created:<1w created < 1 week ago
post_type:xxxx type of post
Search help
Notifications
Mark all as read See all your notifications »
Q&A

Welcome to Software Development on Codidact!

Will you help us build our independent community of developers helping developers? We're small and trying to grow. We welcome questions about all aspects of software development, from design to code to QA and more. Got questions? Got answers? Got code you'd like someone to review? Please join us.

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

+5
−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.

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

0 comment threads

2 answers

+4
−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.

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

0 comment threads

+2
−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).

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

0 comment threads

Sign up to answer this question »