#Postgresql non sequential primary key serial
#Postgresql non sequential primary key update
#Postgresql non sequential primary key code

If you use a sequence in that case, you simply update other non-key columns in the base table and you're good to go. Do you really want to go through the main table and all the foreign keys that reference it, putting in the new country code- or living with the old country code, cause that's your choice.

#Postgresql non sequential primary key code

Example: Let's say you have a country code table and a revolution takes place and a country code changes. ID fields are much more likely to 'need to change' in the future, than a sequence. This can cause headaches in the long run, so even if you do use IDs on a table, you may want to steer clear from ever showing those IDs directly to your customer. Many people store ID's in human readable form, like 'VALID', or state abbreviations. Many ORMs can confuse those and leave off leading zeros causing errors in your results. Sometimes they're numeric, sometimes they're varchar or char. Example: 'where status_seq=1' is harder to read than 'where status_id='ACTIVE''. Sequences can result in 'hard to read' code when using 'lookup tables', especially when values are hard coded in your database. Sometimes, people instead generate hashes or GUIDs to result in a more balanced tree. When using b-tree indexes, sequences are generally inserted in ascending order, which can result in an 'unbalanced tree' and cause less than perfect performance (on b-tree indexes) over time. Additionally many shops require single column primary keys to assist further in code complexity. The best answer is to point you back to your situation.įirst, many people prefer sequences, as they are easy to generate and provide a single data type to navigate your joins. The primary key should also be the only key used for foreign key references, even if other columns - in isolation or together - are unique and candidate primary keys.

New inserts are then always at the 'end' (although if you are deleting data, then pages might only be partially used). This should also be the key used for clustering the data, if such a concept exists in the database. I do want to point out that using an auto-incremented surrogate key has other benefits. Otherwise, I would use the mechanism designed to work with tables.

#Postgresql non sequential primary key serial

An identity or serial column is built into the table.įor a single table, I have only considered using sequences in databases that do not support built-in auto-increment/serial/identity columns (ahem, 'Oracle'). A sequence requires managing two objects (the table and the sequence). There is a bigger difference from a data management perspective. Sharing a single sequence across multiple tables makes this problem worse. In a high transaction environment, inadequate caching can be a performance bottleneck. The rules for caching sequences might be different. One important difference is that some databases will cache identity columns, which can speed inserts but cause gaps. These would always be some sort of number in a single column.įrom the database performance perspective, there is not much difference between the two. In Postgres, these would be declared as serial. Your question is about using sequences versus identity ('generated always as identity' columns, presumably).