Monday, July 09, 2007

M359: Block 3 Ex 5.13 - Flawed?

IMHO the solution (page 211) provided for exercise 5.13 (page 117) of block 3 is flawed for a specific set of circumstances.

Scenario
If the textbook table has a number of text books recorded for a specific course prior to the constraint (solution 5.13) being added, then once the constraint is added no text books will be able to be INSERT'd or UPDATE'd for any course not just the course where the number of recorded text books exceeds 5.

Example
For the following table:
bookcode,title,                                 author_name,       no_pages,course_code
'65281', 'Beginning Syntax',                    'Kershaw, J.',     247,     'c2'
'12953', 'Grammatical Structures',              (NULL),            258,     'c2'
'63948', 'Interpreting Semantics',              'Hyde, E.',        (NULL),  'c4'
'73642', 'The Semantics of Romance Languages',  'Jane Hanley',     (NULL),  'c4'
'65284', 'A First Course In Logic',             'Jerry Maxwell',   115,     'c5'
'65285', 'More Logic',                          'Jerry Maxwell',   210,     'c5'
'65290', 'Even More Logic',                     (NULL),            (NULL),  'c5'
'65291', 'A First Course In Magic',             'Jerry Maxwell',   115,     'c5'
'65292', 'A First Course In Poker',             'Jerry Maxwell',   115,     'c5'
'65293', 'A First Course In Distilling Whiskey','Jerry Maxwell',   115,     'c5'
'38572', 'Practical Pragmatics',                'Christine Davies',320,     'c7'
'93881', 'Advances in Pragmatics',              'Farmer, S.J.',    (NULL),  'c7'

Note that course 'c5' has 6 text books recorded.

Now the constraint (solution 5.13) is added:
ALTER TABLE textbook
   ADD CONSTRAINT max_5_textbooks CHECK (
          5 >= ALL (SELECT COUNT(*)
                    FROM   textbook
                    GROUP BY course_code));

Both of the following queries will break the constraint with SQLAnywhere generating a "Constraint 'max_5'_textbooks' violated:" error message, even though the first query relates to course 'c2' which only has 2 text books recorded and IMO should not break the constraint:
INSERT INTO textbook
    VALUES ('65282', 'Ending Syntax', 'Jerry Maxwell', 115, 'c2');

UPDATE textbook
   SET title = 'A First Course In SQL'
 WHERE bookcode = '65291';

Solution
The following is my solution, which not only covers the requirements specified by the exercise but also for the above scenario:
ALTER TABLE textbook
  ADD CONSTRAINT max_5_textbooks CHECK (
         NOT (course_code =ANY(SELECT t.course_code
                               FROM   textbook t
                               GROUP BY t.course_code
                               HAVING COUNT(t.course_code)>= 5)));

For the above 2 queries (the INSERT and the UPDATE queries) the INSERT will work and the UPDATE will fail, which is as expected.

2 comments:

Anonymous said...

But a constraint should apply to all the data in the database, not just data added after the constraint has been imposed.

If there is existing data that violates the constraint that is about to be added then the constraint is meaningless until that data is made to comply with the constraint (or the original intent of the constraint is flawed).

Only after the data already in the table is made to satisfy the constraint should the constraint be added.

Ian M. said...

Yes I agree. The problem is that the course text did not make this to clear in block 3 when it talked about constraints.