Module 5 Implementing Data Integrity Overview n Types

  • Slides: 23
Download presentation
Module 5: Implementing Data Integrity

Module 5: Implementing Data Integrity

Overview n Types of Data Integrity n Enforcing Data Integrity n Defining Constraints n

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)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Lab A: Implementing Data Integrity

Lab A: Implementing Data Integrity

Review n Types of Data Integrity n Enforcing Data Integrity n Defining Constraints n

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