LINK/UNLINK Records

Valentina SQL has commands to Link and Unlink existing records of two tables using a Link.

These commands can be used for a Link of any kind: FOREIGN KEY, ObjectPtr and BinaryLink. However only BinaryLinks require this method. FK and ObjectPtr can link records using regular INSERT / UPDATE syntax - this puts values into Table Fields. But using this new syntax you get the common syntax for all kinds of VLinks, and if future you will change kind of link (e.g. from ObjectPtr to BinaryLink), these commands still will be valid.

Note: Link commands can link only existing records of tables. If you specify RecID of non-existing records, you will receive an error.

v_link_records
    :    LINK   RECORD[S] v_list_of_records OF table_name WITH 
         [EACH] RECORD[S] v_list_of_records OF table_name 
         [USING link_name]
         [IF NOT EXISTS] 

v_unlink_records
    :    UNLINK RECORD[S] v_list_of_records OF table_name FROM 
         [EACH] RECORD[S] v_list_of_records OF table_name 
         [USING link_name]
         [IF EXISTS] 

v_list_of_records
    :    ( v_link_value [, v_link_value ...] )
    |    subquery 

v_link_value
    :    LAST                              -- v7.4.4, same as last_recid_of_table(tbl_name)
    |    UINT
    |    dynamic_parameter_specification 
    |    column_reference                  -- v5.0 for Triggers, and @var

These commands are needed for Valentina Database Model to allow establish a link between two records using Binary Link or break such link. For other kinds of links such as ObjectPtr and ForeignKey, there is no need in such command, because they use a value of PTR field as the fact of a link. But for consistency, these commands work for all types of links. Therefore using these commands allows you to get stable code/commands even if you decide to change Link type between tables.

By default, the LINK command throws an error if you try to link a pair of records that already exist. This behavior is similar to other commands, e.g. when you try to create a table with the name that already exists. UNLINK command in a similar way throws an error if you try to unlink pair of records that are not linked. You can use suffix IF NOT EXISTS to suppress such error. This can be especially useful if you link to a few records.

Example:

LINK RECORD(1) OF T1 WITH RECORD(3) OF T2;
LINK RECORD(1) OF T1 WITH RECORD(3) OF T2;                 >>> error: records already linked.
LINK RECORD(1) OF T1 WITH RECORD(3) OF T2 IF NOT EXISTS;   >>> no error

Example:

This example demonstrates linking of few records, which have duplicate and ability to skip it using IF NOT EXISTS suffix.

LINK RECORD(1) OF T1 WITH RECORD(3) OF T2;
 
// ONLY records 1-2 will be linked. 1-3 pair causes an error AND stops the execution OF the command.
LINK RECORD(1) OF T1 WITH RECORD(2,3,5,8,9) OF T2;        

LINK RECORD(1) OF T1 WITH RECORD(3) OF T2;
 
// ALL pairs will be established. 1-3 already EXISTS, okay, silent skip. 
LINK RECORD(1) OF T1 WITH RECORD(2,3,5,8,9) OF T2 IF NOT EXISTS;  

Starting from 3.1 version of Valentina engine you can use SQL Binding for Link/Unlink commands.

Example:

query = "LINK RECORD (:1) OF Person WITH RECORD(:2) OF Phone"
db.SqlExecute( query, [25, 687] )

The benefit is that query string now have the same form (comparing byte to byte) and SQL Parser is able to reuse parsed tree from the pool of queries. This increases speed because the time of parsing become zero.

In many cases, you will want to link just added records of Tables T1 and T2. Starting with v5.0 you can use variables to obtains LastRecID values and pass them into LINK/UNLINK commands.

INSERT INTO T1(f1,f2,f3) VALUES(v1,v2,v3);
SET @last_T1 = last_recid_of_table( 'T1' );
 
INSERT INTO T2(f1,f2,f3) VALUES(v11,v12,v13);
SET @last_T2 = last_recid_of_table( 'T2' );
 
LINK RECORD(@last_T1) OF T1 WITH RECORD(@last_T2) OF T2;

Starting with v7.4.4 you can just specify keyword “last” in the parents to link last inserted records of tables. The example below do the same as the example above.

INSERT INTO T1(f1,f2,f3) VALUES(v1,v2,v3);
INSERT INTO T2(f1,f2,f3) VALUES(v11,v12,v13);
 
LINK RECORD(LAST) OF T1 WITH RECORD(LAST) OF T2;

In this section, we will consider in depth how we can use LINK/UNLINK commands with a LINK of 1:1 type.

The following example shows SQL command that allows you to link one record of table Person to one record of table Phone.

LINK RECORD(1) OF Person WITH RECORD(10) OF Phone

If tables Person and Phone have only single VLink between them, then Valentina will use it to link/unlink records. Otherwise, you must explicitly specify what link to use to avoid error message about ambiguity:

LINK RECORD (1) OF Person WITH RECORD (10) OF Phone USING PersonHasPone

This is an example of UNLINK commands that correspond to LINK commands in the previous example. Future in this article we may give examples with LINK commands only to save space.

UNLINK RECORD (1) OF Person FROM RECORD (10) OF Phone

