Page 69 - DCAP508_DATABASE_ADMINISTRATION

Unit 5: SQL Server Databases

An identifier column can be added or deleted using the IDENTITY or ROWGUIDCOL property. Notes
The ROWGUIDCOL property can also be added to or removed from an existing column,
although only one column in a table can have the ROWGUIDCOL property at any one time.
A table and selected columns within the table can be registered for full-text indexing.

5.6 Constraints

Constraints let you define the way the Database Engine automatically enforces the integrity of
a database. Constraints define rules regarding the values allowed in columns and are the standard
mechanism for enforcing integrity. Using constraints is preferred to using DML Triggers, rules,
and defaults. The query optimizer also uses constraint definitions to build high-performance
query execution plans.

5.6.1 Classes of Constraints

SQL Server supports the following classes of constraints:

NOT NULL specifies that the column does not accept NULL values.
CHECK constraints enforce domain integrity by limiting the values that can be put in a
column.
A CHECK constraint specifies a Boolean (evaluates to TRUE, FALSE, or unknown) search
condition that is applied to all values that are entered for the column. All values that
evaluate to FALSE are rejected. You can specify multiple CHECK constraints for each
column. The following sample shows creating the constraint chk_id. This constraint
additionally enforces the domain of the primary key by making sure that only numbers
within a specified range are entered for the key.
CREATE TABLE cust_sample
(
cust_id int PRIMARY KEY,

cust_name char(50),
cust_address char(50),
cust_credit_limit money,
CONSTRAINT chk_id CHECK (cust_id BETWEEN 0 and 10000 )
)

UNIQUE constraints enforce the uniqueness of the values in a set of columns.
In a UNIQUE constraint, no two rows in the table can have the same value for the columns.
Primary keys also enforce uniqueness, but primary keys do not allow for NULL as one of
the unique values.
PRIMARY KEY constraints identify the column or set of columns that have values that
uniquely identify a row in a table.

Did u know? No two rows in a table can have the same primary key value. You cannot
enter NULL for any column in a primary key. We recommend using a small, integer
column as a primary key. Each table should have a primary key. A column or combination
of columns that qualify as a primary key value is referred to as a candidate key.

LOVELY PROFESSIONAL UNIVERSITY 63