How to define unique constraint on multiple columns in multiple tables in postgres?

user_1748450 0
Viewed 81

I have some tables, let's say 2 for simplicity:

CREATE TABLE A (
    name varchar
    ...
);
CREATE TABLE B (
    name varchar
    ...
);

These tables have different structures but all have the same column (name)

I want to add a unique constraint on all of these columns in all of these tables.

How can I do this? This seems like a simple thing but my googling haven't proven successful so far. Most existing questions seem to deal with multiple columns in the same table.

postgresql sql
1 Answers

Since UNIQUE constraints cannot span multiple tables, you'll need to create an extra table to store all the names. Then each table will have a foreign key against the extra table.

For example:

create table all_names (
  zone int not null,
  name varchar(20) not null,
  constraint uq1 unique (zone, name),
  constraint uq2 unique (name) -- this is the critical constraint!
);

create table a (
  zone int not null default 1 check (zone = 1),
  name varchar(20) not null,
  constraint fk1 foreign key (zone, name) references all_names (zone, name)
);

insert into all_names (zone, name) values (1, 'Jenny'); -- succeeds
insert into a (name) values ('Jenny'); -- succeeds

create table b (
  zone int not null default 2 check (zone = 2),
  name varchar(20) not null,
  constraint fk2 foreign key (zone, name) references all_names (zone, name)
);

insert into all_names (zone, name) values (2, 'Ivan'); -- succeeds
insert into b (name) values ('Ivan'); -- succeeds

insert into all_names (zone, name) values (2, 'Jenny'); -- fails!
insert into b (name) values ('Jenny'); -- fails!

Note that each insert now requires an extra insert in the extra all_names table. This can, however, be automated (and happen behind the scenes) by the use of a pre/post-insert trigger (not shown).

See running example at DB Fiddle.

If you implement a trigger, then your inserts will look simple, as in:

insert into a (name) values ('Jenny'); -- succeeds
insert into b (name) values ('Ivan'); -- succeeds
insert into b (name) values ('Jenny'); -- fails!
user_64361910
Related