1 / 8

Database Check Constraints: Usage and Best Practices

Learn how to specify check constraints on columns, domains, and complex scenarios for effective database design. Understand the importance of proper constraint implementation. Explore altering constraints within existing tables.

mrusso
Download Presentation

Database Check Constraints: Usage and Best Practices

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. Check Constraints cs3431

  2. Column Check constraints • Constraints specified on a column • We can specify CHECK constraints. e.g.: gender char (1) CHECK (gender IN (‘F’, ‘M’)) cs3431

  3. Another Check Constraint salary int CONSTRAINT minSalary CHECK (salary>=60000) cs3431

  4. Yet Another Check Constraint • Can use query expression to express constraint. CREATE TABLE Sailors (sid INTEGER, sname CHAR(10), rating INTEGER, PRIMARY KEY (sid), CHECK ( rating >= 1 AND rating <= 10 )) cs3431

  5. Explicit Domain Constraints CREATE DOMAIN values-of-ratings INTEGER DEFAULT 1 CHECK ( VALUE >= 1 AND VALUE <= 10); CREATE TABLE Sailors (sid INTEGER, sname CHAR(10), ratingvalues-of-ratings, age REAL, PRIMARY KEY (sid)); cs3431

  6. Complex Check Constraints • Constraint that Interlake boats cannot be reserved: CREATE TABLE Reserves ( sname CHAR(10), bid INTEGER, day DATE, PRIMARY KEY (bid,day), CONSTRAINT noInterlakeRes CHECK (`Interlake’ <> ( SELECT B.bname FROM Boats B WHERE B.bid= bid))) • Important : Oracle DBMS does NOT support complex check constraints with nested sub-queries or involving other tables cs3431

  7. On Column Check Constraints • Constraint on relation R only checked when tuple inserted into R or updated in R. Changes on other tables besides R do not lead to a checking of R’s constraints. • Only needs to hold TRUE when table is non-empty • Good Design : Check constraint should only involve attributes of the tuple being “checked” and have no sub-queries to assure the constraint always holds. cs3431

  8. Altering Constraints • Constraints can be added to an existing table. ALTER TABLE ADD CONSTRAINT [<cName>] <cBody> • Any constraint that has a name can be dropped ALTER TABLE DROP CONSTRAINT <cName> cs3431

More Related