Thread: 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
keys?

TIA

Matt

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
> keys?
>
> TIA
>
> Matt
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general