Wednesday, 21 August 2013

MySQL preventing insert with foreign key

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