1 / 27

Module 4: Implementing Data Integrity

Module 4: Implementing Data Integrity. Overview. Introducing Data Integrity Defining Constraints Understanding Constraint Types Disabling Constraints Using Defaults and Rules Deciding Which Enforcement Method to Use. Lesson: Introducing Data Integrity. Types of Data Integrity

ranger
Download Presentation

Module 4: Implementing Data Integrity

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Module 4: Implementing Data Integrity

  2. Overview • Introducing Data Integrity • Defining Constraints • Understanding Constraint Types • Disabling Constraints • Using Defaults and Rules • Deciding Which Enforcement Method to Use

  3. Lesson: Introducing Data Integrity • Types of Data Integrity • How Data Integrity Is Enforced

  4. Types of Data Integrity Domain Integrity(columns) Entity Integrity (rows) Referential Integrity(between tables or columns of the same table)

  5. How Data Integrity is Enforced • 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 scripts • Criteria enforced by scripts • Implement by using triggers and stored procedures

  6. Lesson: Defining Constraints • Guidelines for Determining Which Type of Constraint to Use • Constraint Creation • Considerations for Using Constraints

  7. Guidelines for Determining Which Type of Constraint to Use

  8. Constraint Creation • Use CREATE TABLE or ALTER TABLE • Can Add Constraints to a Table with Existing Data • Can Place Constraints on Single or Multiple Columns • Column-Level Constraint • Can only reference a single column • Specify as part of the column definition • Table-Level Constraint • Can reference one or more columns • Specify as part of the table definition (after all column definitions)

  9. Considerations for Using Constraints • Can Be Changed Without Re-creating a Table • Require Error-Checking in Applications and Transactions • Verify Existing Data

  10. Lesson: Understanding Constraint Types • DEFAULT Constraints • CHECK Constraints • PRIMARY KEY Constraints • UNIQUE Constraints • FOREIGN KEY Constraints • Cascading Referential Integrity

  11. DEFAULT Constraints • Apply Only to INSERT Statements • Only One DEFAULT Constraint Per Column • Cannot Be Used with IDENTITY Propertyor rowversion Data Type • Allow Some System-Supplied Values USE NorthwindALTER TABLE dbo.CustomersADDCONSTRAINT DF_contactname DEFAULT 'UNKNOWN' FOR ContactName

  12. CHECK Constraints • Are Invoked by INSERT and UPDATE Statements • Can Reference Other Columns in the Same Table • Cannot: • Be used with the rowversion data type • Contain subqueries USE NorthwindALTER TABLE dbo.EmployeesADD CONSTRAINT CK_birthdateCHECK (BirthDate > '01-01-1900' AND BirthDate < getdate())

  13. 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 Northwind ALTER TABLE dbo.Customers ADD CONSTRAINT PK_Customers PRIMARY KEY NONCLUSTERED (CustomerID)

  14. 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 Northwind ALTER TABLE dbo.Suppliers ADD CONSTRAINT U_CompanyName UNIQUE NONCLUSTERED (CompanyName)

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

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

  17. Lesson: Disabling Constraints • Guidelines for Disabling Constraint Checking on Existing Data • Guidelines for Disabling Constraint Checking When Loading New Data

  18. Guidelines for Disabling Constraint Checking on Existing Data • Applies to CHECK and FOREIGN KEY Constraints • Use WITH NOCHECK Option When Adding a New Constraint • Existing Data Will Be Checked Only During Future Updates of the Constraint Column • Can Change Existing Data Before Adding Constraints USE Northwind ALTER TABLE dbo.Employees WITH NOCHECK ADD CONSTRAINT FK_Employees_Employees FOREIGN KEY (ReportsTo) REFERENCES dbo.Employees(EmployeeID)

  19. Guidelines for 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 Northwind ALTER TABLE dbo.Employees NOCHECK CONSTRAINT FK_Employees_Employees

  20. Lesson: Using Defaults and Rules • Defaults • Rules

  21. Defaults • Considerations: • Defined once, can be bound to one or more columns or user-defined data types • Rules on the column validates the value of a default • Check constraints on the column validates the value of a default. CREATE DEFAULT phone_no_default AS '(000)000-0000' GOEXEC sp_bindefault phone_no_default, 'Customers.Phone'

  22. Rules • Considerations: • Defined once, can be bound to one or more columns or user-defined data types • Can contain any expression valid in a WHERE clause • A column or user-defined data type can have only one rule that is bound to it. CREATE RULE regioncode_rule AS @regioncode IN ('IA', 'IL', 'KS', 'MO')GOEXEC sp_bindrule regioncode_rule, 'Customers.Region'

  23. Lesson: Deciding Which Enforcement Method to Use • Functionality and Performance Considerations • Comparison Matrix • Best Practices

  24. Functionality and Performance Considerations • Declarative Integrity • Use for fundamental integrity logic, such as: • When enforcing valid values • Maintaining the relationships between tables • Triggers Or Stored Procedures • Use to maintain complex redundant data that is not part of a primary or foreign key relationship

  25. Comparison Matrix

  26. Best Practices Use Constraints Instead of Triggers Because They Incur Less Overhead ü Use Constraints Because They Are ANSI-Compliant ü Use Cascading Referential Integrity Instead of Triggers ü

  27. Lab A: Implementing Data Integrity • Exercise 1: Defining DEFAULT Constraints • Exercise 2: Defining CHECK Constraints • Exercise 3: Defining PRIMARY KEY Constraints • Exercise 4: Defining FOREIGN KEY Constraints • Optional Exercise: Creating Defaults and Rules

More Related