Is it possible to create a Unique (Index) constraint across multiple tables? Is it possible to create a Unique (Index) constraint across multiple tables? database database

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.