Tables > Table field types > Autoinc/Primary Key Fields

Autoinc or Autoincrement Fields

Autoinc fields are auto-incrementing and auto filled read only fields, often also specified as a Primary Key fields.
In BDE SQL the field type is specified as AUTOINC.
Technically Paradox Autoinc fields are to be considered as read only fields.
Though technical differences between Paradox (level 4, 5, 7) and dBASE (level 7), the function is the same and different naming is just a matter of table format.
However the dBASE 7 implementation having some serious issues - see below.

The BDE/Local SQL does not provide a means of initializing this type of field to a certain start value, neither is it possible to insert (typing) specific data to this kind of field.

INSERT INTO with specified values

Errors will always be raised, if you try to insert specified values into an Autoinc field, whatever a Primary Key field or not, and whatever by manual typing or as with:

INSERT INTO TableName VALUES (999,'ABC','DEF', etc.);
where the first value of this exmaple, whatever the number, is targetting an Autoinc field.

INSERT INTO from SELECT statement

However inserting data via a SELECT statement is where Paradox differs from dBASE 7:

Paradox INSERT INTO table SELECT

Executing
INSERT INTO ParadoxTable SELECT * FROM SourceTable
every value inserted into the AutoInc field will be re-initialized disregarding the original values from the source table, and this vay bypassing the read only nature of Autoinc fields.

dBASE 7 INSERT INTO table SELECT

Executing
INSERT INTO dBASETable SELECT * FROM SourceTable
and if the AutoInc fields is not PRIMARY KEY indexed, every value inserted into the AutoInc field will be inserted as from the original source table.
Inserting new records after this INSERT INTO procedure, the autogenerated AutoInc field values for new records will be renumbered starting from the value 1 and on, and thus may duplicate some of the AutoInc values already bulk inserted as above - thus generating a total mess if without the PRIMARY KEY! - anyway, when through the standard BDE access.

Target Autoinc field NOT being a Primary Key field

If the Autoinc field is NOT a Primary Key field, adding data where the values for the Autoinc field are duplicate values of the existing values in the Autoinc field in the target table, data will be added with new autoincremented values for the target Autoinc field.

Target Autoinc field specified as a Primary Key field

If the target table has a Primary Key field, what ever Autoinc or not, adding data where the source values are duplicates of the Primary Key values in the target table, source records will be ignored, and ignored records will reported in a ERRORINS.db/.dbf table created in the home directory of the PdxEditor application (your :PRIVATE: directory), and substituting any pre-existing table of the same name.
The ERRORINS table is also created when INSERT INTO violates unique indexes not being primary keys (as e.g. with dBASE IV/5 tables).

Resetting the Autoinc counter

If you intend deliberately to reset a Paradox table's Autoinc counter, do use the Restructure fields extended.