loading..
Русский    English
15:59
go

Check Constraints

A check constraint can be defined as follows: 

  1. CHECK (< predicate >)

The predicate accepts the values TRUE, FALSE, or UNKNOWN. If it takes the FALSE value, the constraint is considered to be violated, the action that caused this violation is cancelled, and an error message is generated.

Let’s get back to our problem – inserting the row (NULL,    '2000',    NULL) into the Product table. It’s obvious such data should be avoided, since it’s not clear to what type of product the model 2000 belongs and who produces it. We can use the IS NOT NULL predicate in the check constraints for the columns maker and type:

  1. DROP TABLE Product;
  2. CREATE TABLE Product (
  3. maker varchar(10), 
  4. model varchar(50), 
  5. type varchar(50),
  6. CONSTRAINT product_PK PRIMARY KEY (model),
  7. CONSTRAINT maker_ch CHECK(maker IS NOT NULL),
  8. CONSTRAINT type_ch CHECK(type IS NOT NULL)
  9. );

Now, if we attempt to add the row (NULL,    '2000',    NULL) we’ll get the following error message:

Cannot insert the value NULL into column 'model', table 'Product'; column does not allow nulls. INSERT fails.

The row won’t be inserted, or, to be more precise, the whole INSERT statement will be rejected.

Note that the NOT NULL constraint can be included into the column definition itself (just as the PRIMARY KEY constraint for a simple key):

  1. DROP TABLE Product;
  2. CREATE TABLE Product (
  3. maker varchar(10) NOT NULL, 
  4. model varchar(50) PRIMARY KEY, 
  5. type varchar(50) NOT NULL
  6. );

Moreover, any column-level constraint can be written down directly in definition of a column. Below is the example adding a new column (named available) to Product table:

  1. ALTER TABLE Product ADD available VARCHAR(20) DEFAULT 'Yes';

To restrict admissible values, which can appear in the column, by the values ' Yes' and ' No ', this statement could be rewritten in the form of:

  1. ALTER TABLE Product ADD available VARCHAR(20) 
  2. CHECK(available IN('Yes','No')) DEFAULT 'Yes';

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