# Table Check Constraint Design Question

 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) asked 29 May '12, 13:44 J Diaz 830●26●31●44 accept rate: 14% Breck Carter 27.2k●460●624●897 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...) (30 May '12, 03:59) Volker Barth

 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 Breck Carter 27.2k●460●624●897 accept rate: 21% 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 showing 4 of 7 show all flat view
 As to the second question (and Breck's question): Um... still don't understand... what is X? 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. answered 30 May '12, 04:11 Volker Barth 31.7k●323●465●680 accept rate: 32% 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
 toggle preview community wiki:

By Email:

Markdown Basics

• *italic* or _italic_
• **bold** or __bold__
• image?![alt text](/path/img.jpg "title")
• numbered list: 1. Foo 2. Bar
• to add a line break simply add two spaces to where you would like the new line to be.
• basic HTML tags are also supported

Question tags:

×10

question asked: 29 May '12, 13:44

question was seen: 1,184 times

last updated: 30 May '12, 07:41