Database Principles

1. Database Principles College of Computer Science and Technology Chongqing University of Posts & Telecom.

2. Chapter 6Constraints Foreign Keys Constraints

3. Constraints • A constraint is a relationship among data elements that the DBMS is required to enforce. • Example: key constraints.

4. Kinds of Constraints • Keys. • Foreign-key, or referential-integrity. • Value-based constraints. • Constrain values of a particular attribute. • Tuple-based constraints. • Relationship among components. • Assertions: any SQL boolean expression.

5. 6.1 Keys in SQL • Two ways to declare a primary key in CREATE TABLE statement: 1)When an attribute is listed in the relation schema. 2)Add an additional declaration that says a particular attribute or set of attributes forms the primary key. Note: PRIMARY KEY and UNIQUE are two similar ways to declare keys. However, a table may have only one primary key but any number of ‘unique’ declarations.

6. CREATE TABLE MovieStar( name CHAR(30) PRIMARY KEY, address VARCHAR(255), gender CHAR(1), birthdate DATE ); // for 1) ---------------------------------------------------------------- CREATECREATE TABLE MovieStar( name CHAR(30), address VARCHAR(255), gender CHAR(1), birthdate DATE PRIMARY KEY(name) ); // for 2)

7. 6.1 Enforcing Key Constraints • Usually, we want to build indexes on attributes declared to be PRIMARY KEY or UNIQUE. • Only an insertion or update can causes a violation(constraints checking)

8. 6.2 Foreign Keys • Consider Relation Studio(name,address,presC#) • We might expect that attribute presC# has a value c --- something appearing in MovieExec.cert# . • A constraint that requires a cert# in MovieExec to be the value ‘c ’ in Studio is called a foreign -key constraint.

9. 6.2.1Expressing Foreign Keys • Use the keyword REFERENCES, either: • Within the declaration of an attribute, when only one attribute is involved. • As an element of the schema, as: FOREIGN KEY ( <list of attributes> ) REFERENCES <relation> ( <attributes> ) Note:Referenced attributes must be declared PRIMARY KEY or UNIQUE.

10. Example CREATE TABLE Studio ( name CHAR(20) PRIMARY KEY, address VARCHAR(255) presC# INT REFERENCES MovieExec(cert#) ); CREATE TABLE Studio ( name CHAR(20) PRIMARY KEY, address VARCHAR(255) presC# INT FOREIGN KEY pressC# REFERENCES MovieExec(cert#) );

11. 6.2.2 Enforcing Foreign-Key Constraints • If there is a foreign-key constraint from attributes of relation R to the primary 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.”

12. Actions Taken • Suppose R = Studio, S = MovieExec. • An insert or update to Studio that introduces a nonexistent pres#C must be rejected. • A deletion or update to MovieExec that removes a cert# value found in some tuples of Studio can be handled in three ways.

13. Three policies • Default : Reject the modification. • Cascade : Make the same changes in Studio. • Deleted a cert# value inMovieExec : deletethe correspondingtuple in studio. • Updated cert# : change value in Studio. • Set NULL : Change the presC# to NULL.

14. Example: Cascade • Suppose we delete the c1 tuple from MovieExec. • Then delete all tuples from Studio that have presC# = ’c1’. • Suppose we update the c1 tuple by changing ’c1’ to ’c2’. • Then change all Studio tuples with presC# = ’c1’ so that presC# = ’c2’.

15. Example: Set NULL • Suppose we delete the c1 tuple from MovieExec. • Change all tuples of Studio that have presC# = ’c1’ to have presC# = NULL. • Suppose we update the c1 tuple by changing ’c1’ to ’c2’. • Same change.

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

17. Example CREATE TABLE Studio ( name CHAR(30)PRIMARY KEY, address VARCHAR(255), presC# INT REFERENCES MovieExec(cert#), ON DELETE SET NULL ON UPDATE CASCADE );

18. 6.3 Constraints on the Values of Attributes • Not-Null Constraints presC# INT REFERENCES MovieExec(cert#)NOT NULL • Attribute-Based CHECK Constraints. • Domain Constraints

19. Attribute-Based Checks • Put a constraint on the value of a particular attribute. • CHECK( <condition> ) must be added to the declaration for the attribute. • The condition may use the name of the attribute, but any other relation or attribute name must be in a subquery.

20. Example CREATE TABLE Stdio ( name CHAR(30)PRIMARY KEY, address VARCHAR(255), presC# INT REFERENCES MovieExec(cert#) CHECK(presC# >= 100000), ON DELETE SET NULL ON UPDATE CASCADE );

21. Domain Constraints • Use the special keyword VALUE to refer to a value in the domain e.g. CREATE DOMAIN GenderDomain CHAR(1) CHECK (VALUE IN (‘F’,’M’));

22. 6.4 Global Constraints • Tuple-based CHECK constraints • Restrict any aspect of the tuples of a single relation. • Assertions • Constraint that may involve entire relations or several tuple-variables ranging over the same relation.

23. 6.4.1 Tuple-Based Checks • CHECK ( <condition> ) may be added as another element of a schema definition. • The condition may refer to any attribute of the relation, but any other attributes or relations require a subquery. • Checked on insert or update only.

24. Example: Tuple-Based Check • Only the star’s gender is male ,then his name must not begin with ‘Ms.’.: CREATE TABLE MovieStar ( name CHAR(30) UNIQUE, address VARCHAR(255), gender CHAR(1), CHECK (gender = ‘F’ OR name NOT LIKE ‘Ms.%’) );

25. 6.4.2 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.

26. Example: Assertion • No one can become the president of a studio unless their net worth is at least $10,000,000. CREATE ASSERTION RichPres CHECK ( NOT EXISTS ( SELECT * FROM Studio, MovieExec WHERE presC# = cert# AND netWorth < 10000000) );

27. Example: Assertion CREATE TABLE Stdio ( name CHAR(30)PRIMARY KEY, address VARCHAR(255), presC# INT REFERENCES MovieExec(cert#) CHECK(presC# NOT IN (SELECT cert# FROM MovieExec WHERE netWorth < 10000000) ) );

28. About 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.

29. 6.5 Modification of Constraints • Giving Names to Constraints name CHAR(30) CONSTRAINT NameIsKey PRIMARY KEY; • Altering Constraints on Tables ALTER TABLE MovieStar DROP CONSTRAINT NameIsKey; ALTER TABLE MovieStar ADD CONSTRAINT NameIsKey PRIMARY KEY(name);

30. Modification of Constraints • Altering Domain Constraints ALTER DOMAIN CertDomain DROP CONSTRAINT SixDigits; ALTER DOMAIN CertDomain ADD CONSTRAINT SixDigits CHECK (VALUE >= 100000) ; • Altering Assertions DROP ASSERTION RichPres;

31. Summary • Key Constraints: declare a key with a UNIQUE or PRIMARY KEY • Referential Integrity Constraints:declare with a REFERENCES or FOREIGN KEY. • Value-Based Check Constraints:adding the keyword CHECK and the condition to be checked.

32. Exercises • Requiredreading: 6.1 ~ 6.4, Summary • Recommend reading: 6.5