Module 5 Implementing Data Integrity Overview n Types

Module 5: Implementing Data Integrity

Overview n Types of Data Integrity n Enforcing Data Integrity n Defining Constraints n Types of Constraints n Disabling Constraints n Using Defaults and Rules n Deciding Which Enforcement Method to Use

Types of Data Integrity Domain Integrity (columns) Entity Integrity (rows) Referential Integrity (between tables)

Enforcing Data Integrity n n Declarative Data Integrity l Criteria defined in object definitions l SQL Server enforces automatically l Implement by using constraints, defaults, and rules Procedural Data Integrity l Criteria defined in script l Script enforces l Implement by using triggers and stored procedures

u Defining Constraints n Determining Which Type of Constraint to Use n Creating Constraints n Considerations for Using Constraints

Determining Which Type of Constraint to Use Type of integrity Domain Entity Referential Constraint type DEFAULT CHECK REFERENTIAL PRIMARY KEY UNIQUE FOREIGN KEY CHECK

Creating Constraints n Use CREATE TABLE or ALTER TABLE n Can Add Constraints to a Table with Existing Data n Can Place Constraints on Single or Multiple Columns l Single column, called column-level constraint l Multiple columns, called table-level constraint

Considerations for Using Constraints n Can Be Changed Without Recreating a Table n Require Error-Checking in Applications and Transactions n Verify Existing Data

u Types of Constraints n DEFAULT Constraints n CHECK Constraints n PRIMARY KEY Constraints n UNIQUE Constraints n FOREIGN KEY Constraints n Cascading Referential Integrity

DEFAULT Constraints n Apply Only to INSERT Statements n Only One DEFAULT Constraint Per Column n Cannot Be Used with IDENTITY Property or rowversion. Data Type n Allow Some System-supplied Values USE Northwind ALTER TABLE dbo. Customers ADD CONSTRAINT DF_contactname DEFAULT 'UNKNOWN' FOR Contact. Name

CHECK Constraints n Are Used with INSERT and UPDATE Statements n Can Reference Other Columns in the Same Table n Cannot: l Be used with the rowversiondata type l Contain subqueries USE Northwind ALTER TABLE dbo. Employees ADD CONSTRAINT CK_birthdate CHECK (Birth. Date > '01 -01 -1900' AND Birth. Date < getdate())

PRIMARY KEY Constraints n Only One PRIMARY KEY Constraint Per Table n Values Must Be Unique n Null Values Are Not Allowed n Creates a Unique Index on Specified Columns USE Northwind ALTER TABLE dbo. Customers ADD CONSTRAINT PK_Customers PRIMARY KEY NONCLUSTERED (Customer. ID)

UNIQUE Constraints n Allow One Null Value n Allow Multiple UNIQUE Constraints on a Table n Defined with One or More Columns n Enforced with a Unique Index USE Northwind ALTER TABLE dbo. Suppliers ADD CONSTRAINT U_Company. Name UNIQUE NONCLUSTERED (Company. Name)

FOREIGN KEY Constraints n Must Reference a PRIMARY KEY or UNIQUE Constraint n Provide Single or Multicolumn Referential Integrity n Do Not Automatically Create Indexes n Users Must Have SELECT or REFERENCES Permissions on Referenced Tables n Use Only REFERENCES Clause Within Same Table USE Northwind ALTER TABLE dbo. Orders ADD CONSTRAINT FK_Orders_Customers FOREIGN KEY (Customer. ID) REFERENCES dbo. Customers(Customer. ID)

Cascading Referential Integrity NO ACTION CASCADE Customers Customer. ID (PK) 1 1 INSERT new Customer. ID 2 Customers Customer. ID (PK) Orders Customer. ID (FK) UPDATE old Customer. ID to new Customer. ID Customers Customer. ID (PK) 3 DELETE old Customer. ID UPDATE Customer. ID CASCADE Orders Customer. ID (FK)

u Disabling Constraints n Disabling Constraint Checking on Existing Data n Disabling Constraint Checking When Loading New Data

Disabling Constraint Checking on Existing Data n Applies to CHECK and FOREIGN KEY Constraints n Use WITH NOCHECK Option When Adding a New Constraint n Use if Existing Data Will Not Change n Can Change Existing Data Before Adding Constraints USE Northwind ALTER TABLE dbo. Employees WITH NOCHECK ADD CONSTRAINT FK_Employees FOREIGN KEY (Reports. To) REFERENCES dbo. Employees(Employee. ID)

Disabling Constraint Checking When Loading New Data n Applies to CHECK and FOREIGN KEY Constraints n Use When: l Data conforms to constraints l You load new data that does not conform to constraints USE Northwind ALTER TABLE dbo. Employees NOCHECK CONSTRAINT FK_Employees

Using Defaults and Rules n As Independent Objects They: l Are defined once l Can be bound to one or more columns or user-defined data types CREATE DEFAULT phone_no_default AS '(000)000 -0000' GO EXEC sp_bindefault phone_no_default, 'Customers. Phone' CREATE RULE regioncode_rule AS @regioncode IN ('IA', 'IL', 'KS', 'MO') GO EXEC sp_bindrule regioncode_rule, 'Customers. Region'

Deciding Which Enforcement Method to Use Data integrity components Functionality Performance Before or after costs modification Constraints Medium Low Before Defaults and rules Low Before Triggers High Medium-High After Data types, Null/Not Null Low Before

Recommended Practices Use Constraints Because They Are ANSI-compliant Use Cascading Referential Integrity Instead of Triggers

Lab A: Implementing Data Integrity

Review n Types of Data Integrity n Enforcing Data Integrity n Defining Constraints n Types of Constraints n Disabling Constraints n Using Defaults and Rules n Deciding Which Enforcement Method to Use