Switch to: V12V11V10V9V8V7V6V5

Convert BinaryLink M:M to M:M Table with FK

Let you have tables Person and Phone linked as M:M with the help of BinaryLink.

person_phone_mm_blink.jpg

Final State

Let you want to switch from Binary Link to MM third table design using Foreign Keys. Reason of this can be - you have found that you need additional information associated with pairs of links.

person_phone_mm_3dtable_fk.jpg

Transition Steps

To switch from original state to target state you should make the following steps.

Create ID Fields

You need to create in tables Person and Phone new ID fields and specify them as Primary Keys. You should do some work to assign unique values for each record for these new ID fields.

Create MM Table

Create MM table with two fields that has types corresponded to ID fields of tables Person and Phone. Specify two Foreign Key links based on these fields to point Person and Phone tables.

Optionally you can create other columns in this table, but this not matter for the current task.

  • First of all it needs assign unique ID values for all existed records of tables Person and Phone. For this we can use such simple trick as:
UPDATE TABLE tblPerson SET ID = RecID 
  • Loop by BinaryLink to get pairs {RecID1, RecID2} of linked records of tables Person and Phone. For each such pair:
    • For found RecIDs locate records in tables Person and Phone to obtain values of corresponded ID fields.
    • Having now pair { ID1, ID2 } you can insert it into table tblPersonPhoneMM

Drop Binary Link because now table PersonePhoneMM contains MM links of records of Person and Phone tables.

API Method(s)

Valentina offers API method which is able execute above loop in the most effective way.

VLink2.CopyLinksTo( linkToLeftTable as VLink, linkToRightTable as VLink )

API Example

 

SQL Command(s)

COPY LINKS FROM blink_name TO link_to_left_name AND link_to_right_name

SQL Example

-- STEP 1: Create ID fields in tables:
ALTER TABLE tblPerson
    ADD COLUMN ID ULONG PRIMARY KEY
 
ALTER TABLE tblPhone
    ADD COLUMN ID ULONG PRIMARY KEY
 
-- STEP 2: Fill Primary Key using RecID
UPDATE tblPerson SET ID = RecID
UPDATE tblPhone SET ID = RecID
 
-- STEP 3: Create MM Table
CREATE TABLE tblPersonPhoneMM(
    PersonPtr ULONG 
        FOREIGN KEY linkToPerson 
        REFERENCES tblPerson ON DELETE CASCADE ON UPDATE CASADE,
    PhonePtr ULONG 
        FOREIGN KEY linkToPhone 
        REFERENCES tblPhone ON DELETE CASCADE ON UPDATE CASADE )
 
-- STEP 4: Copy Links
COPY LINKS FROM linkPersonPhone TO linkToPerson AND linkToPhone
 
-- STEP 5: Drop Binary Link
DROP LINK linkPersonPhone