Implementing
Data Integrity
Vu Tuyet Trinh
trinhvt-fit@[Link]
Hanoi University of Technology
1
Data Integrity
Defining the quality of the data in the database.
Types:
Domain Integrity
(columns)
Entity Integrity (rows)
User-defined
integrity
Referential
Integrity
(between tables)
Microsoft
Enforcing Data Integrity
Declarative data integrity
Defined in object definitions
Enforced automatically SQL Server by using constraints,
defaults, and rules
Procedural data integrity
Defined in script
Enforced by executing scripts, triggers and stored procedures
Microsoft
Outline
Data Integrity
Enforcing Data Integrity
Types of Constraints
Defining Constraints
Disabling Constraints
Using Defaults and Rules
Deciding enforcement method to use
Microsoft
Types of Constraints
PRIMARY KEY constraints
UNIQUE constraints
FOREIGN KEY constraints
CHECK constraints
DEFAULT constraints
Cascading referential integrity
Microsoft
PRIMARY KEY Constraints
Only one PRIMARY KEY constraint per table
Unique values
Not allowed NULL values
Enforced with unique index
USE
USE Northwind
Northwind
ALTER
ALTER TABLE
TABLE [Link]
[Link]
ADD
ADD CONSTRAINT
CONSTRAINT PK_Customers
PK_Customers
PRIMARY
PRIMARY KEY
KEY NONCLUSTERED
NONCLUSTERED (CustomerID)
(CustomerID)
Microsoft
UNIQUE Constraints
Defined with one or more columns
Allowing one null value
Allowing multiple UNIQUE constraints on a table
Enforced with a unique Index
USE
USE Northwind
Northwind
ALTER
ALTER TABLE
TABLE [Link]
[Link]
ADD
ADD CONSTRAINT
CONSTRAINT U_CompanyName
U_CompanyName
UNIQUE
UNIQUE NONCLUSTERED
NONCLUSTERED (CompanyName)
(CompanyName)
Microsoft
FOREIGN KEY Constraints
Single or multi-column referential integrity
Must reference a PRIMARY KEY or UNIQUE constraint
Must have SELECT or REFERENCES permissions on
referenced tables
Use only REFERENCES clause within same table
USE
USE Northwind
Northwind
ALTER
ALTER TABLE
TABLE [Link]
[Link]
ADD
ADD CONSTRAINT
CONSTRAINT FK_Orders_Customers
FK_Orders_Customers
FOREIGN
FOREIGN KEY
KEY (CustomerID)
(CustomerID)
REFERENCES
REFERENCES [Link](CustomerID)
[Link](CustomerID)
Microsoft
CHECK Constraints
Used with INSERT or UPDATE Statements
Defined on one or more column(s) in the same table
Cannot
Be used with the rowversion data type
Contain subqueries
USE
USE Northwind
Northwind
ALTER
ALTER TABLE
TABLE [Link]
[Link]
ADD
ADD CONSTRAINT
CONSTRAINT CK_birthdate
CK_birthdate
CHECK
CHECK (BirthDate
(BirthDate >> '01-01-1900'
'01-01-1900' AND
AND BirthDate
BirthDate <getdate())
<getdate())
Microsoft
DEFAULT Constraints
Applied for INSERT statements
Only one DEFAULT constraint per column
Cannot be used with IDENTITY property
or rowversion data type
Allowing some system-supplied values
USE
USE Northwind
Northwind
ALTER
ALTER TABLE
TABLE [Link]
[Link]
ADD
ADD CONSTRAINT
CONSTRAINT DF_contactname
DF_contactname
DEFAULT
DEFAULT 'UNKNOWN'
'UNKNOWN'
FOR
FOR ContactName
ContactName
Microsoft
Cascading referential integrity
Defined for DETELE or UPDATE a key to which existing
foreign keys point
[ON DELETE {NO ACTION|CASCADE|SET NULL|SET DEFAULT}]
[ON UPDATE {NO ACTION|CASCADE|SET NULL|SET DEFAULT}]
Microsoft
Considerations for using constraints
Can be changed without recreating a table
Require error-checking in applications and transactions
Verify existing data?
If not case, disabling constraints
Microsoft
Disabling Constraints
Disabling constraint checking on existing data
Using WITH NOCHECK option when adding a new constraint
Applied to CHECK and FOREIGN KEY constraints
USE
USE Northwind
Northwind
ALTER
ALTER TABLE
TABLE [Link]
[Link]
WITH
WITH NOCHECK
NOCHECK
ADD
ADD CONSTRAINT
CONSTRAINT FK_Employees_Employees
FK_Employees_Employees
FOREIGN
FOREIGN KEY
KEY (ReportsTo)
(ReportsTo)
REFERENCES
REFERENCES [Link](EmployeeID)
[Link](EmployeeID)
Microsoft
Disabling Constraints
Disabling constraint checking when loading new data
Using NOCHECK option before loading new data
Applied to CHECK and FOREIGN KEY Constraints
USE
USE Northwind
Northwind
ALTER
ALTER TABLE
TABLE [Link]
[Link]
NOCHECK
NOCHECK
CONSTRAINT
CONSTRAINT FK_Employees_Employees
FK_Employees_Employees
Microsoft
Default Constraints
Defined as independent objects
Bound to one or more columns or user-defined data
types
CREATE
CREATE DEFAULT
DEFAULT [schema_name.]
[schema_name.] default_name
default_name AS
AS
constant_expression
constant_expression [[ ;; ]]
EXEC
EXEC sp_bindefault
sp_bindefault <default_name>,
<default_name>, <Object_name>
<Object_name>
CREATE
CREATE DEFAULT
DEFAULT phone_no_default
phone_no_default
AS
AS '(000)000-0000'
'(000)000-0000'
GO
GO
EXEC
EXEC sp_bindefault
sp_bindefault phone_no_default,
phone_no_default, '[Link]'
'[Link]'
Microsoft
Rule Constraints
CREATE
CREATE RULE
RULE [schema_name.]
[schema_name.] rule_name
rule_name
AS
AS <condition_expression>
<condition_expression>
GO
GO
EXEC
EXEC sp_bindrule
sp_bindrule regioncode_rule,'[Link]'
regioncode_rule,'[Link]'
CREATE
CREATE RULE
RULE regioncode_rule
regioncode_rule
AS
AS @regioncode
@regioncode IN
IN ('IA',
('IA', 'IL',
'IL', 'KS',
'KS', 'MO')
'MO')
GO
GO
EXEC
EXEC sp_bindrule
sp_bindrule regioncode_rule,'[Link]'
regioncode_rule,'[Link]'
Note: CREATE RULE may be not supported anymore
in next version of SQL Server
Microsoft
Enforcement Method to Use
Data
Dataintegrity
integrity Functionality
Functionality Performance
Performance Before
Beforeor
orafter
after
components
components costs
costs modification
modification
Constraints
Constraints Medium
Medium Low
Low Before
Before
Defaults
Defaultsand
andrules
rules Low
Low Low
Low Before
Before
Triggers
Triggers High
High Medium-High
Medium-High After
After
Data
Datatypes,
types, Low
Low Low
Low Before
Before
Null/Not
Null/NotNull
Null
Microsoft
Summary
Data integrity as means for defining the correctness and
the completeness of data
Enforcing data integrity as means for maintaining
complete and correct database
Using data integrity, using enforcement method for data
integrity are important but … only when necessary
Microsoft
Microsoft
Using Stored Procedure & Trigger
Microsoft