Constraints

1
Constraints
2
Some basic ones

• Some basic integrity constraints primary keys,
  not null constraints, and unique constraints.
  Examples

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

• 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.
• However, if a column is not explicitly defined as
  NOT NULL, nulls can be inserted multiple times.
• CREATE TABLE AB (
• A NUMBER UNIQUE,
• B NUMBER
• )
• 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)
• insert into AB values (2,7)
• The last statement issues a
• ORA-00001 unique constraint (THOMO.SYS_C006985)
  violated

4
Constraint names

• Every constraint, has a name. In this case, the
  name is THOMO.SYS_C006985.
• We can explicitly name the constraint for easier
  handling.
• CREATE TABLE ABC (
• A number,
• B number,
• C number,
• CONSTRAINT my_unique_constraint2 UNIQUE (A,B)
• )
• To find the constraint names and the tables on
  which the constraints are set do
• select CONSTRAINT_NAME, TABLE_NAME from
  user_constraints

It cant have the same name as another constraint
even if it is in another table.
5
Dropping/Adding

• Examples
• ALTER TABLE AB DROP CONSTRAINT SYS_C006985
• ALTER TABLE AB add
• CONSTRAINT my_unique_constraint UNIQUE (A)

6
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.
• Example Each employee in the table EMP must work
  in a department that is contained in the table
  DEPT.
• CREATE TABLE Emp (
• empno INT CONSTRAINT pk_emp PRIMARY KEY,
• ... ,
• deptno INT CONSTRAINT fk_deptno REFERENCES
  Dept(deptno)
• )

Dept table has to exist first!
7
Longer syntax for foreign keys
Remark. If you dont specify primary keys or
unique constraints in the parent tables, you
cant 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) )
8
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,
• . . .
• )

When only the name of the parenttable is given,
the primary key of that table is assumed.
9
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 INT CONSTRAINT emp_pk PRIMARY KEY,
• . . .
• mgr INT NOT NULL CONSTRAINT fk_mgr REFERENCES
  EMP,
• . . .
• )

10
Enforcing Foreign-Key Constraints

• If there is a foreign-key constraint from table R
  to 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
11
Action taken

• 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 tuples referencing
  that beer.
• Updated beer change values in Sells.
• Set NULL Change the beer to NULL.

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

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

Oracle does not allow ON UPDATE options
14
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.

15
Some first attempt

• To work around this problem, 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 could add foreign key constraints.
• ALTER TABLE chicken ADD CONSTRAINT chickenREFegg
• FOREIGN KEY (eID) REFERENCES egg(eID)
• ALTER TABLE egg ADD CONSTRAINT eggREFchicken
• FOREIGN KEY (cID) REFERENCES chicken(cID)

16
Inserting

• INSERT INTO chicken VALUES(1, 2)
• ERROR at line 1
• ORA-02291 integrity constraint
  (THOMO.CHICKENREFEGG) violated - parent key not
  found
• INSERT INTO egg VALUES(2, 1)
• ERROR at line 1
• ORA-02291 integrity constraint
  (THOMO.EGGREFCHICKEN) violated - parent key not
  found

17
Deferrable Constraints

• Solving the problem
• We need the ability to group several SQL
  statements into one unit called transaction.
• Then, we need a way to tell the SQL system not to
  check the constraints until the transaction is
  committed.
• Any constraint may be declared DEFERRABLE or
  NOT DEFERRABLE.
• NOT DEFERRABLE is the default, and means that
  every time a database modification occurs, the
  constraint is immediately checked.
• DEFERRABLE means that we have the option of
  telling the system to wait until a transaction is
  complete before checking the constraint.

In ORACLE SQLPlus, when we login, a transaction
is automatically created. We can (positively)
finish this transaction by calling commit and a
new transaction is created.
18
Initially Deferred / Initially Immediate

• If a constraint is deferrable, then we may also
  declare it
• INITIALLY DEFERRED, and the check will be
  deferred to the end of the current transaction.
• INITIALLY IMMEDIATE, (default) and the check will
  be made before any modification.
