80 likes | 144 Views
Check Constraints. Column Check constraints. Constraints specified on a column We can specify CHECK constraints. e.g.: gender char (1) CHECK (gender IN (‘F’, ‘M’)). Another Check Constraint. salary int CONSTRAINT minSalary CHECK (salary>=60000). Yet Another Check Constraint.
E N D
Check Constraints cs3431
Column Check constraints • Constraints specified on a column • We can specify CHECK constraints. e.g.: gender char (1) CHECK (gender IN (‘F’, ‘M’)) cs3431
Another Check Constraint salary int CONSTRAINT minSalary CHECK (salary>=60000) cs3431
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
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
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
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
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