1 / 13

Constraints

Constraints. B term 2004: lecture 15. Keys: Primary keys and unique. CREATE TABLE Student ( sNumber int, sName varchar (20), dept char (2), CONSTRAINT key PRIMARY KEY (sNumber), CONSTRAINT uniqueName UNIQUE (sName));. Unique vs. primary keys.

aron
Download Presentation

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. Constraints B term 2004: lecture 15 Murali Mani

  2. Keys: Primary keys and unique CREATE TABLE Student ( sNumber int, sName varchar (20), dept char (2), CONSTRAINT key PRIMARY KEY (sNumber), CONSTRAINT uniqueName UNIQUE (sName)); Murali Mani

  3. Unique vs. primary keys • Attribute values may be null even if they are declared unique (primary key attributes should not be null). • We can have any number of unique constraints for a table (only one primary key constraint can be defined for a table). Murali Mani

  4. Foreign Keys: Referential Integrity Constraints • Specified for a table as CONSTRAINT [<fkName>] FOREIGN KEY (<a1List>) REFERENCES <tableName> (<a2List>) • eg: FOREIGN KEY R (a, b) REFERENCES S (a, b) • Requires (a, b) be unique or primary key of S. • Consider a row in R with values of a as a1, and b as b1 where a1, b1 are both non-null. There must be a row in S with values for (a, b) as (a1, b1). Murali Mani

  5. Maintaining referential integrity: Inserts/Deletes/Updates • Default: reject any modification that violates the constraints. • We can specify other policies for delete/update as set null/cascade. • Eg: FOREIGN KEY (name) references Student (sName) ON DELETE SET NULL ON UPDATE CASCADE • SET NULL: if the update violates foreign key constraint, the foreign key attributes are set to null. • CASCADE: if the update violates foreign key constraint, the foreign key attributes are modified. Murali Mani

  6. Constraints on Attributes • We can specify attributes as NULL or NOT NULL. Eg: sName varchar (20) NOT NULL • We can specify CHECK constraints. Eg: gender char (1) CHECK (gender IN (‘F’, ‘M’)) salary int CHECK (salary >= 60000) CONSTRAINT c1 check (salary >= 60000) professor varchar (30) CHECK (professor IN select pname from professor); • Constraints are checked only when inserting/updating the table. Murali Mani

  7. Constraints for a tuple CONSTRAINT <cName> CHECK (<condition>) Eg: CREATE TABLE Marks (midTerm int, final int, CHECK (midTerm + final >= 25)); Note: Constraints are checked only during insertion/update Murali Mani

  8. Altering Constraints ALTER TABLE ADD CONSTRAINT <cName> <cBody> ALTER TABLE DROP CONSTRAINT <cName> Murali Mani

  9. Constraints on the entire relational schema Assertions: CREATE ASSERTION <assertionName> CHECK (<condition>) Eg: CREATE ASSERTION CHECK ( NOT EXISTS (SELECT * FROM PROFESSOR WHERE salary < 60000)); Condition is any condition that can appear in WHERE clause For any database modification, the assertion must be true. Murali Mani

  10. Triggers (Event, Condition, Action rules) • We specify triggers as Event, Condition, Action rules. • When event occurs, and condition is satisfied, the action is performed. Murali Mani

  11. Triggers CREATE TRIGGER <triggerName> BEFORE | AFTER INSERT|DELETE|UPDATE [OF <columnList>] ON <tableName>|<viewName> [REFERENCING OLD AS <oldName>, NEW AS <newName>] FOR EACH ROW [WHEN (<condition>)] <PSM body>; Murali Mani

  12. Triggers: example CREATE TRIGGER t1 AFTER INSERT ON Student REFERENCING NEW as newTuple FOR EACH ROW WHEN (newTuple.professor IS NOT NULL) BEGIN INSERT INTO Professor (pname) values (:newTuple.professor); END; . run; Murali Mani

  13. Statement Level: Triggers CREATE TRIGGER t1 AFTER INSERT ON Student REFERENCING NEW as newTable BEGIN INSERT INTO Professor (pname) SELECT professor FROM Student where (professor IS NOT NULL AND professor NOT IN (SELECT pname FROM Professor)); END; . run; Murali Mani

More Related