This presentation is the property of its rightful owner.
1 / 15

# Referential Integrity & Foreign Keys PowerPoint PPT Presentation

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.

Referential Integrity & Foreign Keys

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

### 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

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]

### 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.

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

### 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.

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

### 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).

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

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

### 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 ?

### 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.

### 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

### Examples of an SQL Foreign Key (1)

• Give the relation CAR a foreign key, attribute Owner, referencingattribute ENo of EMPLOYEE.

Create TableCAR (RegNoChar(9) Primary Key,TypeVarchar2(24),OwnerChar(2) References EMPLOYEE( ENo )

) ;

Two equivalent versions, withno user-assigned constraint name.

Create TableCAR (RegNoChar(9) Primary Key,TypeVarchar2(24),OwnerChar(2),Foreign Key(Owner) References EMPLOYEE( ENo )

);

### Examples of an SQL Foreign Key (2)

Create TableSHIPMENT (PNoChar(2),SNoChar(2),QtyInteger, Constraint CAND_KEY Primary Key (Pno, SNo),Constraint FOR_KEY Foreign Key ( SNo ) References SUPPLIER (SNo ));

Create TableSHIPMENT (PNoChar(2),SNoChar(2)Constraint FOR_KEYReferences SUPPLIER (SNo ),QtyInteger,Constraint CAND_KEY Primary Key (Pno, SNo));

Two equivalent versions, withuser-assigned constraint names.

### Example of an SQL Foreign Key (3)

Create TableSUPERVISE (MgerChar(2),EmpChar(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.