Transcript Document

Constraints
•
We have discussed three types of integrity constraints: primary keys, not null constraints, and
unique constraints.
CREATE TABLE Movies (
title CHAR(40) PRIMARY KEY,
year INT,
length INT,
type CHAR(2)
);
CREATE TABLE Movies (
title CHAR(40),
year INT,
length INT,
type CHAR(2),
PRIMARY KEY (title, year)
);
CREATE TABLE ABC (
A number NOT NULL,
B number NULL,
C number
);
insert into ABC values (
1, null, null);
insert into ABC values (
2,
3,
4);
insert into ABC values (null,
5,
6);
The first two records can be inserted, the third cannot, throwing a
ORA-01400: cannot insert NULL into ("userschema"."ABC"."A").
The not null/null constraint can be altered with
ALTER TABLE ABC MODIFY A null;
After this modification, the column A can contain null values.
•
•
The UNIQUE constraint doesn't allow duplicate values in a column.
If the unique constraint encompasses two or more columns, no two equal
combinations are allowed.
CREATE TABLE AB (
A NUMBER UNIQUE,
B NUMBER
);
•
However, if a column is not explicitly defined as NOT NULL, nulls can be inserted
multiple times:
insert
insert
insert
insert
insert
insert
•
into
into
into
into
into
into
AB
AB
AB
AB
AB
AB
values
values
values
values
values
values
(4,
5);
(2,
1);
(9,
8);
(6,
9);
(null,9);
(null,9);
Now: trying to insert the number 2 again into A:
insert into AB values (2,7);
•
This statement issues a
ORA-00001: unique constraint (THOMO.SYS_C006985) violated
Constraint names
• Every constraint, by the way, has a name. In this case, the name is:
THOMO.SYS_C006985.
• In order to remove that constraint, an alter table ... drop constraint ... is needed:
ALTER TABLE AB DROP CONSTRAINT SYS_C006985;
• Of course, it is also possible to add a unique constraint on an existing table:
ALTER TABLE AB add CONSTRAINT my_unique_constraint UNIQUE (A);
• Here we name the constraint for easier handling.
• To find the constraint names and the tables on which the constraints are set do:
select CONSTRAINT_NAME, TABLE_NAME from user_constraints;
• The following example creates a unique constraint on the columns A and B and
names the constraint.
CREATE TABLE ABC (
It can’t have the same name as another
A number,
constraint even if it is in another table.
B number,
C number,
CONSTRAINT my_unique_constraint2 UNIQUE (A,B)
);
Foreign key constraints
• We specify a column or a list of columns as a foreign key of the referencing
table.
• The referencing table is called the childtable, and the referenced table is called
the parenttable.
• One cannot define a referential integrity constraint that refers to a table R before
that table R has been created.
• Example: Each employee in the table EMP must work in a department that is
contained in the table DEPT:
CREATE TABLE Emp (
empno NUMBER CONSTRAINT pk_emp PRIMARY KEY,
... ,
deptno NUMBER CONSTRAINT fk_deptno REFERENCES Dept(deptno)
);
Longer syntax for foreign keys
If you don’t specify primary keys or
unique constraints in the parent tables,
you cannot specify foreign keys in the
child tables.
CREATE TABLE MovieStars(
name VARCHAR2(20) PRIMARY KEY,
address VARCHAR2(30),
gender VARCHAR2(1),
birthdate VARCHAR2(20)
);
CREATE TABLE Movies (
title VARCHAR2(40),
year INT,
length INT,
type VARCHAR2(2),
PRIMARY KEY (title, year)
);
CREATE TABLE StarsIn (
title VARCHAR2(40),
year INT,
starName VARCHAR2(20),
CONSTRAINT fk_movies FOREIGN KEY(title,year) REFERENCES Movies(title,year),
CONSTRAINT fk_moviestars FOREIGN KEY(starName) REFERENCES MovieStars(name)
);
Foreign key constraints (cont.)
•
In order to satisfy a foreign key constraint, each row in the childtable has to satisfy one of
the following two conditions:
– the attribute value (list of attribute values) of the foreign key must appear as a
primary key value in the parenttable, or
– the attribute value of the foreign key is null
• in case of a composite foreign key, at least one attribute value of the foreign key is null
•
According to the above definition,
for table EMP, an employee must not necessarily work in a department,
i.e., for the attribute DEPTNO, the value null is admissible.
•
•
If we want to not allow NULL’s in a foreign key we must say so.
Example: There should always be a project manager, who must be an employee:
CREATE TABLE PROJECT (
PNO number(3) CONSTRAINT prj_pk PRIMARY KEY,
PMGR number(4) NOT NULL CONSTRAINT fk_pmgr REFERENCES EMP,
. . .
);
•
Because only the name of the parenttable is given (EMP), the primary key of this relation
is assumed.
Foreign key constraints (cont.)
• A foreign key constraint may also refer to the same table, i.e.,
parenttable and childtable are identical.
• Example: Every employee must have a manager who must be an
employee:
CREATE TABLE EMP (
empno NUMBER CONSTRAINT emp_pk PRIMARY KEY,
. . .
mgr NUMBER NOT NULL CONSTRAINT fk_mgr REFERENCES EMP,
. . .
);
Enforcing Foreign-Key Constraints
• If there is a foreign-key constraint from attributes of relation R to a key of
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.”
Example.
CREATE TABLE Beers (
name CHAR(20) PRIMARY KEY,
manf CHAR(20)
);
Relation S
Relation R
CREATE TABLE Sells (
bar
CHAR(20),
beer CHAR(20),
price REAL,
FOREIGN KEY(beer) REFERENCES Beers(name)
);
Action taken 1
•
•
An insert or update to Sells that introduces a nonexistent beer
must be rejected.
A deletion or update to Beers that removes a beer value found
in some tuples of Sells can be handled in three ways.
1. Default : Reject the modification.
2. Cascade : Make the same changes in Sells.
–
–
Deleted beer: delete Sells tuple.
Updated beer: change value in Sells.
3. Set NULL : Change the beer to NULL.
Example
Cascade
• Delete the Bud tuple from Beers:
– Then delete all tuples from Sells that have beer = 'Bud'.
• Update the Bud tuple by changing 'Bud' to 'Budweiser':
– Then change all Sells tuples with beer = 'Bud' so that
beer = 'Budweiser'.
Set NULL
• Delete the Bud tuple from Beers:
– Change all tuples of Sells that have beer = 'Bud' to have
beer = NULL.
• Update the Bud tuple by changing 'Bud' to 'Budweiser':
– Same change.
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.
CREATE TABLE Sells (
bar
CHAR(20),
beer CHAR(20),
price
REAL,
FOREIGN KEY(beer)
REFERENCES Beers(name)
ON DELETE SET NULL
ON UPDATE CASCADE
);
Chicken and egg
• Suppose we want to say:
CREATE TABLE chicken (
cID INT PRIMARY KEY,
eID INT REFERENCES egg(eID)
);
CREATE TABLE egg(
eID INT PRIMARY KEY,
cID INT REFERENCES chicken(cID)
);
• But, if we simply type the above statements, we'll get an error.
– The reason is that the CREATE TABLE statement for chicken refers to table egg,
which hasn't been created yet!
– Creating egg won't help either, because egg refers to chicken.
Deferring Constraint Checking
• To work around this problem, we need SQL schema modification
commands.
• First, create chicken and egg without foreign key declarations:
CREATE
cID
eID
);
CREATE
eID
cID
);
TABLE chicken(
INT PRIMARY KEY,
INT
TABLE egg(
INT PRIMARY KEY,
INT
• Then, we add foreign key constraints:
ALTER TABLE chicken ADD CONSTRAINT chickenREFegg
FOREIGN KEY (eID) REFERENCES egg(eID)
INITIALLY DEFERRED DEFERRABLE;
ALTER TABLE egg ADD CONSTRAINT eggREFchicken
FOREIGN KEY (cID) REFERENCES chicken(cID)
INITIALLY DEFERRED DEFERRABLE;
Chicken and egg (Cont’d)
• The DEFERRABLE tells Oracle to do deferred constraint checking.
– For example, to insert (1, 2) into chicken and (2, 1) into egg, we use:
INSERT INTO chicken VALUES(1, 2);
INSERT INTO egg VALUES(2, 1);
COMMIT;
• Because we've declared the foreign key constraints as "deferred", they are
only checked at the commit point.
– Without deferred constraint checking, we cannot insert anything into
chicken and egg, because the first INSERT would always be a constraint
violation.
• Finally, to get rid of the tables, we have to drop the constraints first, because
Oracle won't allow us to drop a table that's referenced by another table.
ALTER TABLE egg DROP CONSTRAINT eggREFchicken;
ALTER TABLE chicken DROP CONSTRAINT chickenREFegg;
DROP TABLE egg;
DROP TABLE chicken;
Check Constraints
• Check constraints allow users to restrict possible attribute values
for columns to admissible ones.
– They can be specified as column constraints or table constraints.
• The syntax for a check constraint is
[CONSTRAINT <name>] CHECK(<condition>)
• If a check constraint is specified as a column constraint, the
condition can only refer that column.
Check Constraints (Examples)
• Example:
– The name of an employee must consist of upper case letters only;
– the minimum salary of an employee is 500;
– department numbers must range between 10 and 100:
CREATE TABLE Emp (
empno NUMBER,
ename VARCHAR2(30) CONSTRAINT check_name
CHECK( ename = UPPER(ename) ),
sal NUMBER CONSTRAINT check_sal CHECK( sal >= 500 ),
deptno NUMBER CONSTRAINT check_deptno
CHECK(deptno BETWEEN 10 AND 100)
);
Checking
• DBMS automatically checks the specified conditions each time
a database modification is performed on this relation.
– For example, the insertion
INSERT INTO emp VALUES(7999,'SCOTT',450,10);
causes a constraint violation ORA02290: check constraint
(SAL_CHECK) violated and the insertion is rejected.
Check Constraints (cont’d)
• If a check constraint is specified as a table constraint, the <condition> can
refer to all columns of the table.
• Example:
– At least two persons must participate in a project, and
– the project's start date must be before the project's end date
CREATE TABLE Project (
... ,
pstart DATE,
pend DATE,
persons NUMBER CONSTRAINT check_pers CHECK (persons>=2),
... ,
CONSTRAINT dates_ok CHECK (pend > pstart)
);
• In this table definition, check_pers is a column constraint and dates_ok is a
table constraint.
What’s allowed in check
• Note that only simple conditions are allowed. For example
– It is not allowed to refer to columns of other tables
– No queries as check conditions.
– The functions sysdate and user cannot be used in a condition.
• A check condition, however, can include a NOT NULL constraint:
sal NUMBER CONSTRAINT check_sal CHECK(sal IS NOT NULL AND sal>=500)
More about constraints
REM Adding a violating constraint
ALTER TABLE Emp DROP CONSTRAINT check_sal;
INSERT INTO Emp(empno, ename, sal, deptno)
VALUES(9, 'ALEX', 300, 20);
ALTER TABLE Emp ADD CONSTRAINT check_sal CHECK(sal >= 500)
EXCEPTIONS INTO Exceptions;
REM The constraint cannot be created at all, because there is
REM a violating tuple.
• In order to identify those tuples that violate a constraint whose activation
failed, one can use the clause EXCEPTIONS INTO Exceptions
with the alter table statement.
• Exceptions is a table that stores information about the violating
tuples.
More about constraints
• Each tuple in the EXCEPTIONS table is identified by the attribute
ROWID.
– Every tuple in a database has a (pseudo) column of type ROWID that is used
to identify tuples.
– Besides the row id, the name of the table, the table owner as well as the
name of the violated constraint are stored.
• First we have to create the Exceptions table:
CREATE TABLE Exceptions(
row_id ROWID,
owner VARCHAR2(30),
table_name VARCHAR2(30),
constraint VARCHAR2(30)
);
• Then, we can query it:
Also, recall that information about
integrity constraints, their status
(enabled, disabled) etc. is stored in
the data dictionary, more precisely,
in the table USER_CONSTRAINTS.
SELECT Emp.*, constraint
FROM Emp, Exceptions
WHERE Emp.rowid = Exceptions.row_id;