• But, because the constraint is deferrable, we
  have the option of later deciding to defer
  checking (SET CONSTRAINT MyConstraint DEFERRED).
• Example
• Here we declare the constraints DEFERRABLE and
  INITIALLY DEFERRED. First drop the current
  constraints and then do
• ALTER TABLE chicken ADD CONSTRAINT chickenREFegg
• FOREIGN KEY (eID) REFERENCES egg(eID)
• DEFERRABLE INITIALLY DEFERRED
• ALTER TABLE egg ADD CONSTRAINT eggREFchicken
• FOREIGN KEY (cID) REFERENCES chicken(cID)
• DEFERRABLE INITIALLY DEFERRED

19
Successful Insertions

• Now we can finally insert
• INSERT INTO chicken VALUES(1, 2)
• INSERT INTO egg VALUES(2, 1)
• COMMIT

20
Dropping

• Finally, to get rid of the tables, we have to
  drop the constraints first, because we cant 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

21
Another Example of Deferring

• Studio and President

22
Check Constraints

• Check constraints allow users to restrict
  possible attribute values for columns to
  admissible ones. Syntax is
• CONSTRAINT ltnamegt CHECK(ltconditiongt)
• 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)
• )

These three are column constraints, and can only
refer the corresponding column.
23
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.

24
Check Constraints (contd)

• A check constraint can be specified as a table
  constraint, and the ltconditiongt can refer to any
  column of the table.
• Example
• At least two persons must participate in a
  project, and
• project's start date must be before 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)
• )

Column constraint
Table constraint
25
Checks with subqueries

• Example
• CREATE TABLE Sells (
• bar CHAR(20),
• beer CHAR(20) CONSTRAINT beer_check
• CHECK ( beer IN (SELECT name FROM Beers)),
• price REAL CHECK ( price lt 5.00 )
• )

This is a column constraint. The condition may
use the name of the attribute (beer), but any
other relation or attribute name must be in a
subquery.
26
Timing of Checks

• Column checks are performed only when a value for
  that attribute is inserted or updated.
• Table checks are performed only when values for
  the involved attributes (not those in
  subqueries) are inserted or updated.
• Example.
• CHECK (price lt 5.00)
• checks every new price and rejects the
  modification (for that tuple) if the price is
  more than 5.
• Example.
• CHECK (beer IN (SELECT name FROM Beers))
• isnt checked if a beer is deleted from Beers
  (unlike foreign-keys).

27
Violating Tuples

• 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.
• Exceptions is a table that we create and stores
  information about the violating tuples.

28
Violating Tuples (cont.)

• 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

Every tuple has a (pseudo) column of type rowid
that is used to identify tuples. row_id here
will reference to rowid in the Emp table.
Besides the row_id, the name of the table, the
table owner, as well as the name of the violated
constraint are stored.
29
Writing Constraints Correctly

• Create the table MovieStar. If the star gender is
  'M', then his name must not begin with 'Ms.'.
• CREATE TABLE MovieStar (
• name CHAR(20) PRIMARY KEY,
• address VARCHAR(255),
• gender CHAR(1),
• CHECK (gender'F' OR name NOT LIKE 'Ms.')
• )

We cant use an implication. We should
formulate it in terms of OR. p-gtq is the same as
(not p) OR q.
30
Exercise mutually exclusive subclasses

