Adding Foreign key constraint without checking existing data in Postgres

Question

ALTER TABLE <table name> WITH NOCHECK
    ADD CONSTRAINT attachments_user_id_fkey FOREIGN KEY (user_id)
    REFERENCES public.users (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;

The above query is throwing the following error ERROR: syntax error at or near "WITH NOCHECK"

Possible duplicate of Facing error while Altering table in PostgreSQL — hungrykoala
– hungrykoala, Commented Jun 19, 2018 at 0:56

mu is too short · Accepted Answer · 2018-06-18 23:43:10Z

5

That appears to be SQL Server syntax. PostgreSQL doesn't support that WITH NOCHECK, I think you want:

ALTER TABLE <table name>
ADD CONSTRAINT attachments_user_id_fkey FOREIGN KEY (user_id)
REFERENCES public.users (id) NOT VALID

You'll have to check the documentation to see if there are equivalents for the rest of the options you're trying to use.

answered Jun 18, 2018 at 23:43

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Adding Foreign key constraint without checking existing data in Postgres

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related