MySQL preventing insert with foreign key

Question

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.

What columns in relations are non nullable?

peterm
– peterm

2013-08-22 02:00:01 +00:00
Commented Aug 22, 2013 at 2:00 — peterm
– peterm, Commented Aug 22, 2013 at 2:00
The ones I've posted are all non-nullable.

Callum Jenkins
– Callum Jenkins

2013-08-22 02:09:45 +00:00
Commented Aug 22, 2013 at 2:09 — Callum Jenkins
– Callum Jenkins, Commented Aug 22, 2013 at 2:09

peterm · Accepted Answer · 2013-08-22 02:14:57Z

You can achieve this with triggers.

CREATE TRIGGER tg_bi_relations BEFORE INSERT ON relations FOR EACH ROW SET NEW.userdata_uuid = IF(EXISTS( SELECT * FROM userdata WHERE uuid = NEW.userdata_uuid AND user_id = NEW.receiver_id), NULL, NEW.userdata_uuid); CREATE TRIGGER tg_bu_relations BEFORE UPDATE ON relations FOR EACH ROW SET NEW.userdata_uuid = IF(EXISTS( SELECT * FROM userdata WHERE uuid = NEW.userdata_uuid AND user_id = NEW.receiver_id), NULL, NEW.userdata_uuid);

The trick is to violate NOT NULL constraint if conditions for your check is met.

Here is SQLFiddle demo
Try to uncomment two last insert and update statements one at a time. Triggers won't let them to succeed.

Collectives™ on Stack Overflow

MySQL preventing insert with foreign key

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related