Switch to: V12V11V10V9V8V7V6V5

ObjectPtr Link

Overview

ObjectPtr field is a unique Valentina Database Model feature. ObjectPtr allows to set M:1 and 1:1 relationship between the two Tables. As only you have create an ObjectPtr field you have establish also the Link between two tables.

person_phone_objectptr.jpg

This kind of Link is similar to both FOREIGN KEY of Relational Model and to direct pointer of Network Model.

This Field keeps values of the RecID field of parent/target Table. So its type is ULONG and the size is 4 bytes.

ObjectPtr can be used as both FIELD and LINK.

This section considers ObjectPtr as a LINK mostly.
You should check ObjectPtr Field page to read about ObjectPtr as a FIELD.

ObjectPtr Creation

API EXAMPLE:

VObjectPtr fldPersonPtr = tblPhone.CreateObjectPtr( 
        "PersonPtr", tblPerson, kCascade, fNullable + fIndex, "link_Person_Phone" );

SQL EXAMPLE:

CREATE TABLE Phone(
    NUMBER String[12], 
    PersonPtr ObjectPtr INDEXED 
        CONSTRAINT link_Person_Phone 
        REFERENCES Person 
        ON DELETE SET NULL );

Target Table

When you create an ObjectPtr field you must specify the Target Table.

Pay attention that the name of Link created by ObjectPtr Field differs from the name of Field. So creating ObjectPtr Field you need specify both. If you have not specify the Link name then Valentina will assign some name automatically, which you can change later. (You'd better choose different names for ObjectPtr as a link and as a field to avoid possible ambiguity in SQL statements )

ON DELETE Action

When you create an ObjectPtr field/link you can specify the Action ON DELETE, which defines what should happens with a child record, when you delete its parent record.

Nullable

Note, that ObjectPtr field, as well as FK, in most cases should accept NULL values, what means that you can have child records, which do not have parent. All such orphan child records will have NULL(0) value of ObjectPtr.

Note: If you will assign ZERO to ObjectPtr field, then Valentina consider it as NULL. This is natural, because ZERO means non-existing RecID.

If you want prohibit existance of child records without parent (rare case) you need make the ObjectPtr field to be NOT NULL.

You can specify that ObjectPtr field is UNIQUE, in this case you get semantic of 1 : 1 Link. Really, you cannot insert into ObjectPtr field the same RecID value of a parent record.

Compare to FK Creation

It is interesting to compare SQL queries of ObjectPtr and FOREIGN KEY:

CREATE TABLE T2( 
  T1_PTR ULONG CONSTRAINT link_T1_T2 
               REFERENCES TABLE_1.ID
               ON DELETE CASCADE 
               ON UPDATE CASCADE 
) 

and its analog using ObjectPtr field:

CREATE TABLE T2( 
  T1_PTR ObjectPtr 
              CONSTRAINT link_T1_T2 
              REFERENCES TABLE_1 
              ON DELETE CASCADE 
) 

You can see that ObjectPtr field needs only Target Table specification, but do not need the key field of that table, because always a RecID field of Target Table is used. Also ObjectPtr field do not need ON UPDATE action.

ObjectPtr Deletion

To Delete ObjectPtr Field (and Link) you need just drop this field.

SQL EXAMPLE:

ALTER TABLE Phone DROP COLUMN PersonPtr;

API EXAMPLE:

tblPhone.DropField( fldPersonPtr );

Work With Records

Linking New Record

To link a child record to some parent record you need just assign RecID value of parent record into ObjectPtr field of child record.

SQL EXAMPLE:

INSERT INTO Phone(NUMBER, PersonPtr) VALUES( '11111', 547 )

API EXAMPLE:

tblPhone.SetBlank();
fldPersonPtr.value = 547;
tblPhone.AddRecord();

Linking Existing Record

To link existing child record to existing parent record you need assign RecID of parent record into PTR Field:

SQL EXAMPLE:

UPDATE Phone SET PersonPtr = 333 WHERE NUMBER = '222222'

API EXAMPLE:

tblPhone.RecID = fldNumber.FindSingleValue( "22222" );
fldPersonPtr.value = 333;
tblPhone.UpdateRecord();

Unlinking Records

To unlink a child record you need set the value of PTR Field to be NULL(0).

SQL EXAMPLE:

UPDATE Phone SET PersonPtr = 0 WHERE PersonPtr = 333

API EXAMPLE:

tblPhone.RecID = fldPeronPtr.FindSingleValue( 333 );
fldPersonPtr.value = 0;
tblPhone.UpdateRecord();

Also See