PostgreSQL nullable Foreign Key

Question

I am creating a postgreSQL table that has a foreign key that references itself, so this is a structure that looks like 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 the ParentID is automatically set to NOT NULL , and therefore there cannot be any root in this tree. How to make it null?

Answer 1

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

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

Documentation for integer and serial data types is available here: http://www.postgresql.org/docs/current/static/datatype-numeric.html