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?

Answer 1

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