Re: BUG #17247: How to avoid crating multiple Foreign keys on same column on same table.

On Tue, Oct 26, 2021 at 9:07 AM PG Bug reporting form <
noreply(at)postgresql(dot)org> wrote:

>
> Issue: Postgres engine allows multiple FK on same columns for a same
> table.This cause duplication and unwanted use of space . If we accidentally
> run a script multiple times it creates the FK mulitple times.
> Ran the following statement 3 times : to add a foreign key
> ALTER TABLE admin.emp
> ADD FOREIGN KEY (deptno)
> REFERENCES admin.dept (deptno)
> ON DELETE NO ACTION;
>
> I see 3 its created thrice . In Oracle this statement should fail. Is there
> any way this restriction can be applied on PG.
> admin emp_deptno_fkey emp deptno admin dept deptno
> admin emp_deptno_fkey1 emp deptno admin dept deptno
> admin emp_deptno_fkey2 emp deptno admin dept deptno
>
> Please advice.
>
> Using a named constraint can prevent this:

ALTER TABLE admin.emp ADD CONSTRAINT emp_deptno_fkey FOREIGN KEY (deptno)
REFERENCES admin.dept (deptno) ON DELETE NO ACTION;

Regards,

Juan José Santamaría Flecha