tells you what is acceptable in a column

and it can be based on columns already existing in

other tables or columns

in the same table

what values are acceptable

to update

based on another column or tables' values

• it must reference of course, a primary key or
• unique constraint
• user must have REFERENCES permission on a reference table
• FKC that uses REFERENCES clause without the FOREIGN KEY clause refers to a column in the same table (doesn't go outside like a foreign keys does)
• No automatic index creation

If your INSERT statement doesn't have a value,

this kicks in

You cannot use or have this:

• in columns with timestamp data type
• IDENTITY Columns
• ROWGUIDCOL property set on column (Globally Unique Identifier)

null values are not allowed

each row is ID'd uniquely

One per table (column that uniquely identifies rows)

it must be unique

The primary key of course can also be composite

Null of course is NOT allowed

takes place automatically

when you

insert

update

delete

prevents duplication

of alternative

and not the primary key

values in a column

Here NULLS are allowed

A good candidate for unique constraint is

an employee (badge) number

or

parking space assignments

refer to a column

covers:

rules

data types

defaults

constraints

triggers

XML schema

applies to rows

"the primary key"

rules

NULLs

defaults

constraints

triggers

tables

and columns

includes triggers and constraints

"the foreign key"

this is created by CREATE RULE

however Microsoft states that forthcoming versions of MS SQL Server will no longer support Rules

This can be applied to multiple tables

ALTER TABLE [Sales].[SalesOrderHeader]

WITH CHECK ADD CONSTRAINT [FK_SalesOrderHeader_Customer_CustomerID] FOREIGN KEY ([CustomerID])

REFERENCES [Sales].[Customer] ([Customerid])

you can change any column value that defines a UNIQUE or PRIMARY KEY constraint

to propagate the change to any foreign key values that reference it

You change one column and other columns that reference the former column are changed as well

REFERENCES clause of the

ALTER TABLE

CREATE TABLE

statements

this supports:

ON DELETE

ON UPDATE

clauses

and controls the cascading referential integrity

options:

NO ACTION (default setting)

CASCADE

SET NULL

SET DEFAULT

it automatically generates an index

and then SQL Server uses the index to enforce uniqueness

Primary Key Constraint Defined

Example

CONSTRAINT [PK_Department_DepartmentID] PRIMARY KEY CLUSTERED

([DepartmentID] ASC) WITH (IGNORE_DUP_KEY=OFF) ON

[PRIMARY]