Re: 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(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of Matthew Wilson
> Sent: Monday, April 14, 2008 12:36 PM
> To: pgsql-general(at)postgresql(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general