Foreign Keys In relation R a clause that "attribute A references S(B)"says that whatever non- null values appear in the A column of R must also appear in the B column of relation S.B must be declared the primary key for S. Example: CREATE TABLE Beers(name CHAR(20)PRIMARY KEY,manf CHAR(20)); CREATE TABLE Sells bar CHAR(20),beer CHAR(20)REFERENCES Beers(name),price REAL); We expect a beer value is a real beer---something appearing in Beers.name
Foreign Keys In relation R a clause that “attribute A references S(B)” says that whatever nonnull values appear in the A column of R must also appear in the B column of relation S. B must be declared the primary key for S. Example: CREATE TABLE Beers ( name CHAR(20) PRIMARY KEY, manf CHAR(20) ); CREATE TABLE Sells ( bar CHAR (20), beer CHAR(20) REFERENCES Beers(name), price REAL) ; We expect a beer value is a real beer --- something appearing in Beers.name
Expressing Foreign Keys Use the keyword REFERENCES,either: Within the declaration of an attribute,when only one attribute is involved. 2 As an element of the schema,as: FOREIGN KEY (<list of attributes>) REFERENCES <relation> <attributes>) Referenced attributes must be declared PRIMARY KEY or UNIQUE
Expressing Foreign Keys z Use the keyword REFERENCES, either: 1. Within the declaration of an attribute, when only one attribute is involved. 2. As an element of the schema, as: FOREIGN KEY ( <list of attributes> ) REFERENCES <relation> ( <attributes> ) z Referenced attributes must be declared PRIMARY KEY or UNIQUE
Example:With Attribute CREATE TABLE Beers name CHAR (20) PRIMARY KEY manf CHAR(20)): CREATE TABLE Sells bar CHAR(20) beer CHAR (20) REFERENCES Beers (name), price REAL
Example: With Attribute CREATE TABLE Beers ( name CHAR(20) PRIMARY KEY, manf CHAR(20) ); CREATE TABLE Sells ( bar CHAR(20), beer CHAR(20) REFERENCES Beers(name), price REAL );
Example:As Element CREATE TABLE Beers（ name CHAR(20) PRIMARY KEY manf CHAR(20)): CREATE TABLE Se11s（ bar CHAR(20) beer CHAR(20) price REAL FOREIGN KEY (beer) REFERENCES Beers (name));
Example: As Element CREATE TABLE Beers ( name CHAR(20) PRIMARY KEY, manf CHAR(20) ); CREATE TABLE Sells ( bar CHAR(20), beer CHAR(20), price REAL, FOREIGN KEY(beer) REFERENCES Beers(name));
What happens when a foreign key Constraint is violated Two ways: 1.Insert or update a Sells tuple so it refers to a nonexistent beer always rejected. 2. Delete or update a Beers tuple that has a beer value some Sells tuples refer to a Default:reject b) Cascade:Ripple changes to referring Sells tuple c )Set null
What happens when a foreign key Constraint is violated ? Two ways: 1. Insert or update a Sells tuple so it refers to a nonexistent beer Æ always rejected. 2. Delete or update a Beers tuple that has a beer value some Sells tuples refer to a) Default: reject b) Cascade: Ripple changes to referring Sells tuple c) Set null