How do I make sure that an employee and supervisor belong to the same company?

You can have a two column foreign key.

create table employee
(id int primary key not null,
company_id int not null,
supervisor_id int);

alter table employee add unique (id, company_id);

alter table employee add foreign key (supervisor_id, company_id)
references employee (id, company_id);

Jon

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
owner@postgresql.org] On Behalf Of Matthew Wilson
Sent: Monday, April 14, 2008 12:36 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] How do I make sure that an employee and supervisor
belong to the same company?

I have an employees table and one column in the employees table is
"supervisor_id" which is an FK to the id column.

I have employees from numerous companies all in the same table. I

have

a column called company_id that indicates the company.

I want to make sure that an employee chooses a supervisor from the

same

company. I have a column called company_ID. How do I make sure that
the employee company ID matches the supervisor's company ID?

Do I need to use a trigger or is there a way I can do this with

foreign