You can link N records of the Left Table with N records of the Right Table with a single SQL command. For this, you can just specify several RecIDs in the list.

LINK RECORDS (1,2,3) OF Person WITH RECORDS (24,98,16) OF Phones
 
This command will produce 3 pairs OF records: 
  * (1,24), (2,98), (3,16).

Note that in this command we use plural form RECORDS, although this is just for nice reading. SQL Parser will accept both RECORD and RECORDS without complaints.

In this section, we will consider in depth how we can use LINK/UNLINK commands with a LINK of 1:M type.

Although the link is 1:M, in most cases in real life we still link at once only single pair of objects, exactly as described above for Link 1:1.

LINK RECORD(1) OF Person WITH RECORD(10) OF Phone

To link few pairs using a single SQL command you can specify this explicitly using lists of the same length on the left and right sides:

LINK RECORD (1,1,1,1,1) OF Person WITH RECORD (10,11,12,13,14) OF Phone
 
This command will produce 5 links:
  => (1,10), (1,11), (1,12), (1,13), (1,14).

You can specify even different records in the left list, but still important to have lists of the same length:

LINK RECORD (1,1,1,1,1,2,3) OF Person WITH RECORD (10,11,12,13,14,15,16) OF Phone
 
This command will produce 7 links:
  => (1,10), (1,11), (1,12), (1,13), (1,14) AND (2,15) AND (3,16)

Using word EACH you can get more natural syntax to link ONE record of the Left table to MANY records of the RIGHT table for case when only one record on the left side:

LINK RECORD (1) OF Person WITH EACH RECORD (10,11,12,13,14) OF Phone
 
This command will produce 5 links:
  => (1,10), (1,11), (1,12), (1,13), (1,14).

• If you forget to specify EACH and lists have different length, then you get an error.
• It is obvious that for 1:M link command with EACH keyword can contain only one RecID in the left list.

This command gives us the natural way to unlink ONE record of the Left table from MANY records of the RIGHT table.

UNLINK RECORD(1) OF Person FROM EACH RECORD(10,11,12,13,14) OF Phone

In the v4.7 was added the ability to unlink a record from all linked records. The example below shows few forms of the same command:

UNLINK RECORD (1) OF Person FROM EACH RECORD OF Phone
UNLINK RECORD (1) OF Person FROM RECORDS OF Phone

UPDATE tblPhone SET fldPersonPtr = NULL 
WHERE fldPersonPtr = 1

or even for few records at once:

UNLINK RECORDS (1,3,5) OF Person FROM EACH RECORD OF Phone
UNLINK RECORDS (1,3,5) OF Person FROM RECORDS OF Phone

UPDATE tblPhone SET fldPersonPtr = NULL 
WHERE fldPersonPtr IN (1,3,5)

In v5.0 we have even more extend this feature. Now you can do

UNLINK RECORD (1) OF Person FROM EACH RECORD
UNLINK RECORD (1) OF Person FROM RECORDS

to unlink record RecID=1 of Person table from all records linked to it in all tables linked to Person table.

All the commands work with RecID values of linked Tables. You may want to link some records using values of table fields. You can do this using form of LINK/UNLINK with sub-query.

-- This command will link all employers with sales lower 10000 
-- to Manager with recid = 1.
LINK RECORD (1) OF Manager 
WITH RECORDS (SELECT recid FROM Employers WHERE SalesTotal < 10000) OF Employers

You can also specify a sub-query for the LEFT table also.

-- This command will link all employers with sales lower 10000 to TopManager.
LINK RECORDS (SELECT recid FROM Manager WHERE POSITION = 'top') OF Manager
WITH RECORDS (SELECT recid FROM Employers WHERE SalesTotal < 10000) OF Employers

Note that subquery should SELECT only one field that produces RecID values. Query of any other kind will generate an error message.

In this section, we will consider in depth how we can use LINK/UNLINK commands with a LINK of M:1 type. For M:M Link you can do all the same as above with 1:1 and 1:M links, additionally you can do the following:

With M:M link you can specify few records on the left side using EACH keyword. In this case, each record from the left list will be linked to each record in the right list.

LINK RECORD (1,2) OF Person WITH EACH RECORD (10,11,12,13,14) OF Phone
 
This command will produce 10 links: 
  * (1,10), (1,11), (1,12), (1,13), (1,14).
  * (2,10), (2,11), (2,12), (2,13), (2,14).

Example of EACH usage:

LINK RECORDS (SELECT recid FROM Manager WHERE POSITION = 'top') OF Manager
WITH EACH RECORD (SELECT recid FROM Employers WHERE SalesTotal < 10000) OF Employers

Let we have record 10 of Person linked to record 8 of Phone. Let we want now UPDATE this link-pair, so record 10 of Person will be linked to e.g. 56. This operation can be done with help of UNLINK then LINK:

UNLINK RECORD (10) OF Person FROM RECORD (8) OF Phone;
LINK   RECORD (10) OF Person TO RECORD (56) OF Phone;

Instead of this, we can make single SQL call: (NOT IMPLEMENTED!)

LINK RECORD (10) OF Person TO RECORD (56) OF Phone INSTEAD OF (8);