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%20Diaz's gravatar image

J Diaz
830253144
accept rate: 14%

edited 29 May '12, 14:18

Breck%20Carter's gravatar image

Breck Carter
26.2k430599862

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"
permanent link

answered 29 May '12, 14:29

Breck%20Carter's gravatar image

Breck Carter
26.2k430599862
accept rate: 20%

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.

permanent link

answered 30 May '12, 04:11

Volker%20Barth's gravatar image

Volker Barth
30.8k308456665
accept rate: 32%

edited 30 May '12, 05:41

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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • 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:

×9

question asked: 29 May '12, 13:44

question was seen: 1,026 times

last updated: 30 May '12, 07:41