Recursive Foreign Key Issue

Question

In my DB design, I need a table with recursive foreign key relation i.e. the foreign key refers to the same table. When I try it with one column it works fine, but when I use two columns it gives an error. Below is the sample code and the resulting error. Your help will be highly appreciated.

CREATE TABLE categories (
categoryID integer ,
parentID integer ,
setID integer REFERENCES categories(categoryID,parentID),
name char(255) NOT NULL,
PRIMARY KEY(categoryID,parentID)
);

ERROR: number of referencing and referenced columns for foreign key disagree

When I use

setID integer REFERENCES categories(categoryID) and 
PRIMARY KEY(categoryID) 

then it gives no error, but that's not what I want.

Answer 1

You probably want this:

CREATE TABLE categories (
    categoryID integer,
    parentID integer,
    setID integer,
    name char(255) NOT NULL,
    PRIMARY KEY (categoryID, parentID),
    FOREIGN KEY (categoryID, parentID) REFERENCES categories(categoryID, parentID)
);

Consult the manual about the syntax.

Answer to follow up question in comment:

CREATE TABLE categories (
    categoryID integer,
    parentID integer,
    setID integer,
    name char(255) NOT NULL,
    PRIMARY KEY (categoryID, parentID),
    UNIQUE (setID, parentID) 
    FOREIGN KEY (setID, parentID) REFERENCES categories(setID, parentID)
);

The target of a foreign key needs some kind of uniqueness constraint. I quote the manual:

The referenced columns must be the columns of a non-deferrable unique or primary key constraint in the referenced table.

Answer 2

Yeah, unfortunately, you are trying to say that one numeric field should be equivalent to two numeric fields. Not really going to happen.

Think about your use of SetID. Do you really need it?

Instead, ensure that parentID is a foreign key of categoryID ( i.e. express that any value of parentID HAS to exist as a a corresponding and existing value in parentID ).