MySQL preventing insert with foreign key
How might you prevent INSERTs or UPDATEs in MySQL if one value in another
table matches another, but the value itself isn't a key? The answer must
be a FOREIGN KEY constraint but I'm not sure exactly how it works with
multiple columns as well as non-key fields.
My own situation is this:
Given this table USERDATA with these values:
ID | USER_ID | UUID
1 29 aaa
2 29 bbb
3 30 ccc
and this table RELATIONS with these values after INSERT, where
userdata_uuid refs uuid and receiver_id refs user_id
ID | USERDATA_UUID | RECEIVER_ID
1 aaa 29
2 aaa 30
3 bbb 29
4 bbb 30
5 ccc 29
6 ccc 30
how can I prevent an insert that depicts a user having a relation to their
own userdata? With this data, the desired result after an INSERT on
RELATIONS would be:
ID | USERDATA_UUID | RECEIVER_ID
1 aaa 30
2 bbb 30
3 ccc 29
Hope this makes sense, thanks.
No comments:
Post a Comment