L14 - triggers

L14 - triggers - Last time, we talked about constraints...

Info iconThis preview shows pages 1–5. Sign up to view the full content.

View Full Document Right Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: Last time, we talked about constraints Last time, we talked about constraints -- We discussed integrity constraints, specifically referential and value integrity enforced with the aid of table constraints and check clauses We mentioned triggers as providing another mechanism for integrity enforcement But (as we shall see today) they can be used for more than that. But first, the one remaining slide from last time FOREIGN KEY...REFERENCES quick revisit Is a constraint that provides referential integrity for the data in the column or columns. FOREIGN KEY constraints require that each value in the column exists in the corresponding referenced column(s) in the referenced table. FOREIGN KEY constraints can reference only columns that are PRIMARY KEY or UNIQUE constraints in the referenced table or columns referenced in a UNIQUE INDEX on the referenced table. ref_table Is the name of the table referenced by the FOREIGN KEY constraint. ( ref_column [,... n ]) Is a column, or list of columns, from the table referenced by the FOREIGN KEY constraint. ON DELETE {CASCADE | NO ACTION} Specifies what action takes place to a row in the table created, if that row has a referential relationship and the referenced row is deleted from the parent table. The default is NO ACTION. If CASCADE is specified, a row is deleted from the referencing table if that row is deleted from the parent table. If NO ACTION is specified, SQL Server raises an error and the delete action on the row in the parent table is rolled back. ON UPDATE {CASCADE | NO ACTION} Specifies what action takes place to a row in the table created, if that row has a referential relationship and the referenced row is updated in the parent table. The default is NO ACTION. Integrity Constraints (Short Review) Integrity Constraints (Short Review) A constraint describes conditions that every legal instance of a relation must satisfy. Inserts/deletes/updates that violate integrity constraints are disallowed. Can be used to : ensure application semantics (e.g., sid is a key), or prevent inconsistencies (e.g., sname has to be a string, age must be < 200) Types of ICs : Fundamental: Domain constraints, primary key constraints, foreign key constraints General constraints : Check Constraints, Table Constraints and Assertions . Check and Table Constraints Check and Table Constraints CREATE TABLE Sailors ( sid INTEGER, sname CHAR(10), rating INTEGER, age INT, PRIMARY KEY (sid), CHECK ( rating >= 1 AND rating <= 10 ) ) Remember that MySQL (among other DBMSs) parses but does not enforce these. Explicit Domain Constraints...
View Full Document

Page1 / 23

L14 - triggers - Last time, we talked about constraints...

This preview shows document pages 1 - 5. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online