17.2. Complex Constraints via CHECK() and CASE Constraints
This problem was posted on a newsgroup by Patrick L. Nolan at Stanford University He has a small database with the following business rules
Every person in the database is uniquely defined by a single key, their user_id.
Everyone is assigned a job category—call them A, B, and X.
Everyone in job category X has a supervisor, who must be in either job category A or job category B.
Let’s assume that nobody can be their own supervisor.
This is a minimal set of rules that we expect to become more and more complex over time.
One proposal was to divide job category X into two categories; call them XA and XB, respectively. All the XA people would have A supervisors, and all the XB people would have ...
Get Joe Celko's Thinking in Sets: Auxiliary, Temporal, and Virtual Tables in SQL now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
