Switch to: V12V11V10V9V8V7V6V5

Foreign Key Operations

person_phone_foreignkey.jpg

1. INSERTS

1.1. ADD A NEW RECORD into Phone and link it to a Person record at the same time.

INSERT INTO Phone( NUMBER, PersonPtr ) VALUES ( '111111', 1 );

1.2. ADD A NEW RECORD into Phone but do not link it to a Person record at the same time.

INSERT INTO Phone( NUMBER, PersonPtr ) VALUES ( '111111', NULL );

2. UPDATES

2.1. LINK AN EXISTING RECORD of Phone that is not linked to Person yet.

Consider this example as a step following the step 1.2. So value or PersonPtr is changed from NULL to 1.

UPDATE Phone SET PersonPtr = 1 WHERE RecID = 1;

2.2. LINK AN EXISTING RECORD of Phone that is linked to some Person record to another Person record.

Consider this example as a step following the step 2.1. So value for PersonPtr is changed from 1 to 5.

UPDATE Phone SET PersonPtr = 5 WHERE RecID = 1;

Let we have M:M link between tables Person and Phone, implemented with help of third table and two Foreign Keys:

person_phone_mm_3dtable_fk.jpg

1. INSERTS

1.1. ADD A NEW RECORD into Phone and link it to a Person record at the same time.

IMPOSSIBLE

1.2. ADD A NEW RECORD into Phone but do not link it to a Person record at the same time.

INSERT INTO Phone( NUMBER ) VALUES ( '111111' );

2. UPDATES

2.1. LINK AN EXISTING RECORD of Phone that is not linked to Person yet.

Since the link between the two records does not exists, this means that we need to ADD a NEW record into the MM table:

INSERT INTO tblPersonPhoneMM (PersonPtr, Phone) VALUES(1,1);

2.2. Switch Phone to another Person.

Since the link between the two records does exist as a record in the MM table, we need to UPDATE the value of PersonPtr field in order to switch phone from one Person to another.

UPDATE tblPersonPhoneMM SET PersonPtr = 5 WHERE PhonePtr = 1;