Database System: Foreign Key Constraints and Referential Integrity Maintenance, Summaries of Database Programming

Download Database System: Foreign Key Constraints and Referential Integrity Maintenance and more Summaries Database Programming in PDF only on Docsity!

Chapter 2

Constraints, Triggers, Views

Database System – The complete book,

Hector Garcia-Molina, Jeffrey D. Ullman, Jennifer Widom

Chapter Outline

Keys and Foreign Key

Constraints on Attributes and Tuples

Modification of Constraints

Trigger

Views

Two ways to declare a foreign

key.

• (^) Example:

Suppose we wish to declare the relation

Studio(name, address, presC#)

which has a foreign key presC# that references

cert# of relation:

MovieExec(name, addreses, cert#, netWorth)

We may declare presC# directly to reference cert#

as follows:

CREATE TABLE Studio (

name CHAR(30) PRIMARY KEY,

address VARCHAR(255),

presC# INT REFERENCES

MovieExec(cert#));

4

Two ways to declare a foreign key (cont.).

• (^) Example:

An alternative form is to add the foreign key

declaration separately, as

CREATE TABLE Studio (

name CHAR(30) PRIMARY KEY,

address VARCHAR(255),

presC# INT,

FOREIGN KEY (presC#) REFERENCES

MovieExec(cert#)

Maintaining Referential Integrity

(cont.)

• (^) For the first two modifications, where the change

is to the relation where the foreign-key constraint

is declared, there is no alternative; the system

has to reject the violating modification.

• (^) For changes to the referenced relation, of which

the last two modifications are examples, the

designer can choose among three options:

1.The Default Policy: Reject Violating Modifications.

2.The Cascade Policy: Under this policy, changes to

the referenced attribute(s) are mimicked at the

foreign key.

3.The Set-Null Policy.

Maintaining Referential Integrity

(cont.)

Example:

CREATE TABLE Studio (

name CHAR(30) PRIMARY KEY,

address VARCHAR(255),

presC# INT REFERENCES

MovieExec(cert#)

ON DELETE SET NULL

ON UPDATE CASCADE

Constraints on Attributes and Tuples

(cont.).

 Attribute-Based CHECK Constraints :

Example: Suppose we want to require that

certificate numbers be at least six digits.

CREATE TABLE Studio (

name CHAR(30) PRIMARY KEY,

address VARCHAR(255),

presC# INT REFERENCES MovieExec(cert#)

CHECK (presC# >= 100000)

);

• (^) We could not insert a tuple into Studio by

specifying only the name and address.

• (^) We could not use the set-null policy

Tuple-Based CHECK Constraints :

Example:

CREATE TABLE MovieStar (

name CHAR(30) PRIMARY KEY,

address VARCHAR(255),

gender CHAR(1),

birthdate DATE,

CHECK (gender = ’F’ OR name NOT LIKE ’Ms.

Constraints on Attributes and Tuples

(cont.).

Modification of Constraints.

Altering Constraints on Tables:

Example:

ALTER TABLE MovieStar DROP CONSTRAINT

NamelsKey;

ALTER TABLE MovieStar ADD CONSTRAINT

NamelsKey

PRIMARY KEY (name);

Triggers.

• (^) A trigger is a series of actions that are associated

with certain events, and that are performed

whenever these events arise.

• Triggers differ from the kinds of constraints in

three points:

1.Triggers are only awakened when certain events,

specified by the database programmer, occur

(usually insert, delete, or update).

2.Once awakened by its triggering event, the

trigger tests a condition.

3.If the condition of the trigger is satisfied, the

action associated with the trigger is performed

by the DBMS.

Triggers, sometimes called event-condition-

action rules or ECA rules

The Options for Trigger Design

We may replace AFTER by BEFORE (line 2) in

which case the WHEN condition is tested on

the database state that exists before the

triggering event is executed.

Besides UPDATE (line 2), other possible

triggering events are INSERT and DELETE.

The WHEN clause is optional. If it is missing,

then the action is executed whenever the

trigger is awakened.

There can be any number of such statements

(line 8-10), separated by semicolons and

surrounded by BEGIN...END.

The Options for Trigger Design

• (^) If we omit the FOR EACH ROW on line (6) or

replace it by the default FOR EACH

STATEMENT, then a row-level trigger becomes

a statement-level trigger.

• (^) A statement-level trigger is executed once

whenever a statement of the appropriate type

is executed, no matter how many rows — zero,

one, or many — it actually affects.

In a statement-level trigger, we cannot refer to

old and new tuples directly (as line 4,5), but

using declarations such as OLD TABLE AS

OldStuff and NEW TABLE AS NewStuff.

Create trigger in SQL server

CREATE TRIGGER Trigger_name ON {table | View}

{FOR | AFTER | INSTEAD OF} { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }

AS { sql_statement [ ; ] [ ,...n ] [ ; ] > }

• Arguments
• FOR | AFTER : AFTER specifies that the DML trigger is fired only when

all operations specified in the triggering SQL statement have

executed successfully. All referential cascade actions and constraint

checks also must succeed before this trigger fires. AFTER is the

default when FOR is the only keyword specified. AFTER triggers

cannot be defined on views.

• INSTEAD OF: Specifies that the DML trigger is executed instead

of the triggering SQL statement, therefore, overriding the actions of

the triggering statements. INSTEAD OF cannot be specified for DDL

or logon triggers. At most, one INSTEAD OF trigger per INSERT,

UPDATE, or DELETE statement can be defined on a table or view.

INSTEAD OF triggers are not allowed on updatable views that use

WITH CHECK OPTION.

• DML triggers are frequently used for enforcing business rules and

data integrity.

19

• (^) Example 1

The following DML trigger prints a message to the

client when anyone tries to add or change data in

the Customertable in the AdventureWorks

database.

IF OBJECT_ID ('Sales.reminder1', 'TR') IS NOT NULL

DROP TRIGGER Sales.reminder1;

GO

CREATE TRIGGER reminder1 ON Sales.Customer

AFTER INSERT, UPDATE

AS RAISERROR ('Notify Customer Relations', 16, 10);

GO

Create trigger in SQL server