1 / 19

CSE 544 Constraints

This lecture focuses on database constraints, integrity, and key concepts essential for managing databases effectively. Led by Dan Suciu, the session covers types of constraints, including integrity constraints, foreign keys, and attribute-level checks. Key topics include how to enforce constraints in SQL, the role of triggers, and techniques for maintaining referential integrity. The lecture will also address complex constraints and provide practical SQL examples for creating and managing constraints. Students will gain insight into optimizing queries through semantic understanding of constraints.

elden
Télécharger la présentation

CSE 544 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. CSE 544Constraints Lecture #3 Friday, January 13, 2011 Dan Suciu -- 544, Winter 2011

  2. Announcements • Tuesday 1/18: • Guest Lecturer: Bill Howe • Wednesday 1/19: 9am-11:30am, Data Models • Room: CSE 403, • Two papers to reviews: What goes around comes around and Query answering using views (Sec. 1-3) • Thursday 1/19: Transactions • One paper to review (Sec. 1, 2.1, 2.2, 3.1, and 3.2) • Friday 1/21: 11:30-1pm • Sign up to meet with me to discuss your project Dan Suciu -- 544, Winter 2011

  3. Outline and Reading Material • Constraints: Book 3.2, 3.3, 5.8 Dan Suciu -- 544, Winter 2011

  4. Constraints • A constraint = a property that we’d like our database to hold • Enforce it by taking some actions: • Forbid an update • Or perform compensating updates • Two approaches: • Declarative integrity constraints • Triggers Dan Suciu -- 544, Winter 2011

  5. Integrity Constraints in SQL • Keys, foreign keys • Attribute-level constraints • Tuple-level constraints • Global constraints: assertions The more complex the constraint, the harder it is to check and to enforce simple complex Dan Suciu -- 544, Winter 2011

  6. Keys OR: CREATE TABLE Product ( name CHAR(30) PRIMARY KEY, price INT) Product(name, price) CREATE TABLE Product ( name CHAR(30), price INT, PRIMARY KEY (name)) Dan Suciu -- 544, Winter 2011

  7. CREATE TABLE Product ( name CHAR(30), category VARCHAR(20), price INT, PRIMARY KEY (name, category)) Keys with Multiple Attributes Product(name, category, price)

  8. Other Keys CREATE TABLE Product ( productID CHAR(10), name CHAR(30), category VARCHAR(20), price INT,PRIMARYKEY (productID), UNIQUE (name, category)) There is at most one PRIMARY KEY;there can be many UNIQUE Dan Suciu -- 544, Winter 2011

  9. Foreign Key Constraints CREATE TABLE Purchase ( buyer CHAR(30), seller CHAR(30), product CHAR(30) REFERENCES Product(name), store VARCHAR(30)) Foreign key Purchase(buyer, seller, product, store)Product(name, price) Dan Suciu -- 544, Winter 2011

  10. Product Purchase Dan Suciu -- 544, Winter 2011

  11. Foreign Key Constraints CREATE TABLE Purchase( buyer VARCHAR(50), seller VARCHAR(50), product CHAR(20), category VAVRCHAR(20), store VARCHAR(30),FOREIGN KEY (product, category)REFERENCESProduct(name, category) ); Purchase(buyer, seller, product, category, store)Product(name, category, price) Dan Suciu -- 544, Winter 2011

  12. What happens during updates ? Types of updates: • In Purchase: insert/update • In Product: delete/update Product Purchase

  13. What happens during updates ? • SQL has three policies for maintaining referential integrity: • Reject violating modifications (default) • Cascade: after a delete/update do a delete/update • Set-null set foreign-key field to NULL Dan Suciu -- 544, Winter 2011

  14. Constraints on Attributes and Tuples Attribute level constraints: CREATE TABLE Purchase ( . . . store VARCHAR(30) NOT NULL, . . . ) CREATE TABLE Product ( . . . price INT CHECK (price >0 and price < 999)) Tuple level constraints: . . . CHECK (price * quantity < 10000) . . . Dan Suciu -- 544, Winter 2011

  15. Whatis the difference fromForeign-Key ? CREATE TABLE Purchase ( prodName CHAR(30) CHECK (prodName INSELECTProduct.nameFROM Product), date DATETIME NOT NULL) Dan Suciu -- 544, Winter 2011

  16. General Assertions CREATE ASSERTIONmyAssertCHECKNOT EXISTS( SELECTProduct.nameFROM Product, PurchaseWHEREProduct.name = Purchase.prodNameGROUPBYProduct.nameHAVING count(*) > 200) Dan Suciu -- 544, Winter 2011

  17. Comments on Constraints • Can give them names, and alter later • We need to understand exactly when they are checked • We need to understand exactly what actions are taken if they fail Dan Suciu -- 544, Winter 2011

  18. Semantic Optimization using Constraints Purchase(buyer, seller, product, store)Product(name, price) SELECT Purchase.storeFROM Product, PurchaseWHEREProduct.name=Purchase.product When can we rewrite the query ? SELECT Purchase.storeFROM Purchase

  19. Semantic Optimization using Constraints Purchase(buyer, seller, product, store)Product(name, price) SELECT Purchase.storeFROM Product, PurchaseWHEREProduct.name=Purchase.product When can we rewrite the query ? SELECT Purchase.storeFROM Purchase Purchase.product isforeign key AND not null

More Related