Unanswered: How to: Using Primary Key/Foreign Key in Postgre
Just looking for a bit of guidance/best practice advice really.
I've read various postings, on various forums regarding the best practices for generating Primary Keys and retrieving them for use as a Foreign Key when inserting data into multiple tables at once.
What do you guys think is the best approach to use? So far, I've seen:
1: Using SERIAL as a datatype when creating the first table, then SELECTING MAX(the serial column) after an INSERT, and using that value as a Foreign Key in the INSERT into the second table
2: Using SEQUENCES, creating a key value by reading nextval(seq), and using that across all tables being inserted into.
3: Not bothering with any of that, and creating a key outside of the INSERTS to be used by them.
I'm sure there are pluses and minuses with each, but what do you guys see as the best approach and why?
Also, I've read some pundits comments stating that using INTEGER type for keys results in the best speed when joining across multiple tables. Is this true? (i.e. even if a table has a unique key candidate, such as a username of CHAR type, still create a unique ID column of type INTEGER using one of the three methods above).
if you have various tables that use independent primary keys
for example: table1 uses table1_id and table2 uses table2_id you can either use serail or sequence.
Personally i prefer creating a sequence on every table but i think most ppl prefer using SERIAL.
Im still exploring postgresql and sql in general but im pretty sure that integers used as primakeys/indexes is faster than varchar.
I was leaning towards that approach myself for three reasons:
1. Using a SERIAL type would mean that you won't know the value of the id column of the first table until after an INSERT, and only then, by performing a SELECT afterwards. Obviously, if you need that id to be used as a foreign key in a second table, what happens if a second INSERT is made before you've read back the id....problems! Maybe there is a way of locking the table that I'm not aware of (Postgre newie), but surely that would cause performance issues.
2: Having a SEQUENCE means that the value of the id column can be calculated prior to any INSERTs, hence avoiding all of the issues with 1 above.
3: They must have created the SEQUENCE feature for a reason!