loading..
Русский    English
09:35

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, let’s 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'))

 Let’s 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, we’re interested in applying a constraint to the type column; thus, let’s 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, let’s 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 doesn’t violate the constraint. 

For example, let’s 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');
we’ll 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
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