Suppose I have the table
CREATE TABLE ABC.PersonsManagers ( PersonId INTEGER NOT NULL, ManagerId INTEGER NOT NULL, AssignmentBeginDate TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP, AssignmentEndDate TIMESTAMP NULL DEFAULT NULL PRIMARY KEY ( PersonId, ManagerId, AssignmentBeginDate ) );
and I want to enforce the following integrity constraints
1) AssignmentEndDate if not null is always >= AssignmentBeginDate
2) A PersonId is always assigned a current Manager (AssignmentEndDate is null)
Please explain what you mean by "2) A PersonId is always assigned a current Manager (AssignmentEndDate is null)"
Here's a solution for "1) AssignmentEndDate if not null is always >= AssignmentBeginDate"...
CREATE TABLE PersonsManagers ( PersonId INTEGER NOT NULL, ManagerId INTEGER NOT NULL, AssignmentBeginDate TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP, AssignmentEndDate TIMESTAMP NULL DEFAULT NULL, PRIMARY KEY ( PersonId, ManagerId, AssignmentBeginDate ), CONSTRAINT "PersonsManagers.AssignmentEndDate should be NULL or >= AssignmentBeginDate" CHECK ( COALESCE ( AssignmentEndDate, AssignmentBeginDate ) >= AssignmentBeginDate ) ); INSERT PersonsManagers VALUES ( 1, 1, '2012-01-01', NULL ); -- OK INSERT PersonsManagers VALUES ( 2, 2, '2012-01-01', '2012-01-01' ); -- OK INSERT PersonsManagers VALUES ( 3, 3, '2012-01-01', '2012-01-02' ); -- OK INSERT PersonsManagers VALUES ( 4, 4, '2012-01-01', '2011-12-31' ); -- not OK Constraint 'PersonsManagers.AssignmentEndDate should be NULL or >= AssignmentBeginDate' violated: Invalid value in table 'PersonsManagers' SQLCODE=-1091, ODBC 3 State="23000"
answered 29 May '12, 14:29
As to the second question (and Breck's question):
In my understanding, this is really a "relation" - some kind of "history of assignments between employees and managers", and there must be mostly (or exactly?) one "active" relation per employee - i.e. one with no AssignmentEndDate.
Personally, I would solve this by a trigger approach: Say, you can only "INSERT" active relations, and the former "active one" will be set to inactive by using the new one's AssignmentBeginDate as its AssignmentEndDate.
Or I would even put the active manager in the Employee table (to guarantee each Employee has a manager - if that rule holds) and use a table like PersonsManagers to log the history - and would then expect begin and end date as NOT NULL. And I would fill this history table automatically by a trigger (and reject direct DML...).
Besides these suggestions (taken from a similar construct we have used for many years):
To validate a rule that relates to several rows, you can use a CHECK CONSTRAINT with a subquery, such as
CHECK( NOT EXISTS (SELECT COUNT(*) FROM PersonsManagers WHERE AssignmentEndDate IS NULL -- AND PersonID = ... GROUP BY PersonId HAVING COUNT(*) > 1) )
CAVEAT: Using such a query (that groups over the whole table) may have an inappropriate performance penalty and/or may scale not well. - AFAIK, I have only used TABLE CHECK CONSTRAINTS for checks on the current row. - Possibly one can limit the check to be done on the current PersonId - I just don't know how to specify that when selecting against the same table.