Table of Contents
This field plays the role of a UNIQUE IDENTIFIER of a record in the scope of its VTable. Each Valentina Table always have a RecID field.
The RecID field is of ULONG type (4 bytes). This fact applies a limit for the number of records per table at 2^32 = 4 billions.
RecID means actually the physical number of a record, so Valentina does not store RecID values on a hard disk. On pictures we draw the RecID field and its values on the left of the table to underline this fact.
The first physical record has a RecID equal to 1. The zero RecID value is used for an undefined record.
A record gets its RecID value when it is inserted into VTable. The RecID value is never changed during the lifetime of the record. Deleted records are marked in the Deletion Bitmap of VTable, but records are not moved physically as this happens for DBMS that use page-files to keep records. When a new record is inserted into VTable, it can reuse place of a deleted record, therefore RecID value will be reused.
This is okay, because Valentina DB keeps consistency of ObjectPtr and BinaryLinks based on RecID values.
See below for details.
RecID Field Access in API
You can access the RecID field only by name. Remember that the RecID field is of the type ULong, and you may need to cast to this type to get/set its value.
fldRecID = tblPerson.ULongField( "RecID" )
RecID Field in SQL
Valentina seamlessly integrates the RecID field into SQL. You can use it in any SELECT query. Note that “SELECT * …” does neither select the RecID nor other Table Methods. You should use “SELECT ** …” or specify the RecID field explicitly by name.
SELECT * FROM T -- does not select RecID but only custom fields. SELECT ** FROM T -- selects custom fields and RecID, OID and all other Table methods. SELECT RecID, f1, f2 FROM T -- selects 3 fields. SELECT f1, f2 FROM T ORDER BY RecID -- actually not very useful but works. DELETE FROM T WHERE recid IN (SELECT fldObjectPtr FROM T2)
Note that since the RecID field is a Table Method and therefore read-only, there is no sense in using it in the INSERT command. Even if you specify that, Valentina simply ignores it.
RecID and Record Migration
Record can change RecID only if it migrates to other database.
If you export pair of records related by RecID + ObjectPtr then you can easily “convert” this into PK + FK pair, as we do this in the relational databases.
On import of such pair into other database, it is clear that RecID and ObjectPtr values should be changed. Value of RecID field should be stored in the ObjectPtr field of Valentina. In this case Valentina will automatically correct values of ObjectPtr field for such operations as db.Clone() or load of dumps.
In case your task requires a Table Key, which not reuse deleted values, you can:
1) Ignore RecID fields and use Primary Keys as you do in Relational DBs. 2) Mix: use RecID field and links to do joins, but have some unique key in table also for tasks that require it.
Developers of relational DBs usually choose between synthetic (e.g. int) and natural (e.g. passportID) Primary Keys. Different DBMS provide different features to help developers with synthetic Primary Keys:
Valentina DB supports both. But autoincrement is based on recID, so it still will reuse values. You can use a sequence or its simplest form Serial32 / Serial64 field types. Read More:
- Serial Types - about Serial32 and Serial64