Chapter Outline, Declare foreign-key constraints, reference Maintain integrity, restrictions on property and Tuples, modify the constraints, the trigger options in the Design,. the content of the lecture chapter 2 "Constraints, Triggers, Views", invite you to refer for further reference accommodate learning and research. | Chapter 2 Constraints, Triggers, Views Chapter Outline Keys and Foreign Key Constraints on Attributes and Tuples Modification of Constraints Assertion Trigger Views Declaring Foreign-Key Constraints In SQL we may declare an attribute or attributes of one relation to be a foreign key, referencing some attribute(s) of a second relation (possibly the same relation) The referenced attribute(s) of the second relation must be declared UNIQUE or the PRIMARY KEY for their relation. Otherwise, we cannot make the foreign-key declaration. Values of the foreign key appearing in the first relation must also appear in the referenced attributes of some tuple. Two ways to declare a foreign key. Example: Suppose we wish to declare the relation Studio(name, address, presC#) which has a foreign key presC# that references cert# of relation: MovieExec(name, addreses, cert#, netWorth) We may declare presC# directly to reference cert# as follows: CREATE TABLE Studio ( name CHAR(30) . | Chapter 2 Constraints, Triggers, Views Chapter Outline Keys and Foreign Key Constraints on Attributes and Tuples Modification of Constraints Assertion Trigger Views Declaring Foreign-Key Constraints In SQL we may declare an attribute or attributes of one relation to be a foreign key, referencing some attribute(s) of a second relation (possibly the same relation) The referenced attribute(s) of the second relation must be declared UNIQUE or the PRIMARY KEY for their relation. Otherwise, we cannot make the foreign-key declaration. Values of the foreign key appearing in the first relation must also appear in the referenced attributes of some tuple. Two ways to declare a foreign key. Example: Suppose we wish to declare the relation Studio(name, address, presC#) which has a foreign key presC# that references cert# of relation: MovieExec(name, addreses, cert#, netWorth) We may declare presC# directly to reference cert# as follows: CREATE TABLE Studio ( name CHAR(30) PRIMARY KEY, address VARCHAR(255), presC# INT REFERENCES MovieExec(cert#)); Two ways to declare a foreign key (cont.). Example: An alternative form is to add the foreign key declaration separately, as CREATE TABLE Studio ( name CHAR(30) PRIMARY KEY, address VARCHAR(255), presC# INT, FOREIGN KEY (presC#) REFERENCES MovieExec(cert#) ); Maintaining Referential Integrity. The following actions will be prevented by the DBMS (., a run-time exception or error will be generated). Insert a new Studio tuple whose presC# value is not NULL and is not the cert# component of any MovieExec tuple. Update a Studio tuple to change the presC# component to a non-NULL value that is not the cert# component of any MovieExec tuple. Delete a MovieExec tuple, and its cert# component, which is not NULL, appears as the presC# component of one or more Studio tuples. Update a MovieExec tuple in a way that changes the cert# value, and the old cert# is the value of presC# of some movie studio. .
