I need to make a category tree where some leafs can have multiple parents, for exaple
g --b --a c --d --a
above category ‘a’ has two parents(g and c)
My first attempt was to create the following table
create table catalogcategories2 ( id serial, name text not null, pid int null, primary key(id,pid), CONSTRAINT catalogcategories_fk FOREIGN KEY (pid) REFERENCES catalogcategories2(id) );
where both parent id and id are primary keys but I get the following error
there is no unique constraint matching given keys for referenced table “catalogcategories2”
Some reflections, a separation of tree structure and content is most likely the way to go. I.e. remove name from catalogcategories2. Now nodes that don’t have a parent does not have to exist in this table. I.e. you can make pid not null.
The current foreign key is invalid since the primary key which it references consists of two columns.
CREATE TABLE CATEGORIES ( ID ... NOT NULL PRIMARY KEY , NAME ... NOT NULL , <additional attributes> ); CREATE TABLE CATEGORY_DAG --  ( ID ... NOT NULL , PID ... NOT NULL , PRIMARY KEY (ID, PID) , FOREIGN KEY (ID) REFERENCES CATEGORIES (ID) , FOREIGN KEY (PID) REFERENCES CATEGORIES (ID) );
 What you describe is a Direct Acyclic Graph (DAG), not a tree.
First, tree is a oriented graph where node have no more then one parent. Your structure is a network, also widely known as bill of materials. So you need separate Category to Category table. Kind of
create table catalogcategories2 ( id serial, name text not null ); create table cat2cat ( master_id int not null, -- upper cat sub_id int not null, -- included cat primary key(master_id,sub_id), CONSTRAINT catalogcategories_fk1 FOREIGN KEY (master_id) REFERENCES catalogcategories2(id), CONSTRAINT catalogcategories_fk2 FOREIGN KEY (sub_id) REFERENCES catalogcategories2(id) );
Because you set
(id, pid) as the primary key, the referencing column (pid) is not pointing to a unique item.
From postgresql doc:
A foreign key must reference columns that either are a primary key or form a unique constraint.
Try it like this:
create table catalogcategories2 ( id serial, name text not null, pid int null, primary key(id), CONSTRAINT catalogcategories_fk FOREIGN KEY (pid) REFERENCES catalogcategories2(id) );
You probably will need an index on
pid as well for efficient lookup. See how your queries will perform and add if necessary.