(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?
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) );