Subqueries in Check Constraints
We have already done a lot to make our relational model match the subject area. Still, there remain several consistency-violating issues. E. g., we can insert a model (say, 1288) declared as a printer in the Product table into the PC table:
Furthermore, nothing stops us from inserting this model into any of the descriptive tables – PC, Laptop, Printer.
Thus, we need a constraint preventing products of an inappropriate type in the child tables.
Let’s formulate a check constraint detecting the model type according to the Product table and comparing it with the type in the descriptive table. E. g., for the PC table such a constraint could be written as follows:
On execution attempt of the (quite legitimate with respect to the SQL-92 standard) code above, we get the following error message:
Subqueries are not allowed in this context. Only scalar expressions are allowed.
In other words, SQL Server doesn’t allow subqueries in CHECK constraints. Concerning implementation examples, this seems to be rather the rule than an exception. And as to MySQL, this DBMS doesn’t support CHECK constraints at all.
In SQL Server, using of user-defined functions (UDF) allows to bridge this gap. The trick works as follows.
Since, as evident from the error message, only scalar expressions are allowed in a CHECK constraint, we write a scalar-valued function that will receive the model number and return its type specified in the Product table. Then, we use this function in the constraint. So then,
Now we add the constraint itself:
After that, when trying to insert a printer model into the PC table, e.g.
The INSERT statement conflicted with the CHECK constraint "model_type". The conflict occurred in database "learn", table "dbo.pc", column 'model'.
However, a model of the appropriate type can still be added to the table:
I hope you won’t have any trouble specifying similar constraints for the remaining tables of this schema.