CODEWITHSUNDEEP
mysqlinsertforeign-keys
Callum Jenkins
Callum Jenkins

Reputation: 103

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.

Upvotes: 0

Views: 343

Answers (1)

peterm
peterm

Reputation: 92815

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.

Upvotes: 1

Related Questions