1 / 32

Database Principles

Database Principles. College of Computer Science and Technology Chongqing University of Posts & Telecom. Chapter 6 Constraints. Foreign Keys Constraints. Constraints. A constraint is a relationship among data elements that the DBMS is required to enforce. Example: key constraints.

garth
Download Presentation

Database Principles

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

More Related