module 05_implementing data integrity

8/9/2019 Module 05_Implementing Data Integrity

1/36

Module 5:Implementing

Data Integrity

Vidya Vrat Agarwal. | MCT, MCSD


2/36

Overview

Types of Data Integrity

Enforcing Data Integrity

Defining Constraints

Types of Constraints

Using Defaults and Rules

Deciding Which Enforcement Method to Use


3/36


Domain Integrity(columns)

Entity Integrity (rows)

Referential Integrity(between tables)

Data integrity refers to theConsistency and Accuracy of

data that is stored in a database.


4/36


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


5/36


Determining Which Type of Constraint to Use

Creating Constraints

Considerations for Using Constraints

Constraints are the preferred method of enforcing data

integrity.


6/36

Determining Which Type of Constraint to Use

Type of integrity

Type of integrityType of integrity Constraint type

Constraint typeConstraint type

Domain

DomainDEFAULT

DEFAULT

CHECK

CHECK

REFERENTIAL

REFERENTIAL

Entity

Entity PRIMARY KEY

PRIMARY KEY

UNIQUE

UNIQUE

Referential

Referential FOREIGN KEY

FOREIGN KEY

CHECK

CHECK


7/36

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


8/36

Considerations for Using Constraints

Can Be Changed Without Recreating a TableYou can create, change, and drop constraints without having to drop andrecreate a table.

Require Error-Checking in Applications and TransactionsYou must build error-checking logic into your applications and transactions

to test whether a constraint has been violated.

Verify Existing DataSQL Server verifies existing data when you add a constraint to a table.

You should specify names for constraints when you create them,

because SQL Server provides complicated, system-generatednames. Names must be unique to the database object owner and

follow the rules for SQL Server identifiers.


9/36


DEFAULT Constraints

CHECK Constraints

PRIMARY KEY Constraints

UNIQUE Constraints

FOREIGN KEY Constraints

Cascading Referential Integrity


10/36


11/36

CHECK Constraints

Are Used with INSERT and UPDATE StatementsA CHECK constraint restricts the data that users can enter into aparticular column to specific values. CHECK constraints are similar toWHERE clauses in that you can specify the conditions under which datawill be accepted.

It verifies data every time that you execute an INSERT orUPDATE statement.

USE NorthwindALTER TABLE EmployeesADD CONSTRAINT CK_birthdate

