Constraints Some basic ones Some basic

Constraints

Some basic ones • Some basic integrity constraints: primary keys, not null constraints, and unique constraints. Examples: CREATE TABLE Movies ( title CHAR(40), title CHAR(40) PRIMARY KEY, 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); 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.

• • • UNIQUE constraint doesn't allow duplicate values in a column. If it encompasses more columns, no two equal combinations are allowed. However, 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 (2, 7); • The last statement issues a ORA 00001: unique constraint (THOMO. SYS_C 006985) violated

Constraint names • Every constraint, has a name. In this case, the name is: THOMO. SYS_C 006985. • We can explicitly name the constraint for easier handling. It can’t have the same name as another CREATE TABLE ABC ( constraint even if it is in another table. A number, B number, CONSTRAINT my_unique_constraint 2 UNIQUE (A, B) ); • To find the constraint names and corresponding tables we can execute: SELECT constraint_name, table_name FROM user_constraints;

Dropping/Adding Examples ALTER TABLE AB DROP CONSTRAINT SYS_C 006985; ALTER TABLE AB ADD CONSTRAINT my_unique_constraint UNIQUE (A);

Foreign key constraints • Specify a column or a list of columns as a foreign key referencing a table. • Referencing table is called the child table and the referenced table is , called the parent table. • Example: Each employee in table Emp must work in a department that is contained in table Dept. CREATE TABLE Emp ( empno INT PRIMARY KEY, . . . , deptno INT REFERENCES Dept(deptno) ); Dept table has to exist first!

Longer syntax foreign keys Remark. If you don’t specify primary keys or unique constraints in the parent tables, you can’t specify foreign keys in the child tables. CREATE TABLE Movie. Stars( name VARCHAR 2(20) PRIMARY KEY, address VARCHAR 2(30), gender VARCHAR 2(1), birthdate VARCHAR 2(20) ); CREATE TABLE Movies ( title VARCHAR 2(40), year INT, length INT, type VARCHAR 2(2), PRIMARY KEY (title, year) ); CREATE TABLE Stars. In ( title VARCHAR 2(40), year INT, star. Name VARCHAR 2(20), CONSTRAINT fk_movies FOREIGN KEY(title, year) REFERENCES Movies(title, year), CONSTRAINT fk_moviestars FOREIGN KEY(star. Name) REFERENCES Movie. Stars(name) );

Satisfying a Foreign key constraint Each row in the child table has to satisfy one of the following two conditions: • Foreign key value must either – appear as a primary key value in the parent table, or – be null • in case of a composite foreign key, at least one attribute value of the foreign key is null • So, for table Emp, an employee must not necessarily work in a department, i. e. , for the attribute deptno, NULL is admissible. • • If we don't want 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 INT PRIMARY KEY, pmno INT NOT NULL REFERENCES Emp, . . . ); When only the name of the parent table is given, the primary key of that table 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 INT PRIMARY KEY, . . . mgrno INT NOT NULL REFERENCES Emp, . . . );

Enforcing Foreign Key Constraints • If there is a foreign key constraint from table R to 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 • • 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 tuples referencing that beer. – Updated beer: change values 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 Follow the foreign key declaration by: ON [UPDATE, DELETE] [SET NULL, CASCADE] 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 doesn't allow ON UPDATE options

Chicken and egg • Suppose we want to say: CREATE TABLE chicken ( c. ID INT PRIMARY KEY, e. ID INT REFERENCES egg(e. ID) ); CREATE TABLE egg( e. ID INT PRIMARY KEY, c. ID INT REFERENCES chicken(c. ID) ); • 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.

Some first attempt • To work around this problem, first, create chicken and egg without foreign key declarations. CREATE TABLE chicken( c. ID INT PRIMARY KEY, e. ID INT ); CREATE TABLE egg( e. ID INT PRIMARY KEY, c. ID INT ); • Then, add foreign key constraints. ALTER TABLE chicken ADD CONSTRAINT chicken. REFegg FOREIGN KEY (e. ID) REFERENCES egg(e. ID); ALTER TABLE egg ADD CONSTRAINT egg. REFchicken FOREIGN KEY (c. ID) REFERENCES chicken(c. ID);

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

Deferrable Constraints • Solving the problem: In ORACLE SQLPlus, 1. We need the ability to group several SQL when we login, a transaction is statements into one unit called transaction. automatically created. 2. Then, we need a way to tell the SQL system not We can (positively) to check the constraints until the transaction is finish this transaction by calling “commit” committed. and a new transaction is created. • 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.

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 My. Constraint DEFERRED). Example Here we declare the constraints DEFERRABLE and INITIALLY DEFERRED. ALTER TABLE chicken ADD CONSTRAINT chicken. REFegg FOREIGN KEY (e. ID) REFERENCES egg(e. ID) DEFERRABLE INITIALLY DEFERRED; ALTER TABLE egg ADD CONSTRAINT egg. REFchicken FOREIGN KEY (c. ID) REFERENCES chicken(c. ID) DEFERRABLE INITIALLY DEFERRED;

