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

Question

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".

Answer 1

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.