loading..
Русский    English
18:42

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:

  1. 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:

  1. ALTER TABLE PC
  2. ADD CONSTRAINT model_type
  3. 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:

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, 

  1. CREATE FUNCTION get_type(@model VARCHAR(50))
  2. RETURNS VARCHAR(50)
  3. AS
  4. BEGIN
  5. RETURN (SELECT type FROM Product WHERE model = @model)
  6. END;

Now we add the constraint itself:

  1. ALTER TABLE PC
  2. 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.

  1. INSERT INTO PC VALUES(13, 1288, 500, 64, 10, '24x', 650);
we get the following error message:

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:

  1. 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.

Bookmark and Share
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 exercise 32 More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100