(If it matters this is postgreSQL and I am not very experienced with SQL)
Say I have 3 tables:
User, Department and UserDept (a user can be a member of more than one department). Partial tables shown below
The department manager should be a member of the department and I want that enforced by the DB server but the complication here is the fact that a user can belong to more than one department .
One way to so that is to allow any user to be made department manager, and then if they are not already a member of the department the should server auto create an entry in the UserDept table… But I am not sure how to do that. If there is a straight forward to do that way please let me know.
The more obvious way would be to require that the user first be required to be made a member of the department, then one could just have the user picked from the department members as defined in the UserDept table…See he partial table definitions below:
But then what happens if the User is deleted to the ManagerKey field? I would just want ManagerKey set to NULL (which is straight forward for a single field foreign key reference)… I certainly Don’t want the Department Table Primary key set to NULL , the record deleted or have an invalid manager key stick around!
From reading docs and looking at on-line examples I am not sure how how to do this… Does anybody know how to handle this?
Thanks
-Karen
CREATE TABLE IF NOT EXISTS UserTable(
Key0 SERIAL PRIMARY KEY,
FirstName TEXT NOT NULL,
LastName TEXT NOT NULL);
CREATE Department(
Key0 SERIAL PRIMARY KEY,
Name TEXT NOT NULL,
ManagerKey INTEGER ,
FOREIGN KEY (ManagerKey, Key0) REFERENCES UserDept( UserKey, DeptKey) ON DELETE SET NULL);
CREATE TABLE IF NOT EXISTS UserDept(
UserKey INTEGER NOT NULL REFERENCES UserTable(Key0) ON DELETE CASCADE,
DeptKey INTEGER NOT NULL REFERENCES Department(Key0) ON DELETE CASCADE,
PRIMARY KEY (UserKey, DeptKey) );