1 / 39

Constraints

Constraints. 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 (

hakan
Download Presentation

Constraints

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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 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 (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 corresponding tables we can execute: SELECT constraint_name, table_name FROM user_constraints; It can’t 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 • 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!

  7. Longer syntax for 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 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) );

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

  9. 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, . . . );

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

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

  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. 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 can’t 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 [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: CREATE TABLE Emp ( empno NUMBER, ename VARCHAR2(30) CONSTRAINT check_name CHECK( ename = UPPER(ename) ), sal NUMBER CONSTRAINT check_salCHECK( sal >= 500 ), deptno NUMBER CONSTRAINT check_deptno CHECK(deptno BETWEEN 10 AND 100) ); These three are column constraints, and can only refer to the corresponding column.

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

  24. 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 ( ... , pstart DATE, pend DATE, persons NUMBER CONSTRAINT check_persCHECK (persons>=2), ... , CONSTRAINT dates_okCHECK (pend > 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 <= 5.00 ) );

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

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

  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 can’t use an “implication.” We should formulate it in terms of OR. p->q 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 >= 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. (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 ProductSafe(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.

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

  34. Exercise – Hotel Stays We want to add the constraint that reservations do not overlap. 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) ); 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 < H1.arrival_date AND H1.arrival_date < H2.departure_date) ) WITH CHECK OPTION;

  35. Exercise – Hotel Stays – Inserting INSERT INTO HotelStays (room_nbr, arrival_date, departure_date, guest_name) VALUES(1, '01-Jan-2009', '03-Jan-2009', 'Alex'); This goes Ok. INSERT INTO HotelStays (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

  36. 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!

  37. Bars with an average price above $5 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 < AVG(price) ));

  38. 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) <= (SELECT COUNT(*) FROM Drinkers) );

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

More Related