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.

Post History

75%
+4 −0
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?

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

posted 3y ago by ghost-in-the-zsh‭

Answer
#1: Initial revision by user avatar ghost-in-the-zsh‭ · 2020-11-09T23:08:49Z (over 3 years ago)
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.