Module 14 Ensuring Data Integrity through Constraints

1. Module 14 Ensuring Data Integrity through Constraints

2. Module Overview • Enforcing Data Integrity • Implementing Domain Integrity • Implementing Entity and Referential Integrity

3. Lesson 1: Enforcing Data Integrity • Discussion: Data Integrity Across Application Layers • Types of Data Integrity • Options for Enforcing Data Integrity

4. Discussion: Data Integrity Across Application Layers • Consider these issues: • We need to make sure that orders are only placed for customers that exist. • We need to ensure that a particular column contains an integer with a value between 1 and 9. • We need to ensure that a particular column always contains a value. • We need to ensure that product specials are only ordered on Tuesdays in summer. • Should the application enforce these? • Should the database enforce these?

5. Types of Data Integrity Domain Integrity (Columns) Entity Integrity (Rows) Referential Integrity (Between Tables or Columns in different rows of the same Table)

6. Options for Enforcing Data Integrity Earlier versions of SQL Server used Rules and Defaults as separate objects. These should no longer be used.

7. Lesson 2: Implementing Domain Integrity • Data Types • Column Nullability • DEFAULT Constraints • CHECK Constraints • Demonstration 2A: Data and Domain Integrity

8. Data Types • Important decision when designing tables • Can be assigned using: • System Data Types • Alias Data Types • User-defined Data Types CREATETABLESales.Opportunity ( OpportunityIDint, Requirements nvarchar(50), ReceivedDatedate, LikelyClosingDatedate, SalespersonIDint Rating int );

9. Column Nullability • Determines whether a value must be provided for a column • Allows to specify the value explicitly as NULL in an INSERT • Is often inappropriately defined CREATETABLESales.Opportunity ( OpportunityIDintNOTNULL, Requirements nvarchar(50)NOTNULL, ReceivedDatedateNOTNULL, LikelyClosingDatedateNULL, SalespersonIDintNULL, Rating intNOTNULL );

10. DEFAULT Constraints • Provide default values for columns • Are used when no value is provided in an INSERT statement • Must be compatible with the data type for the column CREATETABLESales.Opportunity ( OpportunityIDint, Requirements nvarchar(50), ReceivedDatedate CONSTRAINTDF_Opportunity_ReceivedDate DEFAULT (SYSDATETIME()), LikelyClosingDatedate, SalespersonIDint Rating int );

11. CHECK Constraints • Limits the values that are accepted into a column • Values that evaluate to FALSE are rejected • Care must be taken with values returning NULL • Can be defined at table level to refer to multiple columns CREATETABLESales.Opportunity ( OpportunityIDint, Requirements nvarchar(50), ReceivedDatedate, LikelyClosingDatedate, SalespersonIDint, Rating int CONSTRAINTCHK_Opportunity_Rating_Range1To4 CHECK (Rating BETWEEN 1 AND 4) );

12. Demonstration 2A: Data and Domain Integrity In this demonstration, you will see how to enforce data and domain integrity

13. Lesson 3: Implementing Entity and Referential Integrity • PRIMARY KEY Constraints • UNIQUE Constraints • FOREIGN KEY Constraints • Cascading Referential Integrity • Considerations for Constraint Checking • Demonstration 3A: Entity and Referential Integrity

14. PRIMARY KEY Constraints • Is used to uniquely identify a row in a table • Must be unique and not NULL • May involve multiple columns CREATETABLESales.Opportunity ( OpportunityIDintNOTNULL CONSTRAINTPK_Opportunity PRIMARYKEY, Requirements nvarchar(50)NOTNULL, ReceivedDatedateNOTNULL, LikelyClosingDatedateNULL, SalespersonIDintNULL, Rating intNOTNULL );

15. UNIQUE Constraints • Requires that values in each row are different if supplied • Must be unique but one row can be NULL • May involve multiple columns CREATETABLESales.Opportunity ( OpportunityIDintNOTNULL CONSTRAINTPK_Opportunity PRIMARYKEY, Requirements nvarchar(50)NOTNULL CONSTRAINTUQ_Opportunity_Requirements UNIQUE, ReceivedDatedateNOTNULL, LikelyClosingDatedateNULL, SalespersonIDintNULL, Rating intNOTNULL );

16. FOREIGN KEY Constraints • Is used to enforce relationships between tables • Must reference PRIMARY KEY or UNIQUE column(s) • May be NULL • Can be applied WITH NOCHECK CREATETABLESales.Opportunity ( OpportunityIDintNOTNULL CONSTRAINTPK_Opportunity PRIMARYKEY, Requirements nvarchar(50)NOTNULL, ReceivedDatedateNOTNULL, LikelyClosingDatedateNULL, SalespersonIDintNULL CONSTRAINTFK_Opportunity_Salesperson FOREIGNKEY REFERENCESSales.Salesperson(BusinessEntityID), Rating intNOTNULL );

17. Cascading Referential Integrity Controlled by CASCADE clause of FOREIGN KEY

18. Considerations for Constraint Checking • Assign meaningful names to constraints ü • Create, change, and drop constraints without having to drop and recreate the table ü • Perform error checking in your applications and transactions ü Disable CHECK and FOREIGN KEY constraints: • To improve performance during large batch jobs • To avoid checking existing data when you add new constraints to a table

19. Demonstration 3A: Entity and Referential Integrity In this demonstration, you will see how to define: • Entity integrity for tables • Referential integrity for tables • Cascading referential integrity constraints

20. Lab 14: Ensuring Data Integrity through Constraints • Exercise 1: Constraint Design • Challenge Exercise 2: Test the constraints (Only if time permits) Logon information Estimated time: 45minutes

21. Lab Scenario A table has recently been added to the Marketing system but has no constraints in place. In this lab, you will implement the required constraints to ensure data integrity. You have been supplied with the required specifications of a new table called Marketing.Yield. You need to implement the required constraints and, if you have time, test their behavior.

22. Lab Review • In SQL Server Management Studio, you successfully ran a script that created a table but you don’t see the table in Object Explorer. What do you need to do? • What does the option Default do when creating a column? • What requirement does a primary key constraint have that a unique constraint doesn’t?

23. Module Review and Takeaways • Review Questions • Best Practices