sql plus table constraints

I'm an SQL Plus novice and have come across the following problem.

I would appreciate it if someone could help me or let me know if there is a more appropriate forum for such a query.

I have 3 tables Employee, Department and Role.

In the Department Table I have the Department Manager Social Security No in a column called MgrRSI. This column is a foreign key to the RSI column (Primary Key) in the Employee table.

Also within the Employee table I have a column called RoleID which is a foreign key to the column entitled RoleNo of the Role table. In other words, each employee has a role to play which is described in the Role table.

In short, each department has a manager who is an employee. Each employee has a role number e.g. 1 for administrator, 2 for doctor etc.

I want to set up a constraint that checks that the MgrRSI number entered by the user relates to an employee who is a doctor. Or, to avoid having to involve a third table in the query, simply to ensure that the MgrRSI relates to an employee who does role no 2.

I've tried doing all sorts of check constraints but keep getting errors. I've seen in some versions of SQL you can do a Create Assertion command that looks like it would do the trick but it doesn't seem to recognise it in SQL Plus.

Please help!!

Lisa

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sun Dec 03 2000 - 06:01:36 CST