• CREATE TABLE Vehicles (
• vin CHAR(17) PRIMARY KEY,
• vehicle_type CHAR(3) CHECK(vehicle_type IN
  ('SUV', 'ATV')),
• fuel_type CHAR(4),
• door_count INT CHECK(door_count gt 0),
• UNIQUE(vin, vehicle_type)
• )
• CREATE TABLE SUVs (
• vin CHAR(17) PRIMARY KEY,
• vehicle_type CHAR(3) CHECK(vehicle_type
  'SUV'),
• FOREIGN KEY (vin, vehicle_type) REFERENCES
  Vehicles (vin, vehicle_type)
• ON DELETE CASCADE
• )
• CREATE TABLE ATVs (
• vin CHAR(17) PRIMARY KEY,
• vehicle_type CHAR(3) CHECK(vehicle_type
  ATV'),
• FOREIGN KEY (vin, vehicle_type) REFERENCES
  Vehicles (vin, vehicle_type)
• ON DELETE CASCADE
• )

31
Exercise PCs, Laptops, Printers

• Product(maker, model, type)
• PC(model, speed, ram, hd, rd, price)
• Laptop(model, speed, ram, hd, screen, price)
• Printer(model, color, type, price)
• First create keys and foreign key references.
• Then create the following constraints.
• The speed of a laptop must be at least 800.
• The only types of printers are laser, ink-jet,
  and bubble.
• A model of a product must also be the model of a
  PC, a laptop, or a printer.

32
Whats usually allowed in check

• Note that in most DBMSes (including ORACLE) only
  simple conditions are allowed. For example
• It is not allowed to refer to columns of other
  tables
• No queries as check conditions.
• Solution Use views WITH CHECK OPTION
• Example
• CREATE TABLE Hotel (
• room_nbr INT NOT NULL,
• arrival_date DATE NOT NULL,
• departure_date DATE NOT NULL,
• guest_name CHAR(15) NOT NULL,
• PRIMARY KEY (room_nbr, arrival_date),
• CHECK (departure_date gt arrival_date)
• )
• We want to add the constraint that reservations
  do not overlap.

33
Exercise Hotel Stays

• CREATE TABLE Hotel (
• room_nbr INT NOT NULL,
• arrival_date DATE NOT NULL,
• departure_date DATE NOT NULL,
• guest_name CHAR(15) NOT NULL,
• PRIMARY KEY (room_nbr, arrival_date),
• CHECK (departure_date gt arrival_date)
• )
• CREATE VIEW HotelStays AS
• SELECT room_nbr, arrival_date, departure_date,
  guest_name
• FROM Hotel H1
• WHERE NOT EXISTS (
• SELECT
• FROM Hotel H2
• WHERE H1.room_nbr H2.room_nbr AND
• (H2.arrival_date lt H1.arrival_date AND
  H1.arrival_date lt H2.departure_date)
• )
• WITH CHECK OPTION

We want to add the constraint that reservations
do not overlap.
34
Exercise Hotel Stays Inserting

• INSERT INTO HotelStays (room_nbr, arrival_date,
  departure_date, guest_name)
• VALUES(1, '01-Jan-2008', '03-Jan-2008', 'Alex')
• This goes Ok.
• INSERT INTO HotelStays (room_nbr, arrival_date,
  departure_date, guest_name)
• VALUES(1, '02-Jan-2008', '05-Jan-2008', 'Ben')
• ERROR at line 1
• ORA-01402 view WITH CHECK OPTION where-clause
  violation

35
Assertions

• These are database-schema elements, like
  relations or views.
• Defined by
• CREATE ASSERTION ltnamegt
• CHECK (ltconditiongt)
• Condition may refer to any relation or attribute
  in the database schema.
• Remark. NOT implemented in most DBMSes!

36
Example Assertion

• In Sells(bar, beer, price), no bar may charge an
  average of more than 5.
• CREATE ASSERTION NoRipoffBars CHECK (
• NOT EXISTS (
• SELECT bar FROM Sells
• GROUP BY bar
• HAVING 5.00 lt AVG(price)
• ))

37
Example Assertion

• In Drinkers(name, addr, phone) and
• Bars(name, addr, license), there cannot be more
  bars than drinkers.
• CREATE ASSERTION FewBar CHECK (
• (SELECT COUNT() FROM Bars) lt
• (SELECT COUNT() FROM Drinkers)
• )

38
Timing of Assertion Checks

• In principle, we must check every assertion after
  every modification to any relation of the
  database.
• A clever system can observe that only certain
  changes could cause a given assertion to be
  violated.
• Example
• No change to Beers can affect FewBar.
• Neither can an insertion to Drinkers.