Adding a NOT VALID foreign key in Postgres

Question

Example schema: http://sqlfiddle.com/#!1/3d410

I've already got a table and I want to add a new, not valid foreign key to the table. What's the correct syntax for adding a NOT VALID foreign key?

CREATE TABLE junks (
  id serial PRIMARY KEY,
  name text
);

CREATE TABLE trunks (
  id serial PRIMARY KEY,
  name text
  -- no fk
);

-- and the below does not work!

--ALTER TABLE trunks ADD junk serial REFERENCES junks(id) NOT VALID;

So what's wrong with the NOT VALID option you found yourself? — user330315
– user330315, Commented Dec 20, 2012 at 12:56

Clodoaldo Neto · Accepted Answer · 2012-12-20 13:13:56Z

4

You first add the column:

alter table trunks add column junk serial;

Then add the constraint to the table:

alter table trunks add constraint the_constraint_name
    FOREIGN KEY (junk)
    REFERENCES junks (id)
    not valid;

edited Dec 20, 2012 at 13:13

answered Dec 20, 2012 at 13:06

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

1 Comment

Rafs May 19 at 10:54

How to alter an already added and valid constraint?

Robert Rohm · Accepted Answer · 2012-12-20 13:22:10Z

1

This works:

ALTER TABLE trunks ADD CONSTRAINT FK_junk_id 
  FOREIGN KEY (id) 
  REFERENCES junks(id) 
  NOT VALID
;

See, e.g.: http://www.postgresql.org/docs/devel/static/ddl-alter.html#AEN2758

edited Dec 20, 2012 at 13:22

answered Dec 20, 2012 at 13:12

Robert Rohm

3411 silver badge9 bronze badges

Collectives™ on Stack Overflow

Adding a NOT VALID foreign key in Postgres

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related