CHECK ( BirthDate >'01-01-1900'ANDBirthDate'01-01-1900'AND

BirthDate


12/36

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 CustomersADD CONSTRAINT PK_CustomersPRIMARY KEY NONCLUSTERED (CustomerID)

USE NorthwindALTER TABLE CustomersADD CONSTRAINT PK_Customers

PRIMARY KEY NONCLUSTERED (CustomerID)

A PRIMARY KEY constraint defines a primary key on a table thatuniquely identifies a row. It enforces entity integrity.


13/36

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 NorthwindALTER TABLE SuppliersADD CONSTRAINT U_CompanyName

UNIQUE NONCLUSTERED (CompanyName)

USE NorthwindALTER TABLE SuppliersADD CONSTRAINT U_CompanyName

UNIQUE NONCLUSTERED (CompanyName)

A UNIQUE constraint specifies that two rows in a column cannot have thesame value. This constraint enforces entity integrity with a unique index.

A UNIQUE constraint is helpful when you already have a primary key, such

as an employee number, but you want to guarantee that other identifiers,

such as an employees drivers license number, are also unique.


14/36

FOREIGN KEY Constraints

Must Reference a PRIMARY KEY or UNIQUE Constraint

Use Only REFERENCES Clause Within Same Table

USE NorthwindALTER TABLE Orders

ADD CONSTRAINT FK_Orders_CustomersFOREIGN KEY (CustomerID)REFERENCES Customers(CustomerID)

USE NorthwindALTER TABLE Orders

ADD CONSTRAINT FK_Orders_CustomersFOREIGN KEY (CustomerID)REFERENCES Customers(CustomerID)

A FOREIGN KEY constraint enforces referential integrity. The FOREIGNKEY constraint defines a reference to a column with a PRIMARY KEY or

UNIQUE constraint in the same, or another table.


15/36


The FOREIGN KEY constraint includes a CASCADE option that allowsany change to a column value that defines a UNIQUE or PRIMARY KEYconstraint to automatically propagate the change to the foreign keyvalue. This action is referred to as cascading referential integrity.

[CONSTRAINT constraint_name] [FOREIGN KEY] [(column[,n])]

REFERENCES ref_table [(ref_column [,n])].

[ ON DELETE { CASCADE | NO ACTION } ]

[ ON UPDATE { CASCADE | NO ACTION } ]

NO ACTION specifies that any attempt to delete or update a keyreferenced by foreign keys in other tables raises an error and the changeis rolled back. NO ACTION is the default.

If CASCADE is defined and a row is changed in the parent table, thecorresponding row is then changed in the referencing table.


16/36


CASCADENO ACTIONCustomersCustomersCustomers

INSERT newCustomerID

INSERT newCustomerID

CustomerID (PK)

11

OrdersOrdersOrders

CustomerID (FK)

UPDATE oldCustomerID to newCustomerID

UPDATE oldCustomerID to newCustomerID

22

CustomersCustomersCustomers

CustomerID (PK)

UPDATE CustomerIDUPDATE CustomerID

OrdersOrdersOrders

CustomerID (FK)

11

CASCADE

CustomersCustomersCustomers

DELETE oldCustomerID

DELETE oldCustomerID

CustomerID (PK)

33


17/36


As Independent Objects They:

Are defined once and Can be bound to one or morecolumns or user-defined data types

CREATE DEFAULT phone_no_defaultAS '(000)000-0000'

GOEXEC sp_bindefault phone_no_default,

'Customers.Phone'

CREATE DEFAULT phone_no_defaultAS '(000)000-0000'

GOEXEC sp_bindefault phone_no_default,

'Customers.Phone'

CREATE RULE regioncode_ruleAS @regioncode IN ('IA', 'IL', 'KS', 'MO')

GOEXEC sp_bindrule regioncode_rule,

'Customers.Region'

CREATE RULE regioncode_ruleAS @regioncode IN ('IA', 'IL', 'KS', 'MO')

GOEXEC sp_bindrule regioncode_rule,

'Customers.Region'

Table Name

Column Name


18/36

Unbinding Defaults and Rules

After you create a default, you must bind it to a column oruser-defined data type by executing the sp_bindefaultsystem stored procedure.

To detach a default, execute the sp_unbindefault system

stored procedure.

After you create a rule, you must bind it to a column or user-defined data type by executing the sp_bindrule systemstored procedure.

To detach a rule, execute the sp_unbindrule system storedprocedure.


19/36

Dropping a Default or Rule

The DROP statement removes a default or rule from the database.

DROP DEFAULT default[,...n]

DROP RULE rule [, ...n]


20/36


Data integritycomponents

Data integrityData integrity

componentscomponents

ConstraintsConstraints

Defaults and rulesDefaults and rules

TriggersTriggers

FunctionalityFunctionalityFunctionality

MediumMedium

LowLow

HighHigh

Performancecosts

PerformancePerformance

costscosts

LowLow

LowLow

Medium-HighMedium-High

Before or aftermodification

Before or afterBefore or after

modificationmodification

BeforeBefore

BeforeBefore

AfterAfter

Data types,Null/Not Null

Data types,Null/Not Null Low

Low LowLow BeforeBefore


21/36

Check Your Understanding.


22/36

Q.1. What is Data Integrity.? What are the Types of data

integrity.?


23/36

Q.2. What are the ways to Enforce Data Integrity.?


24/36

Q.3. What is Default constraint.?


25/36

Q.4. What is Check Constraint.?


26/36

Q.5. What is Primary Key.?


27/36

Q.6. What is Foreign Key.?


28/36

Q.7. What is Unique Constraint.?


29/36

Q.8. Primary Key allows Null Values. ( T / F)


30/36

Q.9. How many Null values are allowed by Unique

Constraint .?


31/36

Q.10. What is the difference between Default and Rule.?


32/36

Q.11. Which two commands work in Combination to

Create and bind Rule and Default..?


33/36

Q.12. What is Cascading .? What is the action of

No Action.?


34/36

Q.13. What is the difference between Unbinding andDrop.?


35/36

Review








36/36

Motivation to SucceedComes from

the Burning DesireThank You.

module 05_implementing data integrity

Documents