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

When presenting the list of supervisors, you'd write something like
this:

select * from supervisors where supervisors.company_id = ?

....and then pass in the current employee's company_id value. If you
don't have that on hand you could do:

select * from supervisors where supervisors.company_id = (select
company_id from employees where employee_id = ?)

....passing the current employee's id (or whatever you called it).

What does this choice mean, though? If you're saying the employee's
supervisor_id is being filled in by each employee, and you want to
make sure at the database end that they pick a supervisor from their
company, you could add a check constraint like

ALTER TABLE employees
ADD CONSTRAINT "supervisor_from_company_of_employee" CHECK
(reasonable_supervisor(employee_id));

....assuming of course that you already had some function
reasonable_supervisor() that would look something like:

CREATE OR REPLACE FUNCTION reasonable_supervisor(emp integer)
RETURNS boolean AS
$BODY$
select (company_id = (select company_id from supervisors where
supervisors.supervisor_id = employees.supervisor_id)) from employees
where employee_id = $1;

$BODY$
LANGUAGE 'sql' VOLATILE

Hope that helps,
Kev

On Apr 14, 1:36 pm, m...@tplus1.com (Matthew Wilson) wrote:
> 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-gene...@postgresql.org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general