Constraints

Foreign Keys
Local and Global Constraints

1
 Constraints
A constraint is a relationship among data
elements that the DBMS is required to
enforce.
 Example: key constraints.

2
 Kinds of Constraints
Keys.
Foreign-key, or referential-integrity.
Value-based constraints.
 Constrain values of a particular attribute.
Tuple-based constraints.
 Relationship among components.

3
 Review: Single-Attribute Keys
Place PRIMARY KEY or UNIQUE after the
type in the declaration of the attribute.
Example:
CREATE TABLE MovieStar (
name CHAR(30) UNIQUE,
address VARCHAR(255),
Gender CHAR(1),
birthdate DATE
);
4
 Review: Multiattribute Key
The title and year together are the key for Movies:
CREATE TABLE Movies(
title CHAR(100),
year INT,
length INT,
genre CHAR(10),
studioName CHAR(30),
producerC# INT,
PRIMARY KEY(title, year)
); 5
 Foreign Keys
Values appearing in attributes of one
relation must appear together in certain
attributes of another relation.
Example: in Studio(name, address, presC#),
we might expect that a presC# value also
appears in [Link]#.
MovieExec(name, address, cert#, netWorth)

6
 Expressing Foreign Keys
 Use keyword REFERENCES, either:
1. After an attribute (for one-attribute keys).
REFERENCES <relation> (<attributes>)

2. As an element of the schema:

FOREIGN KEY (<list of attributes>)
REFERENCES <relation> (<attributes>)
 Referenced attributes must be declared
PRIMARY KEY or UNIQUE.
7
 Example: With Attribute
CREATE TABLE MovieExec (
name CHAR(20),
address VARCHAR(255),
cert# INT PRIMARY KEY,
netWorth INT
);

CREATE TABLE Studio (

name CHAR(30) PRIMARY KEY,
address VARCHAR(255),
presC# INT REFERENCES MovieExec(cert#)
);
8
 Example: As Schema Element
CREATE TABLE MovieExec (
name CHAR(20),
address VARCHAR(255),
cert# INT PRIMARY KEY,
netWorth INT
);

CREATE TABLE Studio (

name CHAR(30) PRIMARY KEY,
address VARCHAR(255),
presC# INT
FOREIGN KEY(presC#) REFERENCES
MovieExec(cert#));
9
Enforcing Foreign-Key Constraints
 If there is a foreign-key constraint
from relation R to relation S, two
violations are possible:
1. An insert or update to R introduces
values not found in S.
2. A deletion or update to S causes some
tuples of R to “dangle.”

10
 Actions Taken --- (1)
Example: suppose R = Studio, S = MovieExec.
An insert or update to Studio that introduces a
nonexistent of this certificate number in
MovieExec must be rejected.

S = MovieExec R = Studio
Name Address Cert# netWorth name Address presC#
Calvin Fast lane 123 20000 Fox pico 555
George Oak rd. 555 11300 MGM vista 123

Para. Melrose 45011

 Actions Taken --- (2)
Example: suppose R = Studio, S = MovieExec.
A deletion or update to MovieExec that
removes a cert# value found in some tuples of
Studio can be handled in three ways (next slide).

S = MovieExec R = Studio
Name Address Cert# netWorth name Address presC#
Calvin Fast lane 123 20000 Fox pico 555
George Oak rd. 555 11300 MGM vista 123

12
 Actions Taken --- (3)

1. Default : Reject the modification.

2. Cascade : Make the same changes in
Studio.
 Deleted cert#: delete Studio tuple.
 Updated cert#: change value in Studio.
3. Set NULL : Change PresC to NULL.

13
 Example: Default Policy -1
Delete George tuple from MovieExec:
 Then reject the modification.

MovieExec Studio
Name Address Cert# netWorth name Address presC#
Calvin Fast lane 123 20000 Fox pico 555
George Oak rd. 555 11300 MGM vista 123

14
 Example: Default Policy -2
Update George tuple by changing 555 to
157:
 Then reject the modification.

MovieExec Studio
Name Address Cert# netWorth name Address presC#
Calvin Fast lane 123 20000 Fox pico 555
George Oak rd. 555 11300 MGM vista 123

15
 Example: Cascade Policy -1
Delete George tuple from MovieExec:
 Then delete all tuples from Studio that have
presC# = 555.

MovieExec Studio
Name Address Cert# netWorth name Address presC#
Calvin Fast lane 123 20000 Fox pico 555
George Oak rd. 555 11300 MGM vista 123

16
 Example: Cascade Policy -2
Update George tuple by changing 555 to
157:
 Then change all Studio tuples with presC# = 555
to presC# = 157.

MovieExec Studio
Name Address Cert# netWorth name Address presC#
Calvin Fast lane 123 20000 Fox pico 555
157
George Oak rd. 555
157 11300 MGM vista 123

17
 Example: Set-NULL Policy -1
Delete George tuple from MovieExec:
 Change all tuples of Studio that have presC#=
555 to have presC# = NULL.

MovieExec Studio
Name Address Cert# netWorth name Address presC#
Calvin Fast lane 123 20000 Fox pico 555
NULL
George Oak rd. 555 11300 MGM vista 123

18
 Example: Set-NULL Policy -2
Update George tuple by changing 555 to
157:
 Change all tuples of Studio that have presC#=
555 to have presC# = NULL.

MovieExec Studio
Name Address Cert# netWorth name Address presC#
Calvin Fast lane 123 20000 Fox pico 555
NULL
George Oak rd. 555
157 11300 MGM vista 123

19
 Choosing a Policy
When we declare a foreign key, we may
choose policies SET NULL or CASCADE
independently for deletions and updates.

Follow the foreign-key declaration by:

ON [UPDATE, DELETE][SET NULL, CASCADE]

Two such clauses may be used.

Otherwise, the default (reject) is used.

20
 Example: Setting Policy
CREATE TABLE Studio (
name CHAR(30) PRIMARY KEY,
address VARCHAR(255),
presC# INT REFERENCES
MovieExec(cert#)
ON DELETE SET NULL
ON UPDATE CASCADE
);

21
 Constraints on Attributes and
Tuples
Within a SQL CREATE TABLE statement
we can declare two kinds of constraints:
1. A constraint on a single attribute
2. A constraint on a tuple as a whole.

22
 Not-Null constraints
Disallow tuples in which this attribute is
NULL.
CREATE TABLE Studio (
name CHAR(30) PRIMARY KEY,
address VARCHAR(255),
presC# INT REFERENCES MovieExec(cert#)NOT NULL
);

CREATE TABLE Studio (

name CHAR(30) PRIMARY KEY,
address VARCHAR(255) NOT NULL,
presC# INT NOT NULL
23
);
 Attribute-Based Checks
Constraints on the value of a particular
attribute.
Add CHECK(<condition>) to the
declaration for the attribute.
The condition may use the name of the
attribute, but any other relation or
attribute name must be in a subquery.

24
Example 1: Attribute-Based Check
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)
);

25
Example 2: Attribute-Based Check
Gender from
MovieStar(name,address,gender, birthdate)

CREATE TABLE MovieStar (

…,
…,
gender CHAR(1) CHECK (gender IN(‘F’,’M’)),
…
);

26
Example 3: Attribute-Based Check
CREATE TABLE Studio (
name CHAR(30) PRIMARY KEY,
address VARCHAR(255),
presC# INT CHECK
(presC# IN(SELECT cert# FROM MovieExec))
);

27
 Timing of Checks
Attribute-based checks are performed only
when a value for that attribute is inserted or
updated.
 Example: CHECK (presC#>=100000) checks
every new certificate number and rejects the
modification (for that tuple) if the certificate
number is less than 100000.
 Example: CHECK (presC# IN(SELECT cert#
FROM MovieExec))not checked if a certificated
number is deleted from MovieExec (unlike
foreign-keys).
28
 Tuple-Based Checks
CHECK (<condition>) may be added as
a relation-schema element.
The condition may refer to any
attribute of the relation.
 But other attributes or relations require a
subquery.
Checked on insert or update only.

29
 Example: Tuple-Based Check
If the star‟s gender is male, then his name must not
begin with „Ms.‟:
CREATE TABLE MovieStar (
name CHAR(30) PRIMARY KEY,
address VARCHAR(255),
gender CHAR(1),
birthdate DATE,
CHECK (gender = ‘F’ OR name NOT LIKE ‘Ms.%’)
);

30
 Modification of Constraints
Giving names to constraints
 In order to modify or delete an existing
constraint.
 Attribute type CONSTRAINT constraint_name
constraint_type
Example (primary key):
CREATE TABLE MovieStar (
name CHAR(30) CONSTRAINT nameIsKey PRIMARY
KEY,
address VARCHAR(255),
gender CHAR(1),
birthdate DATE
); 31
 Modification of Constraints
Example (check):
CREATE TABLE MovieStar (
name CHAR(30) PRIMARY KEY,
address VARCHAR(255),
gender CHAR(1) CONSTRAINT NoAndro CHECK
(gender IN(‘F’,’M’)),
birthdate DATE
);

32
 Modification of Constraints
Example (tuple-based check):
CREATE TABLE MovieStar (
name CHAR(30) PRIMARY KEY,
address VARCHAR(255),
gender CHAR(1),
birthdate DATE,
CONSTRAINT RightTitle
CHECK(gender=‘F’ OR name NOT LIKE ‘Ms.%’)
);

33
 Modification of Constraints

Altering Constraints on Tables

 ALTER TABLE statement can affect constraints
in several ways:
• Drop a constraint: DROP
• Add a constraint: ADD

34
 Examples
ALTER TABLE MovieStar DROP CONSTRAINT NameIsKey;
ALTER TABLE MovieStar DROP CONSTRAINT NoAndro;
ALTER TABLE MovieStar DROP CONSTRAINT RightTitle;

ALTER TABLE MovieStar ADD CONSTRAINT NameIsKey

PRIMARY KEY(name);
ALTER TABLE MovieStar ADD CONSTRAINT NoAndro
CHECK(gender IN(„F‟,‟M‟));
ALTER TABLE MovieStar ADD CONSTRAINT RightTitle
CHECK(gender =„F‟ OR name NOT LIKE „Ms.%‟);
35