Re: TR: redundant constraint_schema

On 09/01/2018 09:27 AM, Andreas Kretschmer wrote:
>
>
> Am 01.09.2018 um 17:50 schrieb Olivier Leprêtre:
>> I notice that a new constraint "table1_col2_fkeyxxx" is created each
>> time the previous ALTER TABLE ADD COLUMN is called
>
> smells like a bug.

Yeah, a quick test on a database where I have an event trigger:

create table fk_parent(col2 varchar primary key);
NOTICE: Table public.fk_parent created
NOTICE: caught CREATE TABLE event on 'public.fk_parent'
NOTICE: caught CREATE INDEX event on 'public.fk_parent_pkey'

create table fk_child(col1 varchar references fk_parent(col2));
NOTICE: Table public.fk_child created
NOTICE: caught CREATE TABLE event on 'public.fk_child'
NOTICE: caught ALTER TABLE event on 'public.fk_child'

\d fk_child
Table "public.fk_child"
Column | Type | Collation | Nullable | Default
--------+-------------------+-----------+----------+---------
col1 | character varying | | |
Foreign-key constraints:
"fk_child_col1_fkey" FOREIGN KEY (col1) REFERENCES fk_parent(col2)

alter table fk_child add column if not exists col1 varchar references
fk_parent(col2);

NOTICE: column "col1" of relation "fk_child" already exists, skipping

NOTICE: caught ALTER TABLE event on 'public.fk_child'

ALTER TABLE

\d fk_child
Table "public.fk_child"

Column | Type | Collation | Nullable | Default

--------+-------------------+-----------+----------+---------

col1 | character varying | | |

Foreign-key constraints:

"fk_child_col1_fkey" FOREIGN KEY (col1) REFERENCES fk_parent(col2)

"fk_child_col1_fkey1" FOREIGN KEY (col1) REFERENCES fk_parent(col2)

>
> Regards, Andreas
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com