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;
