How to add a foreign key constraint to same table using ALTER TABLE in PostgreSQL
alter-table, foreign-keys, postgresql
Solution
The problem you have - what would be the `parent_id` of a category at the top of the hierarchy?
If it will be `null` - it will break the `NOT NULL` constratint.
If it will be some arbitrary number like `0` - it will break the foreign key (like in your example).
The common solution - drop the `NOT NULL` constratint on the `parent_id` and set `parent_id` to `null` for top categories.
Problem
To create 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 a id to another category. Now I have a problem: how to cascade delete record with its children? I need to set parent_id as foreign key to 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 it falls with: ``` 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". ```