constraints we have discussed three types of integrity constraints: primary keys, not null...
Post on 19-Dec-2015
220 views
TRANSCRIPT
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 into AB values (4, 5);insert into AB values (2, 1);insert into AB values (9, 8);insert into AB values (6, 9);insert into AB values (null,9);insert into AB values (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 ( A number, B number, C number, CONSTRAINT my_unique_constraint2 UNIQUE (A,B));
It can’t have the same name as another constraint even if it is in another table.
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 child table, and the referenced table is called the parent table.
• 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
CREATE TABLE Movies (
title VARCHAR2(40),
year INT,
length INT,
type VARCHAR2(2),
PRIMARY KEY (title, year)
);
CREATE TABLE MovieStars(
name VARCHAR2(20) PRIMARY KEY,
address VARCHAR2(30),
gender VARCHAR2(1),
birthdate VARCHAR2(20)
);
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)
);
If you don’t specify primary keys or unique constraints in the parent tables, you cannot specify foreign keys in the child tables.
Foreign key constraints (cont.)• In order to satisfy a foreign key constraint, each row in the child table 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 parent table, 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 parent table 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.,
parent table and child table 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)
);
CREATE TABLE Sells (bar CHAR(20),beer CHAR(20),price REAL,FOREIGN KEY(beer) REFERENCES Beers(name)
);
Relation S
Relation R
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.
ExampleCascade• 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 NULLON 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 TABLE chicken(cID INT PRIMARY KEY,eID INT
);CREATE TABLE egg(
eID INT PRIMARY KEY,cID 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 ORA 02290: 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 constraintsREM 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:SELECT Emp.*, constraint
FROM Emp, Exceptions
WHERE Emp.rowid = Exceptions.row_id;
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.