Module 5: Implementing Data IntegrityOverviewTypes of Data IntegrityEnforcing Data IntegrityDefining ConstraintsDetermining Which Type of Constraint to UseCreating ConstraintsConsiderations for Using ConstraintsTypes of ConstraintsDEFAULT ConstraintsCHECK ConstraintsPRIMARY KEY ConstraintsUNIQUE ConstraintsFOREIGN KEY ConstraintsCascading Referential IntegrityDisabling ConstraintsDisabling Constraint Checking on Existing DataDisabling Constraint Checking When Loading New DataUsing Defaults and RulesDeciding Which Enforcement Method to UseRecommended PracticesPowerPoint PresentationReviewModule 5: Implementing Data IntegrityOverviewTypes of Data IntegrityEnforcing Data IntegrityDefining ConstraintsTypes of ConstraintsDisabling ConstraintsUsing Defaults and RulesDeciding Which Enforcement Method to UseTypes of Data IntegrityDomain Integrity(columns)Entity Integrity (rows)Referential Integrity(between tables)Enforcing Data IntegrityDeclarative Data IntegrityCriteria defined in object definitionsSQL Server enforces automaticallyImplement by using constraints, defaults, and rulesProcedural Data IntegrityCriteria defined in scriptScript enforcesImplement by using triggers and stored procedures Defining ConstraintsDetermining Which Type of Constraint to Use Creating ConstraintsConsiderations for Using ConstraintsDetermining Which Type of Constraint to UseType of integrityType of integrityType of integrityType of integrityConstraint typeConstraint typeConstraint typeConstraint typeDomainDomainDEFAULTDEFAULTCHECKCHECKREFERENTIALREFERENTIALEntityEntityPRIMARY KEYPRIMARY KEYUNIQUEUNIQUEReferentialReferentialFOREIGN KEYFOREIGN KEYCHECKCHECKCreating ConstraintsUse CREATE TABLE or ALTER TABLECan Add Constraints to a Table with Existing Data Can Place Constraints on Single or Multiple ColumnsSingle column, called column-level constraintMultiple columns, called table-level constraintConsiderations for Using ConstraintsCan Be Changed Without Recreating a TableRequire Error-Checking in Applications and TransactionsVerify Existing Data Types of ConstraintsDEFAULT ConstraintsCHECK ConstraintsPRIMARY KEY ConstraintsUNIQUE ConstraintsFOREIGN KEY ConstraintsCascading Referential IntegrityDEFAULT ConstraintsApply Only to INSERT StatementsOnly One DEFAULT Constraint Per ColumnCannot Be Used with IDENTITY Propertyor rowversion Data TypeAllow Some System-supplied ValuesUSE NorthwindALTER TABLE dbo.CustomersADDCONSTRAINT DF_contactname DEFAULT 'UNKNOWN' FOR ContactName USE NorthwindALTER TABLE dbo.CustomersADDCONSTRAINT DF_contactname DEFAULT 'UNKNOWN' FOR ContactNameCHECK ConstraintsAre Used with INSERT and UPDATE StatementsCan Reference Other Columns in the Same TableCannot:Be used with the rowversion data typeContain subqueries USE NorthwindALTER TABLE dbo.EmployeesADD CONSTRAINT CK_birthdateCHECK (BirthDate > '01-01-1900' AND BirthDate < getdate())USE NorthwindALTER TABLE dbo.EmployeesADD CONSTRAINT CK_birthdateCHECK (BirthDate > '01-01-1900' AND BirthDate < getdate())PRIMARY KEY ConstraintsOnly One PRIMARY KEY Constraint Per TableValues Must Be UniqueNull Values Are Not AllowedCreates a Unique Index on Specified ColumnsUSE NorthwindALTER TABLE dbo.Customers ADD CONSTRAINT PK_Customers PRIMARY KEY NONCLUSTERED (CustomerID)USE NorthwindALTER TABLE dbo.Customers ADD CONSTRAINT PK_Customers PRIMARY KEY NONCLUSTERED (CustomerID)UNIQUE ConstraintsAllow One Null ValueAllow Multiple UNIQUE Constraints on a TableDefined with One or More ColumnsEnforced with a Unique IndexUSE NorthwindALTER TABLE dbo.Suppliers ADD CONSTRAINT U_CompanyName UNIQUE NONCLUSTERED (CompanyName)USE NorthwindALTER TABLE dbo.Suppliers ADD CONSTRAINT U_CompanyName UNIQUE NONCLUSTERED (CompanyName)FOREIGN KEY ConstraintsMust Reference a PRIMARY KEY or UNIQUE ConstraintProvide Single or Multicolumn Referential IntegrityDo Not Automatically Create IndexesUsers Must Have SELECT or REFERENCES Permissions on Referenced TablesUse Only REFERENCES Clause Within Same TableUSE NorthwindALTER TABLE dbo.Orders ADD CONSTRAINT FK_Orders_Customers FOREIGN KEY (CustomerID) REFERENCES dbo.Customers(CustomerID)USE NorthwindALTER TABLE dbo.Orders ADD CONSTRAINT FK_Orders_Customers FOREIGN KEY (CustomerID) REFERENCES dbo.Customers(CustomerID)Cascading Referential IntegrityCASCADENO ACTIONCustomersCustomersCustomersCustomers INSERT new CustomerIDINSERT new CustomerIDCustomerID (PK)11OrdersOrdersOrdersOrders CustomerID (FK)UPDATE old CustomerID to new CustomerIDUPDATE old CustomerID to new CustomerID22CustomersCustomersCustomersCustomers CustomerID (PK)UPDATE CustomerIDUPDATE CustomerIDOrdersOrdersOrdersOrders CustomerID (FK)11CASCADECustomersCustomersCustomersCustomers DELETE old CustomerIDDELETE old CustomerIDCustomerID (PK)33 Disabling ConstraintsDisabling Constraint Checking on Existing DataDisabling Constraint Checking When Loading New DataDisabling Constraint Checking on Existing DataApplies to CHECK and FOREIGN KEY ConstraintsUse WITH NOCHECK Option When Adding a New ConstraintUse if Existing Data Will Not ChangeCan Change Existing Data Before Adding ConstraintsUSE NorthwindALTER TABLE dbo.Employees WITH NOCHECK ADD CONSTRAINT FK_Employees_Employees FOREIGN KEY (ReportsTo) REFERENCES dbo.Employees(EmployeeID)USE NorthwindALTER TABLE dbo.Employees WITH NOCHECK ADD CONSTRAINT FK_Employees_Employees FOREIGN KEY (ReportsTo) REFERENCES dbo.Employees(EmployeeID)Disabling Constraint Checking When Loading New DataApplies to CHECK and FOREIGN KEY ConstraintsUse When:Data conforms to constraintsYou load new data that does not conform to constraintsUSE NorthwindALTER TABLE dbo.Employees NOCHECK CONSTRAINT FK_Employees_Employees USE NorthwindALTER TABLE dbo.Employees NOCHECK CONSTRAINT FK_Employees_EmployeesUsing Defaults and RulesAs Independent Objects They:Are defined onceCan be bound to one or more columns or user-defined data typesCREATE DEFAULT phone_no_default AS '(000)000-0000' GOEXEC sp_bindefault phone_no_default, 'Customers.Phone' CREATE DEFAULT phone_no_default AS '(000)000-0000' GOEXEC sp_bindefault phone_no_default, 'Customers.Phone' CREATE RULE regioncode_rule AS @regioncode IN ('IA', 'IL', 'KS', 'MO')GOEXEC sp_bindrule regioncode_rule, 'Customers.Region' CREATE RULE regioncode_rule AS
View Full Document