SQL Integrity Constraints
Integrity Constraints (Review)
❑ AnICdescribesconditionsthateverylegalinstanceofa relation must satisfy.
Copyright By PowCoder代写 加微信 powcoder
✩ Restrictions on attribute values of tuples
❑ Inserts/deletes/updates that violate IC’s are disallowed.
❑ Covered so far:
✩ Onindividualtuples
● Domain constraints:
▲ Data type: name must be a string ▲ NOT NULL
✩ Forrelationasawhole
● Primary Key and Unique Constraints:
▲ no two tuples may have the same value ✩ Acrossrelations
● Referential integrity through foreign key constraint: ▲ sid is a foreign key in relation Participates;
421B: Database Systems – Integrity Constraints
Rule of thumb to classify an IC
❑ Ifyoucanonly”see”onlythattupleandtheschema definition, can you say if it will fail the IC ?
✩ YES è attribute / tuple level IC :
● Ex: NULL value in the Tuple for an attribute defined to be NOT NULL in the
● Ex: CHARACTER value in the Tuple for an attribute defined to be an INTEGER
❑ Ifyoucanonly”see”allthetuplesalreadyinthetable and the schema definition, can you say if it will fail the IC
the table.
?✩ YES è Table level IC :
● Ex: The new tuple has value for its PRIMARY KEY, which is already present in
421B: Database Systems – Integrity Constraints
Attribute-Based Checks
❑ If a condition must hold for specific attribute: CHECK CREATE TABLE Skaters (
sid INTEGER PRIMARY KEY NOT NULL,
sname VARCHAR(20),
rating INTEGER CHECK(rating > 0 AND rating < 11),
age INTEGER)
❑ Condition is checked only when the associated attribute changes (i.e., an insert or update, but not delete!)
❑ If condition is violated the system rejects the modification
❑ In SQL condition can be anything that could follow WHERE clause
✩ including subqueries
❑ Most database systems allow very restricted attribute-based check (no
subqueries, no reference to other attributes, ...)
421B: Database Systems - Integrity Constraints
Tuple-Based Checks ❑ If a condition covers several attributes
CREATE TABLE Skaters (
sid INTEGER PRIMARY KEY NOT NULL,
sname VARCHAR(20),
rating INTEGER,
age INTEGER,
CHECK (rating <= 4 OR age > 5))
r Checked upon each update and insert
421B: Database Systems – Integrity Constraints
Naming constraints ❑ Problemofpreviousexamples:
✩ what if constraints change (e.g., we want to increase rating constraint to (rating <=5 OR age > 5)
❑ Solution:nameconstraints:
CREATE TABLE Skaters (
sid INT NOT NULL,
sname VARCHAR(20),
rating INT CONSTRAINT rat CHECK
(rating > 0 AND rating < 11),
CONSTRAINT pk PRIMARY KEY (sid),
CONSTRAINT ratage CHECK
❑ Thisallowsustodropandrecreatethemlateron ALTER TABLE Skaters DROP CONSTRAINT ratage
ALTER TABLE Skaters ADD CONSTRAINT ratage
CHECK (rating <=5 OR age > 5)
❑ what if there is already a record with rating = 11 and age = 2 ? 421B: Database Systems – Integrity Constraints
(rating <= 4 OR age > 5))
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com