pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17247: How to avoid crating multiple Foreign keys on same column on same table.
2+ messages / 2 participants
[nested] [flat]
* BUG #17247: How to avoid crating multiple Foreign keys on same column on same table.
@ 2021-10-25 23:29 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2021-10-25 23:29 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: nbipin29@gmail.com
The following bug has been logged on the website:
Bug reference: 17247
Logged by: bipsy Nair
Email address: nbipin29@gmail.com
PostgreSQL version: 13.4
Operating system: Any OS
Description:
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #17247: How to avoid crating multiple Foreign keys on same column on same table.
@ 2021-10-26 08:31 Juan José Santamaría Flecha <juanjo.santamaria@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 0 replies; 2+ messages in thread
From: Juan José Santamaría Flecha @ 2021-10-26 08:31 UTC (permalink / raw)
To: nbipin29@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Tue, Oct 26, 2021 at 9:07 AM PG Bug reporting form <
noreply@postgresql.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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2021-10-26 08:31 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-10-25 23:29 BUG #17247: How to avoid crating multiple Foreign keys on same column on same table. PG Bug reporting form <noreply@postgresql.org>
2021-10-26 08:31 ` Juan José Santamaría Flecha <juanjo.santamaria@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox