Postgres Pro
Downloads
Home   >   mailing lists

Thread: Re: Creation of FK without enforcing constraint for existing data

Re: Creation of FK without enforcing constraint for existing data

From
"sivapostgres@yahoo.com"
Date:
Hello,

I need to create a Foreign Key for a table without enforcing the constraint for existing data.   Few orphan exists in existing data, which we plan to resolve it later.  

We use the following query to create the FK [ Which of course checks for the presence of record in referencing table]

ALTER TABLE public.table1
    ADD CONSTRAINT "constraint1_FK" FOREIGN KEY (field1)
    REFERENCES public.tabnle2(field2) MATCH SIMPLE
    ON UPDATE RESTRICT
    ON DELETE RESTRICT;

The facility NOT to check for the presence of the parent record is available in SQL Server.   Searching an equivalent option in PostGreSQL.  Is it available?   Else any other way to achieve this.

Happiness Always
BKR Sivaprakash

Re: Creation of FK without enforcing constraint for existing data

From
Gavan Schneider
Date:

On 2 Aug 2022, at 17:14, sivapostgres@yahoo.com wrote:

Hello,
I need to create a Foreign Key for a table without enforcing the constraint for existing data.   Few orphan exists in existing data, which we plan to resolve it later.  
We use the following query to create the FK [ Which of course checks for the presence of record in referencing table]

Try something like sql-altertable.html:

ALTER TABLE public.table1 ADD CONSTRAINT "constraint1_FK" FOREIGN KEY (field1) REFERENCES
public.tabnle2(field2) MATCH SIMPLE  ON UPDATE RESTRICT ON DELETE RESTRICT
DEFERRABLE  INITIALLY DEFERRED
;

The facility NOT to check for the presence of the parent record is available in SQL Server.   Searching an equivalent option in PostGreSQL.  Is it available?   Else any other way to achieve this.

No knowledge of SQL Server and how this is specified there, but the …DEFFER… syntax is according to the SQL standard

Regards

Gavan Schneider
——
Gavan Schneider, Sodwalls, NSW, Australia
Explanations exist; they have existed for all time; there is always a well-known solution to every human problem — neat, plausible, and wrong.
— H. L. Mencken, 1920