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.