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

The error message is pretty clear, isn't it? You have a row with parent_id = 0 but no row with cat_id = 0. — user330315
– user330315, Commented Jun 29, 2013 at 10:00
@My-Name-Is WOW!, haven't you done a tree structure in a Relational data approach, this is because a fk is required here — user2511414
– user2511414, Commented Jun 29, 2013 at 10:14

Ihor Romanchenko · Accepted Answer · 2013-06-29 10:45:24Z

6

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.

answered Jun 29, 2013 at 10:45

Ihor Romanchenko

29k9 gold badges56 silver badges45 bronze badges

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

Comments

wildplasser · Accepted Answer · 2013-06-29 14:01:34Z

-- 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
        ;

CodZilla · Accepted Answer · 2013-09-18 04:59:52Z

1

This is quite simple.
Here the foreign key parent_id refers to cat_id.
Here a record with parent_id=0 exists but not a record with cat_id=0.

edited Sep 18, 2013 at 4:59

answered Sep 16, 2013 at 6:21

CodZilla

1511 gold badge1 silver badge10 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related