Implementing DataIntegrity

Types of Data Integrity

Entity Integrity ensures that there are no duplicate rows in a table.

Domain Integrity enforces valid entries for a given column by restricting the type, the format, or the range of possible values.

Referential integrity ensures that rows cannot be deleted, which are used by other records (for example, corresponding data values between tables will be vital).

User-Defined Integrity enforces some specific business rules that do not fall into entity, domain, or referential integrity categories.
 

Defining Constraints

 Each of these categories of the data integrity can be enforced by the appropriate constraints. Microsoft SQL Server supports the following constraints:


PRIMARY KEY
UNIQUE
FOREIGN KEY
CHECK
NOT NULL

Creating Constraints

Use CREATE TABLE or ALTER TABLE Statements to create the above constraint
Can Add Constraints to a Table with Existing Data
Can Place Constraints on Single or Multiple Columns
Single column, called column-level constraint
Multiple columns, called table-level constraint

A PRIMARY KEY constraint is a unique identifier for a row within a database table. Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table. The primary key constraints are used to enforce entity integrity

Only One PRIMARY KEY Constraint Per Table

Values Must Be Unique

Null Values Are Not Allowed

Creates a Unique Index on Specified Columns

ALTER TABLE dbo.Customers

ADD

CONSTRAINT PK_Customers

PRIMARY KEY CLUSTERED (CustomerID)

 UNIQUE Constraints
Allow One Null Value

Allow Multiple UNIQUE Constraints on a Table

Defined with One or More Columns

Enforced with a Unique Index

Example:

ALTER TABLE dbo.Suppliers
ADD

CONSTRAINT U_CompanyName

UNIQUE NONCLUSTERED (CompanyName)

A FOREIGN KEY constraint prevents any actions that would destroy link between tables with the corresponding data values. A foreign key in one table points to a primary key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value. The foreign key constraints are used to enforce referential integrity.

Must Reference a PRIMARY KEY or UNIQUE Constraint Provide Single or Multicolumn Referential Integrity

Do Not Automatically Create Indexes
Users Must Have SELECT or REFERENCES Permissions on Referenced Tables

Example:ALTER TABLE dbo.Orders
ADD CONSTRAINT FK_Orders_Customers
FOREIGN KEY (CustomerID)
REFERENCES dbo.Customers(CustomerID)

A CHECK constraint is used to limit the values that can be placed in a column. The check constraints are used to enforce domain integrity

Are Used with INSERT and UPDATE Statements
Can Reference Other Columns in the Same Table

Example:
ALTER TABLE dbo.Employees
ADD CONSTRAINT CK_birthdate
CHECK (BirthDate > '01-01-1900' AND BirthDate < getdate())

A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity, as the check constraints.

You can create constraints when the table is created, as part of the table definition by using the CREATE TABLE statement.

DEFAULT Constraints

Apply Only to INSERT Statements
Only One DEFAULT Constraint Per Column
Cannot Be Used with IDENTITY Property
Allow Some System-supplied Values

ALTER TABLE dbo.Customers
ADD CONSTRAINT DF_contactname
DEFAULT 'UNKNOWN' FOR ContactName

 ExamplesThe following example creates a check_sale CHECK constraint on an employee table, the constraint is created with the table creation:
CREATE TABLE employee
( EmployeeId INT NOT NULL,
LName VARCHAR(30) NOT NULL,
FName VARCHAR(30) NOT NULL,
Address VARCHAR(100) NOT NULL,
HireDate DATETIME NOT NULL,
Salary MONEY NOT NULL CONSTRAINT check_sale CHECK (salary > 0) )

Cascading Referential Integrity

SQL Server 2000 has introduced many new features. Some of them fall into the constraints area. Now you can control the actions SQL Server takes when you attempt to update or delete a key to which existing foreign keys point.


You can control it by using the ON DELETE and ON UPDATE clauses in the REFERENCES clause of the CREATE TABLE and ALTER TABLE statements.

For example, in the previous versions of SQL Server if you wanted to do a cascade delete from the referenced table when the appropriate record in the parent table is deleted, you had to create a trigger which executed on delete of the parent table, but now you can simply specify the ON DELETE clause in the REFERENCES clause.

The following example is used to create the Books and the Authors tables and create a foreign key constraint which will perform the cascade delete action, therefore, when a row in the Authors table is deleted, the corresponding rows in the Books are also deleted:

CREATE TABLE Books (
BookID INT NOT NULL PRIMARY KEY,
AuthorID INT NOT NULL,
BookName VARCHAR(100) NOT NULL,
Price MONEY NOT NULL )

GO

CREATE TABLE Authors (
AuthorID INT NOT NULL PRIMARY KEY,
Name VARCHAR(100) NOT NULL )

GO

ALTER TABLE Books
ADD CONSTRAINT fk_author
FOREIGN KEY (AuthorID)
REFERENCES Authors (AuthorID) ON DELETE CASCADE

Sometimes you need to perform some actions that require the FOREIGN KEY or CHECK constraints be disabled
The following example disables the check_sale constraint in the employee table and enables this constraint later:

-- disable the check_sale constraint in the employee table
ALTER TABLE employee NOCHECK CONSTRAINT check_sale

-- enable the check_sale constraint in the employee table
ALTER TABLE employee CHECK CONSTRAINT check_sale

Disabling Constraint Checking on Existing Data

Applies to CHECK and FOREIGN KEY Constraints
Use WITH NOCHECK Option When Adding a New Constraint
Use if Existing Data Will Not Change
Can Change Existing Data Before Adding Constraints

Example:
ALTER TABLE dbo.Employees
WITH NOCHECK
ADD CONSTRAINT FK_Employees_Employees
FOREIGN KEY (ReportsTo)
REFERENCES dbo.Employees(EmployeeID)
 
 Using Defaults and Rules
As Independent Objects They Are defined once
Can be bound to one or more columns or user-defined data types

Example - Creating a Default:

CREATE DEFAULT phone_no_default AS '000-0000000'
GO
EXEC sp_bindefault phone_no_default, 'Customers.Phone'

Example - Creating a Rule:

CREATE RULE regioncode_rule  AS @regioncode IN (‘CMB', ‘KND', ‘GAL', 'MAT')GOEXEC sp_bindrule regioncode_rule,  'Customers.Region'

 

Once the rule is created you can insert only CMB, KND, GAL, or MAT to the region column of Customers table.