|
FAQ: Why PostgreSQL Sequence mechanism would be better than Clarion Auto-increment? |
|
|
|
|
Written by Bernard Grosperrin
|
|
Wednesday, 25 April 2007 |
|
To make the question a little broader, why would it be better to have an external counter, like a sequence in PostgreSQL, rather than simply increment the last value found in the table, which Clarion autoincrement does, or like SELECT MAX(id) + 1 would also do?
There are different answers, and I may miss a few here, but here is my take on this:
- If for some reason, there is NO cascade delete, or as a result of an error, a parent is deleted but not the childs, you end up with orpheans childs. If the parent was the last, and a new parent is added, the ID number will be the same than the previous one, and the new record get (inherit?) "illegitimate" childs, as they have not been explicitly created FOR the new record.
- With an external counter, the next record get the next number, previous record deleted or not, and the childs are "real" orpheans.
- It's up to the application to have a method recovering and affecting orpheans, if needed (or deleting them). So it enforce better referential integrity to use a PostgreSQL sequence.
- To use a PostgreSQL sequence for your primary key, simply use the type "SERIAL" instead of INTEGER, and tell Clarion that auto-increment is server side. PostgreSQL will automatically generate a sequence for you, with the name "table_field_seq", that is, assuming a sysid column in the table customers, your sequence will be "customers_sysid_seq".
|
|
Last Updated ( Sunday, 29 April 2007 )
|
