Is it possible to create a Unique (Index) constraint across multiple tables?
I think, it is not possible by directly creating a constraint. But there are some solutions in order for you to get unique IDs.
One suggestion would be is by using TRIGGER
. Create a BEFORE INSERT trigger on both tables which checks the ID for existence during INSERT
or UPDATE
.
Second, is by creating a third table which contains UNIQUE
numbers and the other tables: TableA
and TableB
will then be reference on the third one.
Like JW says, this would probably work well with using a third table. In MySQL, you can use identity fields to make this easier. A very simple example would be using these tables (simple versions of the tables with just names without knowing any further info):
CREATE TABLE a ( `id` int, `name` varchar(100), PRIMARY KEY(`id`) ) ENGINE = INNODB;CREATE TABLE b ( `id` int, `name` varchar(100), PRIMARY KEY(`id`) ) ENGINE = INNODB;CREATE TABLE c ( `id` int auto_increment, `intable` varchar(10) not null, PRIMARY KEY(`id`) ) ENGINE = INNODB;
Then, when you want to insert a value on either table, do (a sample inserting 'Joe' into a):
INSERT INTO c (`intable`) VALUES ('a');INSERT INTO a (`id`, `name`) SELECT LAST_INSERT_ID() AS id, 'Joe' AS name;
The only reason for the intable entry in c is so you know which table it was created for. Any sort of value to insert into c can be used instead.