PostgreSQL nullable Foreign Key

Question

I'm creating a postgreSQL table that has a foreign key that references itself, so it's a structure which is similar to a tree:

CREATE TABLE Person(
    ID serial PRIMARY KEY,
    Description text,
    Name varchar(5),
    ParentID serial,
    FOREIGN KEY (ParentID) REFERENCES Person(ID)
    );

The problem is that ParentID is automatically set to NOT NULL and therefore there can be no root in this tree. How do I make it nullable?

Craig Ringer · Accepted Answer · 2013-12-30 18:38:17Z

19

You probably want to change the type of your ParentID to an integer, which is the underlying type of serial.

CREATE TABLE Person(
    ID serial PRIMARY KEY,
    Description text,
    Name varchar(5),
    ParentID integer,
    FOREIGN KEY (ParentID) REFERENCES Person(ID)
);

The documentation for the integer and serial data type is available here: http://www.postgresql.org/docs/current/static/datatype-numeric.html

edited Dec 30, 2013 at 18:38

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

answered Dec 30, 2013 at 16:22

Erik Näslund

1,4261 gold badge14 silver badges10 bronze badges

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

1 Comment

Craig Ringer Over a year ago

To explain a little: serial is basically a macro that expands into integer default nextval('tablename_colname_seq') and creates a sequence owned by the table. You don't need or want that for a foreign key, it's only useful for auto-generated key columns. So use integer. If you inspect the base table with \dt Person you'll see that the id column is really integer after the table creation.

Collectives™ on Stack Overflow

PostgreSQL nullable Foreign Key

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related