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:

insert into PC values(13, 1288, 500, 64, 10, '24x', 650);

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:

ALTER TABLE PC
ADD CONSTRAINT model_type
CHECK('PC' = (SELECT type FROM Product pr WHERE pr.model = pc.model));

On execution attempt of the (quite legitimate with respect to the SQL-92 standard) code above, we get the following error message:

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,

CREATE FUNCTION get_type(@model VARCHAR(50))
RETURNS VARCHAR(50)
AS
BEGIN
RETURN (SELECT type FROM Product WHERE model = @model)
END;

Now we add the constraint itself:

ALTER TABLE PC
ADD CONSTRAINT model_type CHECK('PC' = dbo.get_type(model));

After that, when trying to insert a printer model into the PC table, e.g.

insert into PC values(13, 1288, 500, 64, 10, '24x', 650);

we get the following error message:

However, a model of the appropriate type can still be added to the table:

insert into PC values(13, 1260, 500, 64, 10, '24x', 650);

I hope you won’t have any trouble specifying similar constraints for the remaining tables of this schema.