Successful Insertions Now we can finally insert: INSERT INTO chicken VALUES(1, 2); INSERT INTO egg VALUES(2, 1); COMMIT;

Dropping • Finally, to get rid of the tables, we have to drop the constraints first, because we can’t to drop a table that's referenced by another table. ALTER TABLE egg DROP CONSTRAINT egg. REFchicken; ALTER TABLE chicken DROP CONSTRAINT chicken. REFegg; DROP TABLE chicken;

Another Example of Deferring • Studio and President

Check Constraints [CONSTRAINT <name>] CHECK(<condition>) allows users to restrict possible attribute values for columns to admissible ones 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: These three are column constraints, and can only refer to the corresponding column. CREATE TABLE Emp ( empno NUMBER, ename VARCHAR 2(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. – E. g. , the insertion INSERT INTO emp VALUES(7999, 'SCOTT', 450, 10); causes a constraint violation ORA 02290: check constraint (SAL_CHECK) violated and it is rejected.

Check Constraints (cont’d) • A check constraint can be specified as a table constraint, and the <condition> 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 (. . . , Column constraint pstart DATE, pend DATE, persons NUMBER CONSTRAINT check_pers CHECK (persons>=2), . . . , CONSTRAINT dates_ok CHECK (pend > pstart) ); Table constraint

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 <= 5. 00 ) );

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 <= 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)) isn’t checked if a beer is deleted from Beers (unlike foreign keys).

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 >= 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 should create and stores information about the violating tuples.

Violating Tuples (cont. ) • First we have to create the Exceptions table: CREATE TABLE Exceptions( row_id ROWID, owner VARCHAR 2(30), table_name VARCHAR 2(30), constraint VARCHAR 2(30) ); • Then, we can query it: 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. SELECT Emp. *, constraint FROM Emp, Exceptions WHERE Emp. rowid = Exceptions. row_id;

Writing Constraints Correctly • Create the table Movie. Star. If the star gender is 'M', then his name must not begin with 'Ms. '. CREATE TABLE Movie. Star ( name CHAR(20) PRIMARY KEY, address VARCHAR(255), gender CHAR(1), CHECK (gender='F' OR name NOT LIKE 'Ms. %') ); We can’t use an “implication. ” We should formulate it in terms of OR. p >q is the same as (not p) OR q.

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 >= 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 );

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. a) The speed of a laptop must be at least 800. b) The only types of printers are laser, ink jet, and bubble. c) A model of a product must also be the model of a PC, a laptop, or a printer.

(c) • Note that in most DBMS’es (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 CREATE VIEW Product. Safe(maker, model, type) AS SELECT maker, model, type FROM Product WHERE model IN ( (SELECT model FROM PC) UNION (SELECT model FROM Laptop) UNION (SELECT model FROM Printer) ) WITH CHECK OPTION; Then, we insert into this view as opposed to directly into Product. Also, make the FOREIGN KEY constraints in PC, Laptop, and Printer deferrable initially deferred.

Another 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 > arrival_date) ); We want to add the constraint that reservations do not overlap.

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 > arrival_date) ); We want to add the constraint that reservations do not overlap. CREATE VIEW Hotel. Stays AS SELECT room_nbr, arrival_date, departure_date, guest_name FROM Hotel H 1 WHERE NOT EXISTS ( SELECT * FROM Hotel H 2 WHERE H 1. room_nbr = H 2. room_nbr AND (H 2. arrival_date < H 1. arrival_date AND H 1. arrival_date < H 2. departure_date) ) WITH CHECK OPTION;

Exercise – Hotel Stays – Inserting INSERT INTO Hotel. Stays (room_nbr, arrival_date, departure_date, guest_name) VALUES(1, '01 Jan 2009', '03 Jan 2009', 'Alex'); This goes Ok. INSERT INTO Hotel. Stays (room_nbr, arrival_date, departure_date, guest_name) VALUES(1, '02 Jan 2009', '05 Jan 2009', 'Ben'); * ERROR at line 1: ORA 01402: view WITH CHECK OPTION where clause violation

Assertions • These are database schema elements, like relations or views. • Defined by: CREATE ASSERTION <name> CHECK (<condition>); • Condition may refer to any relation or attribute in the database schema. • Remark. NOT implemented in most DBMS’es!

Example: Assertion • In Sells(bar, beer, price), no bar may charge an average of more than $5. CREATE ASSERTION No. Ripoff. Bars CHECK ( NOT EXISTS ( Bars with an average price SELECT bar FROM Sells above $5 GROUP BY bar HAVING 5. 00 < AVG(price) ));

Example: Assertion • In Drinkers(name, addr, phone) and Bars(name, addr, license), there cannot be more bars than drinkers. CREATE ASSERTION Few. Bar CHECK ( (SELECT COUNT(*) FROM Bars) <= (SELECT COUNT(*) FROM Drinkers) );

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 Few. Bar. • Neither can an insertion to Drinkers.