Constraints

1
Constraints

• We have discussed three types of integrity
  constraints primary keys, not null constraints,
  and unique constraints.

CREATE TABLE Movies ( title CHAR(40), year
INT, length INT, type CHAR(2), PRIMARY KEY
(title, year) )
CREATE TABLE Movies ( title CHAR(40) PRIMARY
KEY, year INT, length INT, type CHAR(2) )
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.
2

• 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)

3
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_constrain
  t 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 cant have the same name as another constraint
even if it is in another table.
4
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)
• )

5
Longer syntax for foreign keys
If you dont specify primary keys or unique
constraints in the parent tables, you cannot
specify foreign keys in the child tables.
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) )
6
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 NULLs 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.

7
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,
• . . .
• )

8
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
• An insert or update to R introduces values not
  found in S.
• 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
9
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.
• Default Reject the modification.
• Cascade Make the same changes in Sells.
• Deleted beer delete Sells tuple.
• Updated beer change value in Sells.
• Set NULL Change the beer to NULL.

10
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.

11
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
• )

12
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.

13
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)

14
Chicken and egg (Contd)

• 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

15
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 ltnamegt CHECK(ltconditiongt)
• If a check constraint is specified as a column
  constraint, the condition can only refer that
  column.

16
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 gt
  500 ),
• deptno NUMBER CONSTRAINT check_deptno
• CHECK(deptno BETWEEN 10 AND 100)
• )

17
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.

18
Check Constraints (contd)

• If a check constraint is specified as a table
  constraint, the ltconditiongt 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
  (personsgt2),
• ... ,
• CONSTRAINT dates_ok CHECK (pend gt pstart)
• )
• In this table definition, check_pers is a column
  constraint and dates_ok is a table constraint.

19
Whats 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 salgt500)

20
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 gt 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.

21
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.