What is the best way to ensure that primary key and foreign key never intersect?

Question

Say I have a table with a primary key a_id and foreign key b_id.

It is important that a_id and b_id never intersect, i.e. it should never be the case that there exists an a_id = b_id.

What would be the best way to implement this? Could I somehow do this on the database end (mySql), or should this be ensured programmatically? If I ensure this programmatically, is it bad practice to insert a number for a primary key (rather than have the database autoincrement it)? I assume I would just create a routine that checks the latest primary key and simply increments it (and I would also need to ensure that the range of a_id never intersects the range of b_id). Any suggestions?

Answer 1

In Oracle you could implement this behavior using a sequence that was used to assign id's to rows in both tables. Each table would have a trigger on INSERT where the id would be set from the next number in the sequence.

Answer 2

You can set the AUTO_INCREMENT value such that one table has only odd numbers and the other has only evens.

This is likely to be very performant, but it doesn't leave room for adding a 3rd table with yet another unique key.

I'm not 100% sure about MySQL, but with Oracle you can define a sequence, then simply use that same sequence to select all of your values from, which is probably the best option (if it is available with mysql).

Answer 3

The easiest way to achieve this would be to use a CHECK constraint. Unfortunately, MySQL being MySQL, it doesn't support CHECK.

In order to achieve the same effect in MySQL, you will need to create a BEFORE INSERT and a BEFORE UPDATE trigger to make sure that both values are valid. The FK itself is in charge of making sure the relationship is valid. Here is an example:

CREATE TRIGGER upd_check BEFORE UPDATE ON sometable
FOR EACH ROW
BEGIN
    IF NEW.a_id = NEW.b_id THEN
        call ERROR_SELFREFERENCING_ID();
    END IF;
END;

More information about MySQL TRIGGERS are available in the MySQL Manual:

18.3.1: Trigger Syntax

EDIT: MySQL doesn't currently support RAISE or SIGNAL in their triggers, so I have to resort to calling a non-existent procedure ERROR_SELFREFERENCING_ID() for it to fail. This will cause the INSERT or UPDATE to fail if a_id = b_id, pretty much the same way if you would set an invalid b_id.

Answer 4

You could create another table that has the auto-increment field. Upon insertion of a record, it would insert into that "key table" and use the referenced values there. So if you have two globally unique keys in one table each insert would be two key inserts. This solution would scale beyond 2 as well.

But I have to ask: Why?

Answer 5

You could use GUIDs (UUIDs) as unique keys.

I have used this on several projects and it works well for uniqueness, although it isn't the best for index performance.