Thread: BUG #15670: alter table .. add column if not exists ... references ...; adds a FK constraint on each execution
BUG #15670: alter table .. add column if not exists ... references ...; adds a FK constraint on each execution
From
PG Bug reporting form
Date:
The following bug has been logged on the website: Bug reference: 15670 Logged by: Michael Binder Email address: michael@mibi.io PostgreSQL version: 11.2 Operating system: Debian 9.8 Description: Hi, I don't know if this is the expected behavior but when I execute this script: create table test1 ( id serial primary key, name text ); create table test2 ( id serial primary key ); alter table test2 add column if not exists test1_fk integer not null references test1(id); alter table test2 add column if not exists test1_fk integer not null references test1(id); alter table test2 add column if not exists test1_fk integer not null references test1(id); I end up with 3 FK constrains: select constraint_name from information_schema.key_column_usage where table_name='test2' and position_in_unique_constraint is not null; -- test2_test1_fk_fkey test2_test1_fk_fkey1 test2_test1_fk_fkey2 best regards, Michael Binder
Re: BUG #15670: alter table .. add column if not exists ... references ...; adds a FK constraint on each execution
From
Tom Lane
Date:
PG Bug reporting form <noreply@postgresql.org> writes: > I don't know if this is the expected behavior but when I execute this > script: > alter table test2 add column if not exists test1_fk integer not null > references test1(id); > alter table test2 add column if not exists test1_fk integer not null > references test1(id); > alter table test2 add column if not exists test1_fk integer not null > references test1(id); > I end up with 3 FK constrains: Yeah, this is the same problem previously reported at bug #15180, https://www.postgresql.org/message-id/flat/152509815280.19803.16118194452213577808%40wrigleys.postgresql.org We had a sketch for a fix but discussion seems to have trailed off :-( regards, tom lane
Re: BUG #15670: alter table .. add column if not exists ...references ...; adds a FK constraint on each execution
From
Amit Langote
Date:
On Tue, Mar 5, 2019 at 11:35 PM PG Bug reporting form <noreply@postgresql.org> wrote: > > The following bug has been logged on the website: > > Bug reference: 15670 > Logged by: Michael Binder > Email address: michael@mibi.io > PostgreSQL version: 11.2 > Operating system: Debian 9.8 > Description: > > Hi, > > I don't know if this is the expected behavior but when I execute this > script: > > create table test1 ( > id serial primary key, > name text > ); > > create table test2 ( > id serial primary key > ); > > alter table test2 add column if not exists test1_fk integer not null > references test1(id); > alter table test2 add column if not exists test1_fk integer not null > references test1(id); > alter table test2 add column if not exists test1_fk integer not null > references test1(id); I think the foreign key constraint creation (references test1(id) part) is executed independently of add column part, so the latter's no-op semantics due to the "if not exists" clause doesn't apply to foreign key creation. You would get duplicate constraints even if you had instead done the following: alter table test2 add column if not exists test1_fk integer not null; alter table test2 add foreign key (test1_fk) references test1(id); alter table test2 add foreign key (test1_fk) references test1(id); alter table test2 add foreign key (test1_fk) references test1(id); \d test2 Table "public.test2" Column | Type | Collation | Nullable | Default ----------+---------+-----------+----------+----------------------------------- id | integer | | not null | nextval('test2_id_seq'::regclass) test1_fk | integer | | not null | Indexes: "test2_pkey" PRIMARY KEY, btree (id) Foreign-key constraints: "test2_test1_fk_fkey" FOREIGN KEY (test1_fk) REFERENCES test1(id) "test2_test1_fk_fkey1" FOREIGN KEY (test1_fk) REFERENCES test1(id) "test2_test1_fk_fkey2" FOREIGN KEY (test1_fk) REFERENCES test1(id) In fact same thing happens when adding unnamed check constraints (like I did above when adding the foreign key constraint): alter table bar add check (a > 0); alter table bar add check (a > 0); alter table bar add check (a > 0); \d bar Table "public.bar" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- a | integer | | | Check constraints: "bar_a_check" CHECK (a > 0) "bar_a_check1" CHECK (a > 0) "bar_a_check2" CHECK (a > 0) Foreign-key constraints: "bar_a_fkey" FOREIGN KEY (a) REFERENCES foo(a) "bar_a_fkey1" FOREIGN KEY (a) REFERENCES foo(a) "bar_a_fkey2" FOREIGN KEY (a) REFERENCES foo(a) I don't know why Postgres doesn't try to recognize a duplicate constraint definition. Maybe the thinking is that users won't deliberately add the same constraint, but the resulting behavior as seen in the OP's example may surprise some. Thanks, Amit