title: How to add a foreign key constraint to same table using ALTER TABLE in PostgreSQL
body: <p>To create table I use:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>  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
  </code></pre>
  
  <p>But it falls with:</p>
  
  <pre><code>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".
  </code></pre>