CONSTRAINT-12

A CONSTRAINT clause is an optional part of a CREATE TABLE statement or ALTER TABLE. A constraint is a rule to which data must conform. Constraint names are o ptional.
A CONSTRAINT can be one of the following:
· a column-level constraint
Column-level constraints refer to a single column in the table and do not specify a column name (except check constraints). They refer to the column that they follow.
· a table-level constraint
Table-level constraints refer to one or more columns in the table. Table-level constraints specify the names of the columns to which they apply. Table-level CHECK constraints can refer to 0 or more columns in the table.
Column constraints include:
· NOT NULL
Specifies that this column cannot hold NULL values (constraints of this type are not nameable).
· NULL
The opposite of NOT NULL (not really a constraint), it specifies that the column can hold NULL values. Specifying NULL is the same as saying nothing at all, except when the column is included in a PRIMARY KEY constraint.
· PRIMARY KEY
Specifies the column that uniquely identifies a row in the table. The identified columns must be defined as NOT NULL.
Note:
If you attempt to add a primary key using ALTER TABLE and any of the columns included in the primary key contain null values, an error will be generated and the primary key will not be added. See ALTER TABLE for more information.
· UNIQUE
Specifies that values in the column must be unique. NULL values are not allowed.
· FOREIGN KEY
Specifies that the values in the column must correspond to values in a referenced primary key or unique key column or that they are NULL.
· CHECK
Specifies rules for values in the column.
Table constraints include:
· PRIMARY KEY
Specifies the column or columns that uniquely identify a row in the table. NULL values are not allowed.
· UNIQUE
Specifies that values in the columns must be unique. The identified columns must be defined as NOT NULL.
· FOREIGN KEY
Specifies that the values in the columns must correspond to values in referenced primary key or unique columns or that they are NULL.
Note:
If the foreign key consists of multiple columns, and any column is NULL, the whole key is considered NULL. The insert is permitted no matter what is on the non-null columns.
· CHECK
Specifies a wide range of rules for values in the table.
Column constraints and table constraints have the same function; the difference is in where you specify them. Table constraints allow you to specify more than one column in a PRIMARY KEY, UNIQUE, CHECK, or FOREIGN KEY constraint definition. Column-level constraints (except for check constraints) refer to only one column