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

Question

Is there a way to create a unique index across tables in a MySQL database?

By unique, I mean,

                table A ids=1,2,5,7... 

                table B ids=3,4,6,8...

Answer 1

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.

Answer 2

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.