Table of Contents
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.
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.
You should check ObjectPtr Field page to read about ObjectPtr as a FIELD.
VObjectPtr fldPersonPtr = tblPhone.CreateObjectPtr( "PersonPtr", tblPerson, kCascade, fNullable + fIndex, "link_Person_Phone" );
CREATE TABLE Phone( NUMBER String, PersonPtr ObjectPtr INDEXED CONSTRAINT link_Person_Phone REFERENCES Person ON DELETE SET NULL );
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.
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.
Unique: 1:1 Link
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.
To Delete ObjectPtr Field (and Link) you need just drop this field.
ALTER TABLE Phone DROP COLUMN PersonPtr;
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.
INSERT INTO Phone(NUMBER, PersonPtr) VALUES( '11111', 547 )
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:
UPDATE Phone SET PersonPtr = 333 WHERE NUMBER = '222222'
tblPhone.RecID = fldNumber.FindSingleValue( "22222" ); fldPersonPtr.value = 333; tblPhone.UpdateRecord();
To unlink a child record you need set the value of PTR Field to be NULL(0).
UPDATE Phone SET PersonPtr = 0 WHERE PersonPtr = 333
tblPhone.RecID = fldPeronPtr.FindSingleValue( 333 ); fldPersonPtr.value = 0; tblPhone.UpdateRecord();