Module 5 Implementing Data Integrity Overview Types of Data Integrity Enforcing Data Integrity Defining Constraints Types of Constraints Disabling Constraints Using Defaults and Rules Deciding Which Enforcement Method to Use Types of Data Integrity Domain Integrity columns Entity Integrity rows Referential Integrity between tables Enforcing Data Integrity Declarative Data Integrity Criteria defined in object definitions SQL Server enforces automatically Implement by using constraints defaults and rules Procedural Data Integrity Criteria defined in script Script enforces Implement by using triggers and stored procedures Defining Constraints Determining Which Type of Constraint to Use Creating Constraints Considerations for Using Constraints Determining Which Type of Constraint to Use Type Typeof ofintegrity integrity Domain Domain Entity Entity Referential Referential Constraint Constrainttype type DEFAULT DEFAULT CHECK CHECK REFERENTIAL REFERENTIAL PRIMARY PRIMARYKEY KEY UNIQUE UNIQUE FOREIGN FOREIGNKEY KEY CHECK CHECK Creating Constraints Use CREATE TABLE or ALTER TABLE 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 Considerations for Using Constraints Can Be Changed Without Recreating a Table Require Error Checking in Applications and Transactions Verify Existing Data Types of Constraints DEFAULT Constraints CHECK Constraints PRIMARY KEY Constraints UNIQUE Constraints FOREIGN KEY Constraints Cascading Referential Integrity DEFAULT Constraints Apply Only to INSERT Statements Only One DEFAULT Constraint Per Column Cannot Be Used with IDENTITY Property or rowversion Data Type Allow Some System supplied Values USE USE Northwind Northwind ALTER ALTER TABLE TABLE dbo Customers dbo Customers ADD ADD CONSTRAINT CONSTRAINT DF contactname DF contactname DEFAULT DEFAULT UNKNOWN UNKNOWN FOR FOR ContactName ContactName CHECK Constraints Are Used with INSERT and UPDATE Statements Can Reference Other Columns in the Same Table Cannot Be used with the rowversion data type Contain subqueries USE USE Northwind Northwind ALTER ALTER TABLE TABLE dbo Employees dbo Employees ADD ADD CONSTRAINT CONSTRAINT CK birthdate CK birthdate CHECK CHECK BirthDate BirthDate 01 01 1900 01 01 1900 AND AND BirthDate BirthDate getdate getdate PRIMARY KEY Constraints Only One PRIMARY KEY Constraint Per Table Values Must Be Unique Null Values Are Not Allowed Creates a Unique Index on Specified Columns USE USE Northwind Northwind ALTER ALTER TABLE TABLE dbo Customers dbo Customers ADD ADD CONSTRAINT CONSTRAINT PK Customers PK Customers PRIMARY PRIMARY KEY KEY NONCLUSTERED NONCLUSTERED CustomerID 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 USE USE Northwind Northwind ALTER ALTER TABLE TABLE dbo Suppliers dbo Suppliers ADD ADD CONSTRAINT CONSTRAINT U CompanyName U CompanyName UNIQUE UNIQUE NONCLUSTERED NONCLUSTERED CompanyName CompanyName FOREIGN KEY Constraints 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 Use Only REFERENCES Clause Within Same Table USE USE Northwind Northwind ALTER ALTER TABLE TABLE dbo Orders dbo Orders ADD ADD CONSTRAINT CONSTRAINT FK Orders Customers FK Orders Customers FOREIGN FOREIGN KEY KEY CustomerID CustomerID REFERENCES REFERENCES dbo Customers CustomerID dbo Customers CustomerID Cascading Referential Integrity NO ACTION Customers Customers CustomerID PK Customers Customers CustomerID PK 1 1 INSERT INSERT new new CustomerID CustomerID 2 CASCADE Orders Orders CustomerID FK UPDATE UPDATE old old CustomerID CustomerID to to new new CustomerID CustomerID Customers Customers CustomerID PK 3 DELETE DELETE old old CustomerID CustomerID UPDATE UPDATE CustomerID CustomerID CASCADE Orders Orders CustomerID FK Disabling Constraints Disabling Constraint Checking on Existing Data Disabling Constraint Checking When Loading New Data 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 USE USE Northwind Northwind ALTER ALTER TABLE TABLE dbo Employees dbo Employees WITH WITH NOCHECK NOCHECK ADD ADD CONSTRAINT CONSTRAINT FK Employees Employees FK Employees Employees FOREIGN FOREIGN KEY KEY ReportsTo ReportsTo REFERENCES REFERENCES dbo Employees EmployeeID dbo Employees EmployeeID Disabling Constraint Checking When Loading New Data Applies to CHECK and FOREIGN KEY Constraints Use When Data conforms to constraints You load new data that does not conform to constraints USE USE Northwind Northwind ALTER ALTER TABLE TABLE dbo Employees dbo Employees NOCHECK NOCHECK CONSTRAINT CONSTRAINT FK Employees Employees FK Employees Employees Using Defaults and Rules As Independent Objects They Are defined once Can be bound to one or more columns or user defined data types 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 Customers Phone Customers Phone 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 regioncode rule Customers Region Customers Region Deciding Which Enforcement Method to Use Data Dataintegrity integrity components components Functionality Functionality Performance Performance Before Beforeor orafter after costs modification costs 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 Null Not Null NotNull Null Low Low Low Low Before Before Recommended Practices Use Use Constraints Constraints Because Because They They Are Are ANSI compliant ANSI compliant Use Use Cascading Cascading Referential Referential Integrity Integrity Instead Instead of of Triggers Triggers Lab A Implementing Data Integrity Review Types of Data Integrity Enforcing Data Integrity Defining Constraints Types of Constraints Disabling