InterBase Referential Integrity
InterBase has a number of options for Referential Integrity that are powerful to use and simplify keeping data valid.
This video covers (more reading from the InterBase DocWiki on the links here)
Tip for adding Constraints.
One very cool tip in the video is to use constraints to add in a unique key, this way you can drop it easily at runtime. Remember, the field needs to be marketed NOT NULL to enable a primary or unique key.
ALTER TABLE CUSTOMER ADD CONSTRAINT U_TEST UNIQUE (CUSTOMER_NAME);
The above example names the constraint U_TEST. (you can put whatever value you want in to name it, making it easy to drop with the following statement.
ALTER TABLE CUSTOMER DROP CONSTRAINT U_TEST;
InterBase Labs
For more InterBase Labs videos visit the InterBase pages on the Embarcadero website.
Curious what the field type of the primary key is recommended?
We have two large applications one uses integer values and the other a varchar(32).
The integer uses a database generator to establish the value.
For insert requires one database access to get the value and another to update the record. When adding 100,000 records actually requires 200.000 database accesses.
The generator value can be modified (value reduced) and later cause a duplicate value to be generated.
Typically happens during the conversion of a new customer when the step to set the initial value is set AFTER records are converted instead of BEFORE records are inserted. Sometimes takes a few weeks after the new customer is operating when the duplicate primary key error is displayed.
The varchar is generated with a GUID value. When adding 100,000 records requires 100,000 database access.
Performance wise (not counting inserts) we do not see a noticeable difference.
Both have their benefits. I have also used a mixed string made up of a site identifier and a generated value from a generator in the past. This was very handy with replication to identify where data had come from when used in multi-database consolidation.