1 / 15

Referential Integrity & Foreign Keys

Referential Integrity & Foreign Keys. Objectives of the Lecture :. To consider Referential Integrity & Foreign Keys; To consider Referential Integrity Constraints in SQL. EMPLOYEE. CAR. RegNo. ENo. 5. 5. K123 ABC. 5. Owner. E3. 1. 1. 1. E3. E3. E3. 6. 6. W811 STA. Sal. 2.

lazaro
Télécharger la présentation

Referential Integrity & Foreign Keys

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. Referential Integrity & Foreign Keys Objectives of the Lecture : • To consider Referential Integrity & Foreign Keys; • To consider Referential Integrity Constraints in SQL.

  2. EMPLOYEE CAR RegNo ENo 5 5 K123 ABC 5 Owner E3 1 1 1 E3 E3 E3 6 6 W811 STA Sal 2 E5 E5 2 Type 2 2 E5 7 7 E5 M-S JON 1 EName 12,500 2 2 2 Corsa 1.3 E1 3 3 E1 2 S 2 2 E1 E1 8 V771 PQ 8 8 Smith 5 5 5 4 4 21,000 Starlet GLi 4 4 E6 E6 4 E6 E6 M 4 4 Mitchell 6 6 Jaguar XK 6 32,500 6 E8 6 6 D Volvo S80 7 7 Robson 8 8 54,000 8 8 8 M 8 Blake 8 8 8 68,000 W Jones CAR[Owner] is a subset of EMPLOYEE[ENo] Example of Referential Integrity (1)

  3. Purpose of Referential Integrity To ensure that two different relations are consistent with each other. Example : Consider the CAR relation. We know that it does not make sense for an owner in CAR not to be an employee in EMPLOYEE. So every CAR‘Owner’ attribute value must appear in the EMPLOYEE‘ENo’ attribute. So the set of CAR‘Owner’ attribute values must be a subset of the EMPLOYEE‘ENo’ values. • Consistency between a DB’s relations is often required.

  4. EMPLOYEE CAR RegNo ENo K123 ABC 5 5 5 Owner 1 1 E3 1 1 1 1 E3 E3 E3 6 6 W811 STA Sal E5 2 E5 2 2 2 Type 2 2 2 2 E5 E5 7 7 M-S JON 1 EName 2 2 12,500 2 Corsa 1.3 E1 3 3 E1 3 3 2 2 2 S E1 E1 8 V771 PQ 8 8 5 5 Smith 5 4 21,000 4 Starlet GLi 4 E6 4 4 4 4 E6 4 E6 E6 M 4 4 Mitchell 6 6 Jaguar XK 6 6 32,500 E8 E8 D 6 6 Volvo S80 Robson 7 7 8 8 8 54,000 8 8 M 8 Blake 8 8 8 ReferencingAttribute. 68,000 ReferencedAttribute. W Jones Example of Referential Integrity (2)

  5. Definition of Referential Integrity • The values in a referencing attribute must be the same set or a (proper) subset of the values in the referencedattribute. • Thus both attributes must be drawn from the same underlying data type. Referential integrity can be generalised to apply between two corresponding sets of attributes, where the sets may contain more than one attribute. Hence the full definition is :- • The set of n-tuples in the referencing set of n-attributes must be the same set or a (proper) subset of the n-tuples in the referencedset of n-attributes. • Thus both sets of n-attributes must be based on the same set of underlying data types.

  6. EMPLOYEE CAR RegNo ENo K123 ABC 5 5 5 Owner E3 1 1 1 E3 1 1 1 E3 E3 6 6 W811 STA Sal E5 2 2 2 2 E5 Type 2 2 2 2 E5 E5 7 7 M-S JON 1 EName 2 12,500 2 2 Corsa 1.3 3 3 E1 3 3 E1 2 2 2 S E1 E1 8 V771 PQ 8 8 Smith 5 5 5 4 21,000 4 Starlet GLi 4 E6 4 E6 4 4 4 4 E6 E6 M 4 4 Mitchell 6 6 Jaguar XK 6 6 32,500 E8 E8 D 6 6 Volvo S80 7 7 Robson 8 8 54,000 8 8 M 8 8 8 8 Blake 8 68,000 Foreign Key This is a Primary /Candidate Key. W Jones Example of Referential Integrity (3)

  7. Foreign Keys • The referencing (set of) attribute(s) is called a Foreign Key. • The Foreign Key gets its name because traditionally the referenced(set of) attribute(s) is always a candidate key. • In SQL, the referenced(set of) attribute(s) must be a primary or alternate key.However this is not logically necessary, and in principle a Foreign Key may reference any (set of) attribute(s) with the same underlying data type(s).

  8. PNo SNo 5 5 P1 5 S1 PNo 6 6 S1 P2 P1 5 5 5 7 7 P2 S2 5 6 6 P2 2 2 SHIPMENT SUPPLIER 10 7 7 8 8 P3 8 S2 P2 Qty SNo SNo SNo 12 8 P3 8 8 Details S1 S1 ……………….. 7 S1 S2 2 2 …………….. S2 S3 ……………….….. S2 S1 S1 S2 S1 S2 S2 S3 Key Overlap • A foreign key can occur within a candidate key, or overlap with it. Example -Foreign Key within a Candidate Key :- CandidateKey Foreign Key

  9. SUPERVISE Mger Emp E1 E1 E1 E1 E1 E2 E3 E1 E1 E5 E2 E2 E2 E2 E4 E2 E2 E6 E3 E3 E7 Self-Referential Integrity • A foreign key can reference an attribute(s) in the same relation as itself. Example -A manager must also be an employee :- CandidateKey Foreign Key

  10. Further Considerations • The link between foreign and candidate keys is asymmetric.It is the foreign key that is dependent on the candidate key for its values, not vice versa. • Note that a foreign key can, and often does, have values that are replicated in more than one tuple, maybe in many tuples. • The set of foreign key values (ignoring replicas) must be a subset of the candidate key’s set of values.It is useful to consider whether the foreign key set can or should be equal to the candidate key set, or be a proper subset of them, or be allowed to vary between these two possibilities. • Is it essential that the foreign key always have a candidate key value, or is it permissible for it to be missing ?

  11. SQL Referential Integrity • Foreign Keys are allowed to be NULLs (unless additionally specified not to).If NULLs are allowed,thena Foreign Key must contain either a Primary/Alternate Key value or be null. • Referential integrity constraints can be named in the same way asprimary/alternate key constraints. • A single attribute can be made a foreign key in the same sub-statement in which it is assigned its data type,ORA foreign key can be assigned to one or more attributes in a separate sub-statement at the end of a Create Table statement.

  12. SQL Foreign Key Assignment • Assign a single attribute to be a foreign key in the same sub-statement in which it is assigned its data type. After the data type, append ReferencesTABLENAME ( AttributeName ) Two possibilities : optional Insert actual namesof table & attribute. Keyword • Assign one or more attributes to a foreign key in a separate sub-statement at the end of a Create Table statement. Foreign Key ( AttributeName(s) )ReferencesTABLENAME ( AttributeName(s) ) optional

  13. Examples of an SQL Foreign Key (1) • Give the relation CAR a foreign key, attribute Owner, referencingattribute ENo of EMPLOYEE. Create TableCAR (RegNo Char(9) Primary Key,Type Varchar2(24),Owner Char(2) References EMPLOYEE( ENo ) ) ; Two equivalent versions, withno user-assigned constraint name. Create TableCAR (RegNo Char(9) Primary Key,Type Varchar2(24),Owner Char(2),Foreign Key(Owner) References EMPLOYEE( ENo ) );

  14. Examples of an SQL Foreign Key (2) Create TableSHIPMENT (PNo Char(2), SNo Char(2),Qty Integer, Constraint CAND_KEY Primary Key (Pno, SNo),Constraint FOR_KEY Foreign Key ( SNo ) References SUPPLIER (SNo )); Create TableSHIPMENT (PNo Char(2), SNo Char(2)Constraint FOR_KEYReferences SUPPLIER (SNo ),Qty Integer,Constraint CAND_KEY Primary Key (Pno, SNo)); Two equivalent versions, withuser-assigned constraint names.

  15. Example of an SQL Foreign Key (3) Create TableSUPERVISE (Mger Char(2), Emp Char(2), Constraint CAND_KEY Primary Key (Emp),Constraint FOR_KEY Foreign Key ( Mger ) References SUPERVISE ( Emp )); • Ensure that the first row entered is the manager that manages him/herself ! (I.e. the root of the hierarchical tree). • This is because self-referentiality applies. It prevents any row being put into the table whose employee does not have a manager. • Self-referentiality will generally cause analogous problems, whatever its nature.

More Related