I have a
CREATE TABLE companies ( id bigserial, name varchar(255) NOT NULL PRIMARY KEY (id) )
I want companies to have parents and childs. A company might have many parent and many child companies, so I created the following table:
CREATE TABLE parent_companies ( id bigserial, parent_company_id bigint, CONSTRAINT parent_companies_parent_company_id_fkey FOREIGN KEY (parent_company_id) REFERENCES companies(id), child_company_id bigint, CONSTRAINT parent_companies_child_company_id_fkey FOREIGN KEY (child_company_id) REFERENCES companies(id) PRIMARY KEY (id) )
I want parent and child to be unique, so I added the following constraint:
CREATE UNIQUE INDEX parent_companies_parent_company_id_child_company_id_index ON parent_companies (parent_company_id, child_company_id)
This will not allow the same parent and child to be created twice in the database. However, I would like to also prevent a child company being saved as a parent of its parent company. For example, I would to prevent this from happening:
-[ RECORD 1 ]-----+-------------------- id | 1 parent_company_id | 1 child_company_id | 2 -[ RECORD 2 ]-----+-------------------- id | 2 parent_company_id | 2 child_company_id | 1
Since in record 1 it's defined that company 1 is the parent of company 2, I want a way of preventing company 2 being recorded as parent of company 1.
I thought it could be the case of using the
EXCLUDE or the
CHECK constraints, but I couldn't figure out a way of making either work.