4

In MySQL, is there a way to specify that a.column cannot exist in b.column - a reverse foreign key?

In other words:

# Would not return any rows ever
SELECT * FROM a
INNER JOIN b ON a.column = b.column;

# Would fail
INSERT INTO a
SELECT * FROM b;

# Would not insert any rows
INSERT IGNORE INTO a
SELECT * FROM b;
Michael0x2a
  • 58,192
  • 30
  • 175
  • 224
Aistina
  • 12,435
  • 13
  • 69
  • 89

3 Answers3

3

No there is no such thing.

You would need to do that in a trigger:

DELIMITER $$

CREATE TRIGGER bi_a_each BEFORE INSERT ON a FOR EACH ROW
BEGIN
  DECLARE forbidden_key INTEGER;
  SELECT id INTO forbidden_key FROM b WHERE b.id = NEW.acolumn LIMIT 1;
  IF forbidden_key IS NOT NULL THEN 
    SELECT * FROM error_insertion_of_this_value_is_not_allowed;
  END IF;
END $$

DELIMITER ;
Johan
  • 74,508
  • 24
  • 191
  • 319
2

To a point, if you want "can be in A or B, but not both"

This is the "super key/sub type" pattern

Create a new table AB that has a 2 columns

  • SomeUniqueValue, PK
  • WhichChild char(1), limited to 'a' or 'b'

There is also a unique constraint on both columns

Then

  • Add a WhichChild column to tables A and B. In A, it is always 'a'. In B, always 'b'
  • Add foreign keys from A to AB and B to AB on both columns

Now, SomeUniqueValue can be in only A or B.

Note: in proper RDBMS you'd use check constraints or computed columns to restrict WhichChild to 'a' or 'b' as needed. But MySQL is limited so you need to use triggers in MySQL. However, this is simpler then testing table B for each insert in A etc

gbn
  • 422,506
  • 82
  • 585
  • 676
  • Very interesting approach! I assume one would then have to use an ON INSERT trigger to put values from A or B into AB? – Aistina Sep 23 '11 at 11:09
  • 1
    You don't have to use a trigger to guarantee that WhichChild always has the right character. You can use a foreign key constraint to a single-row table. (Each subtype table would have its own single-row table.) Code for one usage: http://stackoverflow.com/questions/4969133/database-design-problem/4970646#4970646 – Mike Sherrill 'Cat Recall' Sep 24 '11 at 01:08
  • @gbn: Right. The code I posted in the other SO question was for PostgreSQL; it uses CHECK constraints. – Mike Sherrill 'Cat Recall' Sep 24 '11 at 11:37
0

Try to create a trigger and throw an error from it.

Devart
  • 119,203
  • 23
  • 166
  • 186