Question 90027 in PostgreSQL Question posted in
The official documentation can be found here.

Unique constraint across multiple postgres tables – Postgresql

Let’s say I have the following two postgres tables with the same field:

CREATE TABLE table1 (
    label VARCHAR(50)
);

CREATE TABLE table2 (
    label VARCHAR(50)
);

I want label to be unique across both tables. That is, the following data should raise an error:

INSERT INTO table1 (label) VALUES ('hello');
INSERT INTO table2 (label) VALUES ('hello');

Is there any way to enforce this at the database level?

Tags:

2

Answers


  1. 0

    You cannot create a unique constraint across table, but a trigger on both tables can. One way: (see demo)

      create or replace function table1_table2_cross_check()
      returns trigger 
     language plpgsql
    as $$
    begin
        if tg_table_name = 'table1' 
        then 
           if exists (select null 
                        from table2 
                       where label = new.label
                     )
           then 
              raise exception 'Executing: % table1, Label Value: ''%'', already exists in table2',tg_op,new.label;
           end if;
       else 
           if exists (select null 
                        from table1  
                       where label = new.label
                     )
           then 
              raise exception 'Executing: % table2, Label Value: ''%'', already exists in table1',tg_op,new.label;
           end if;
       end if; 
       return new; 
    end;
    $$;

create trigger table1_biur
   before insert or update 
   on table1
   for each row 
       execute procedure table1_table2_cross_check();
       
create trigger table2_biur
   before insert or update 
   on table2
   for each row 
       execute procedure table1_table2_cross_check();
    Login or Signup to reply.
  2. 0

    This is an example where a third table and some rules could be very beneficial:

    CREATE TABLE table1(label TEXT UNIQUE);

CREATE TABLE table2(label TEXT UNIQUE);

CREATE TABLE register(label TEXT UNIQUE); -- single source of truth, unique

CREATE RULE unique_register_1 AS ON INSERT TO table1
    DO ALSO
        INSERT INTO register VALUES (NEW.label); -- protected by the unique constraint
        
CREATE RULE unique_register_2 AS ON INSERT TO table1
    DO ALSO
        INSERT INTO register VALUES (NEW.label); -- protected by the unique constraint

    This also works when you have concurrent inserts on both tables, even when you have long running transactions.

    This would be one of the very few exceptions where I would recommend the usage of a RULE.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search