|
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"
1
Alternatively, you could give the constraint an incomprehensible name that is useless at run time, as opposed to a name that tells the user what they should do to fix the problem: "PersonsManagers.AssignmentEndDate should be NULL or >= AssignmentBeginDate"
(29 May '12, 14:32)
Breck Carter
Replies hidden
99 out of 100 DBAs insist on incomprehensible names :)
(29 May '12, 14:33)
Breck Carter
By 2) I mean INSERT PersonsManagers VALUES ( 3, 3, '2012-01-01', '2012-01-02' ) would fail unless there already existed an INSERT PersonsManagers VALUES ( 3, X, '2012-01-02', NULL );
(29 May '12, 14:48)
J Diaz
Replies hidden
Thanks for the solution for 1. FYI, I don't use constraint names, partial to ASAXYZ :)
(29 May '12, 14:51)
J Diaz
Replies hidden
Um... still don't understand... what is X? Usually folks make PersonId the sole primary key column, and ManagerId is a nullable foreign key to PersonId (a fishhook)... a traditional hierarchy implementation in relational-land.
(29 May '12, 17:04)
Breck Carter
Yeah, it makes debugging more interesting, especially the 4AM phone call :)
(29 May '12, 17:05)
Breck Carter
1
FWIW, check constraints are only violated if they evaluate to FALSE, so the explicit "is null" treatment is not necessary: CHECK(AssignmentEndDate >= AssignmentBeginDateIn) would suffice as in case AssignmentEndDate is null, the expression returns UNKNOWN - which is no violation. (Though one surely can argument the NULL treatment should still be documented for reasons of comprehensibility...)
(30 May '12, 03:57)
Volker Barth
|
|
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. Yes X was supposed to be some other manager. These are all excellent suggestions. The trigger approach is preferred I think just need to verify the business rule. Thanks for the help. Jim
(30 May '12, 07:41)
J Diaz
|
Is there are reason you are using TIMESTAMP instead of DATE?
(I'm just asking whether the assignment rule has to respect the time part, as well...)