How to add a foreign key constraint to the same table using ALTER TABLE in PostgreSQL

Question

To create a table, I use:

CREATE TABLE category ( cat_id serial NOT NULL, cat_name character varying NOT NULL, parent_id integer NOT NULL, CONSTRAINT cat_id PRIMARY KEY (cat_id) ) WITH ( OIDS=FALSE ); ALTER TABLE category OWNER TO pgsql; 

parent_id is the identifier of another category. Now I have a problem: how to cascade a delete record with its children? I need to set parent_id as a foreign key for cat_id. I try this:

  ALTER TABLE category ADD CONSTRAINT cat_cat_id_fkey FOREIGN KEY (parent_id) REFERENCES category (cat_id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE 

But he falls from:

 ERROR: insert or update on table "category" violates foreign key constraint "cat_cat_id_fkey" DETAIL: Key (parent_id)=(0) is not present in table "category". 

Answer 1

You have a problem - what will be the parent_id category at the top of the hierarchy?

If it is null , it will break the NOT NULL construct.

If it is some arbitrary number, for example 0 - it will break the foreign key (as in your example).

The general solution is to reverse the NOT NULL construct to parent_id and set parent_id to null for the upper categories.

Answer 2

 -- create some fake data for testing -- DROP SCHEMA tmp CASCADE; CREATE SCHEMA tmp ; SET search_path=tmp; CREATE TABLE category ( cat_id serial NOT NULL, cat_name character varying NOT NULL, parent_id integer NOT NULL, CONSTRAINT cat_id PRIMARY KEY (cat_id) ); INSERT INTO category(cat_name,parent_id) SELECT 'Name_' || gs::text , gs % 3 FROM generate_series(0,9) gs ; -- find the records with the non-existing parents SELECT ca.parent_id , COUNT(*) FROM category ca WHERE NOT EXISTS ( SELECT * FROM category nx WHERE nx.cat_id = ca.parent_id ) GROUP BY ca.parent_id ; -- if all is well: proceed -- make parent pointer nullable ALTER TABLE category ALTER COLUMN parent_id DROP NOT NULL ; -- set non-existing parent pointers to NULL UPDATE category ca SET parent_id = NULL WHERE NOT EXISTS ( SELECT * FROM category nx WHERE nx.cat_id = ca.parent_id ) ; -- Finally, add the FK constraint ALTER TABLE category ADD CONSTRAINT cat_cat_id_fkey FOREIGN KEY (parent_id) REFERENCES category (cat_id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE ; 

Answer 3

It is pretty simple. Here the foreign key parent_id refers to cat_id .
Here an entry with parent_id=0 exists, but not an entry with cat_id=0 .