SQL Database Constraints and Updates PDF

SQL SQL  Constraints  Reaction policies  Schema updates SQL Constraints Constraints are conditions that must be verified by every database instance Two types: Intra-relational constraints - involve a single rela...

SQL SQL  Constraints  Reaction policies  Schema updates SQL Constraints Constraints are conditions that must be verified by every database instance Two types: Intra-relational constraints - involve a single relation Inter-relational constraints - involve several relations SQL Intra-relational constraints  Intra-relational constraints involve a single relation – not null (on single attributes) – unique: permits the definition of keys; syntax: for single attributes: unique, after the domain for multiple attributes: unique( Attribute {, Attribute } ) – primary key: defines the primary key (once for each table; implies not null); syntax like unique – check: described later SQL Example of intra-relational constraints  Each pair of FirstName and Surname uniquely identifies each element FirstName character(20) not null, Surname character(20) not null, unique(FirstName,Surname)  Note the difference with the following (stricter) definition: FirstName character(20) not null unique, Surname character(20) not null unique, SQL Inter-relational constraints  Constraints may take involve several relations: – check: described later – references and foreign key permit the definition of referential integrity constraints; syntax: for single attributes references after the domain for multiple attributes foreign key ( Attribute {, Attribute } ) references … It is possible to associate reaction policies to violations of referential integrity SQL Reaction policies for referential integrity constraints  Reactions operate on the table containing the foreign key, after changes to the external (referenced) table  Violations may be introduced by (1) updates of the referenced attributes (2) row deletions in the referenced table  Reactions: – cascade: propagate the change – set null: nullify the referring attributes – set default: assign the default value to the referring attributes – no action (also: restrict): forbid the change on the external table  Reactions may depend on the event; syntax: on < delete | update > < cascade | set null | set default | no action > SQL Example of inter-relational constraint and reaction policies create table Employee ( RegNo character(6) primary key, FirstName character(20) not null, Surname character(20) not null, Dept character (15) references Department(DeptName) on delete set null on update cascade, Salary numeric(9) default 0, City character(15), unique(Surname,FirstName) ) SQL Example of inter-relational constraint and reaction policies (alternative syntax) create table Employee ( RegNo char(6), FirstName char(20) not null, Surname char(20) not null, Dept char(15), Salary numeric(9) default 0, City char(15), primary key(RegNo), foreign key(Dept) references Department(DeptName) on delete set null on update cascade, unique(FirstName,Surname) ) SQL Schema updates  Two SQL statements: – alter (alter domain..., alter table …) – drop drop < schema | domain | table | view | assertion > ComponentName [ restrict | cascade ]  Examples: – alter table Department add column NoOfOffices integer – drop table TempTable cascade