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.
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!
nameproperty belongs to the abstract entity and it's UNIQUE there.