4

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"

4

Some reflections, a separation of tree[1] 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.

A sketch:

CREATE TABLE CATEGORIES
(  ID ... NOT NULL PRIMARY KEY
,  NAME ... NOT NULL
,  <additional attributes> );

CREATE TABLE CATEGORY_DAG -- [1]
(  ID ... NOT NULL
,  PID ... NOT NULL
,    PRIMARY KEY (ID, PID)
,    FOREIGN KEY (ID) REFERENCES CATEGORIES (ID)
,    FOREIGN KEY (PID) REFERENCES CATEGORIES (ID)
);

[1] What you describe is a Direct Acyclic Graph (DAG), not a tree.

1

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)
);
0

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.

  • it still doesn't let me create a category with id 38 and pid 1005 because an entry with id 38 and pid 1000 exists, it throws this "duplicate key value violates unique constraint "catalogcategories2_pkey" Key (id)=(38) already exists." – SteveL Jun 3 '16 at 10:13
  • For that you need many-to-many relationship and not foreign key. To have more parents look at @Lennart's answer. – Károly Nagy Jun 3 '16 at 11:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.