ALTER TABLE statement

The following levels of check constraints can be distinguished:

  •  attribute (column) level,
  •  tuple (row) level,
  •  relation (table) level.

An attribute-level constraint is applied to a single column, in other words, this type of constraint refers to just one column of the table whose definition contains this constraint. For an example, lets get back to the Computer firm database. The type column of its Product table can contain one of three values. We can forbid entering any other information into this column by using the following constraint:

  1. CHECK (type IN('printer', 'pc', 'laptop'))

 Lets make a digression to familiarize ourselves with the ALTER TABLE statement that allows modifying the table structure without re-creating it anew each time. This is particularly important since a structure change may become necessary at a moment when the table already contains data.

The ALTER TABLE statement lets you add and delete columns, default values, and constraints.

At this point, were interested in applying a constraint to the type column; thus, lets examine the statement syntax for adding constraints first:

  1. ALTER TABLE < TABLE name >
  2. ADD CONSTRAINT < constraint name > < constraint >;

Now, we add our constraint to the table and check how it works.

  1. ALTER TABLE Product
  2. ADD CONSTRAINT chk_type CHECK (type IN('pc', 'laptop', 'printer'));

To make sure the constraint functions as it should, lets try adding a model of a new type:

  1. INSERT INTO Product VALUES('A', 1122, 'notebook');

As expected, we get the following error message in response:

The INSERT statement conflicted with the CHECK constraint "chk_type". The conflict occurred in database "learn", table "dbo.product", column 'type'. The statement has been terminated.

As one could easily have guessed, a row-level constraint refers to several columns, and is checked individually for each record to be changed. The row can be added (or changed) if its data doesnt violate the constraint. 

For example, lets forbid the maker Z producing anything but printers.

  1. ALTER TABLE Product
  2. ADD constraint chk_maker_Z CHECK ((maker='Z' AND type= 'printer') OR maker <>'Z');

Thus, the constraint ensures the model in the Product table is a printer by the maker Z (maker='Z' and type= 'printer') or is produced by any other maker (but not Z).

If we try to add a PC model by maker Z

  1. INSERT INTO Product VALUES('Z', 1122, 'PC');
well get the aforementioned error message with the constraint name chk_type being replaced by chk_maker_Z. However, a printer model can be added without any problems:

  1. INSERT INTO Product VALUES('Z', 1122, 'Printer');

And of course, other makers may produce any kind of product:

  1. INSERT INTO Product VALUES('V', 1123, 'PC');

Bookmark and Share
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 CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.