Enforce unique values across two tables

Question

Is it possible to enforce uniqueness across two tables in MySQL?

I have two tables, both describing users. The users in these tables were for two different systems previously, however now we're merging our authentication systems and I need to make sure that there are unique usernames across these two tables. (it's too much work to put them all into one table right now).

Answer 1

Obviously if there are already duplicates in the two tables you will have to solve that problem by hand. Moving forward, you could write a trigger that checks both tables to see if the value already exists, and then apply that to both tables.

Answer 2

I don't know MySQL but this is how you can do it in Oracle and I believe MySQL does support materialized views too.

You create a materialized view on those two tables. And you add a unique constraint on this view.

This view needs to be refreshed every time a change to one of the two base tables is committed.

Answer 3

You could add an extra table with a single column as a primary key. Then create a trigger on each of your old user tables to insert the id into this extra table.

create table users1 (
    user_id integer primary key,
    username varchar(8) not null unique
);
create table users2 (
    user_id integer primary key,
    username varchar(8) not null unique
);
create table all_usernames (
    username varchar(8) primary key
);
create trigger users1_insert before insert on users1 for each row
    insert into all_usernames values(new.username);
create trigger users2_insert before insert on users2 for each row
    insert into all_usernames values(new.username);
create trigger users1_update before update on users1 for each row
    update all_usernames set username = new.username
    where username = old.username;
create trigger users2_update before update on users2 for each row
    update all_usernames set username = new.username
    where username = old.username;
create trigger users1_delete before delete on users1 for each row
    delete from all_usernames where username = old.username;
create trigger users2_delete before delete on users2 for each row
    delete from all_usernames where username = old.username;

You can then populate the table with

insert into all_usernames select username from users1;
insert into all_usernames select username from users2;

Answer 4

Maybe not direct answer to your quwestion, but:

You should reconsider rewriting your code and db to unite those two tables into one.

The design you are trying to enforce now will complicate your code and db schema and it will make any futrther upgrade to oter db or framework harder.

Answer 5

Would changing the type of the ID column be affordable? Then you could go for GUIDs which would be unique across as many tables as you want.

Answer 6

You can't declare a UNIQUE constraint across multiple tables, and MySQL does not support CHECK constraints at all. But you can design a trigger to search for the matching value in the other table. Here's a test SQL script:

DROP TABLE IF EXISTS foo;
CREATE TABLE FOO (username VARCHAR(10) NOT NULL);

DROP TABLE IF EXISTS bar;
CREATE TABLE BAR (username VARCHAR(10) NOT NULL);

DROP TRIGGER IF EXISTS unique_foo;
DROP TRIGGER IF EXISTS unique_bar;

DELIMITER //

CREATE TRIGGER unique_foo BEFORE INSERT ON foo
FOR EACH ROW BEGIN
  DECLARE c INT;
  SELECT COUNT(*) INTO c FROM bar WHERE username = NEW.username;
  IF (c > 0) THEN
    -- abort insert, because foo.username should be NOT NULL
    SET NEW.username = NULL;
  END IF;
END//

CREATE TRIGGER unique_bar BEFORE INSERT ON bar
FOR EACH ROW BEGIN
  DECLARE c INT;
  SELECT COUNT(*) INTO c FROM foo WHERE username = NEW.username;
  IF (c > 0) THEN
    -- abort insert, because bar.username should be NOT NULL
    SET NEW.username = NULL;
  END IF;
END//

DELIMITER ;

INSERT INTO foo VALUES ('bill');  -- OK

INSERT INTO bar VALUES ('bill');  -- Column 'username' cannot be null

You also need similar triggers ON UPDATE for each table, but you shouldn't need any triggers ON DELETE.