COMP 101 Lecture Notes - Lecture 7: Data Domain, Data Integrity, Referential Integrity
C
h
a
p
t
e
r
7
SQL: Data Definition
Chapter 7 - Objectives
Data types supported by SQL standard.
Purpose of integrity enhancement feature of SQL.
How to define integrity constraints using SQL.
How to use the integrity enhancement feature in the CREATE and ALTER TABLE statements.
Chapter 7 - Objectives
Purpose of views.
How to create and delete views using SQL.
How the DBMS performs operations on views.
Under what conditions views are updatable.
Advantages and disadvantages of views.
How the ISO transaction model works.
How to use the GRANT and REVOKE statements as a level of security.
CREATE TABLE
CREATE TABLE TableName
{(colName dataType [NOT NULL] [UNIQUE]
[DEFAULT defaultOption]
[CHECK searchCondition] [,...]}
[PRIMARY KEY (listOfColumns),]
{[UNIQUE (listOfColumns),] […,]}
{[FOREIGN KEY (listOfFKColumns)
REFERENCES ParentTableName [(listOfCKColumns)],
[ON UPDATE referentialAction]
[ON DELETE referentialAction ]] [,…]}
{[CHECK (searchCondition)] [,…] })
ISO SQL Data Types
Integrity Enhancement Feature
Consider five types of integrity constraints:
–required data
–domain constraints
–entity integrity
–referential integrity
–general constraints.
Integrity Enhancement Feature
Required Data
position VARCHAR(10) NOT NULL
Domain Constraints
(a) CHECK
sex CHAR NOT NULL
CHECK (sex IN (‘M’, ‘F’))
I
n
t
e
g
r
i
t
y
E
n
h
a
n
c
e
m
e
n
t
F
e
a
t
u
r
e
(b) CREATE DOMAIN
CREATE DOMAIN DomainName [AS] dataType
[DEFAULT defaultOption]
[CHECK (searchCondition)]
For example:
CREATE DOMAIN SexType AS CHAR
CHECK (VALUE IN (‘M’, ‘F’));
sex SexType NOT NULL
Integrity Enhancement Feature
searchCondition can involve a table lookup:
CREATE DOMAIN BranchNoType
AS CHAR(4)
CHECK (VALUE IN (SELECT branchNo
FROM Branch));
Domains can be removed using DROP DOMAIN:
DROP DOMAIN DomainName
[RESTRICT | CASCADE]
IEF - Entity Integrity
Primary key of a table must contain a unique, non-null value for each row.
ISO standard supports FOREIGN KEY clause in CREATE and ALTER TABLE statements:
PRIMARY KEY(staffNo)
PRIMARY KEY(clientNo, propertyNo)
Can only have one PRIMARY KEY clause per table. Can still ensure uniqueness for alternate keys
using UNIQUE:
UNIQUE(telNo)
IEF - Referential Integrity
FK is column or set of columns that links each row in child table containing foreign FK to row of
parent table containing matching PK.
Referential integrity means that, if FK contains a value, that value must refer to existing row in
parent table.
ISO standard supports definition of FKs with FOREIGN KEY clause in CREATE and ALTER
TABLE:
FOREIGN KEY(branchNo) REFERENCES Branch
IEF - Referential Integrity
Any INSERT/UPDATE attempting to create FK value in child table without matching CK value in
parent is rejected.
Action taken attempting to update/delete a CK value in parent table with matching rows in child is
dependent on referential action specified using ON UPDATE and ON DELETE subclauses:
–CASCADE - SET NULL
–SET DEFAULT - NO ACTION
I
E
F
-
R
e
f
e
r
e
n
t
i
a
l
I
n
t
e
g
r
i
t
y
CASCADE: Delete row from parent and delete matching rows in child, and so on in cascading manner.
SET NULL: Delete row from parent and set FK column(s) in child to NULL. Only valid if FK columns
are NOT NULL.
SET DEFAULT: Delete row from parent and set each component of FK in child to specified default.
Only valid if DEFAULT specified for FK columns.
NO ACTION: Reject delete from parent. Default.
IEF - Referential Integrity
FOREIGN KEY (staffNo) REFERENCES Staff ON DELETE SET NULL
FOREIGN KEY (ownerNo) REFERENCES Owner ON UPDATE CASCADE
IEF - General Constraints
Could use CHECK/UNIQUE in CREATE and ALTER TABLE.
Similar to the CHECK clause, also have:
CREATE ASSERTION AssertionName
CHECK (searchCondition)
IEF - General Constraints
CREATE ASSERTION StaffNotHandlingTooMuch
CHECK (NOT EXISTS (SELECT staffNo
FROM PropertyForRent
GROUP BY staffNo
HAVING COUNT(*) > 100));
Used in CREATE and ALTER TABLE
CREATE TABLE
CREATE TABLE TableName
{(colName dataType [NOT NULL] [UNIQUE]
[DEFAULT defaultOption]
[CHECK searchCondition] [,...]}
[PRIMARY KEY (listOfColumns),]
{[UNIQUE (listOfColumns),] […,]}
{[FOREIGN KEY (listOfFKColumns)
REFERENCES ParentTableName [(listOfCKColumns)],
[ON UPDATE referentialAction]
[ON DELETE referentialAction ]] [,…]}
{[CHECK (searchCondition)] [,…] })
CREATE TABLE
Creates a table with one or more columns of the specified dataType.
With NOT NULL, system rejects any attempt to insert a null in the column.
Can specify a DEFAULT value for the column.
Primary keys should always be specified as NOT NULL.
FOREIGN KEY clause specifies FK along with the referential action.
Document Summary
Purpose of integrity enhancement feature of sql. How to define integrity constraints using sql. How to use the integrity enhancement feature in the create and alter table statements. How to create and delete views using sql. How the dbms performs operations on views. How to use the grant and revoke statements as a level of security. Domains can be removed using drop domain: Primary key of a table must contain a unique, non-null value for each row. Iso standard supports foreign key clause in create and alter table statements: Can only have one primary key clause per table. Can still ensure uniqueness for alternate keys using unique: Fk is column or set of columns that links each row in child table containing foreign fk to row of parent table containing matching pk. Referential integrity means that, if fk contains a value, that value must refer to existing row in parent table.
