Using SERIAL to choose a unique surrogate key

See also newer document and example (ordering a pizza)

Surrogate Key:
A meaningless unique value, used as a database key in place of either a lengthly natural, meaningful key, or when no obvious key exists. For example, how to identify "one instance of submitting a form," or "one secret ballot."
The prinary table must be created with a SERIAL type. (This automatically creates a “sequence”, whose nextval() function does the work as a default value.) The sequence bears the name of the table,, column, and 'seq', with underscores between them. For example, 'mytable_id_seq'
I recommend that this column be placed last, because it should never be given a value other than the default.
In your php code, as part of the same script
  1. Insert a new row in the primary table (such as all the single-valued items from a form)
  2. Get the currval() of the sequence automatically (see next item) (-- -- you can also call my function newid($atable, $acol) for this if you reallly want to know the number)
  3. Insert multiple rows with this seequence number into subsidiary table(s) where there are multiple-valued items. This can be done simply by setting the default for the foreign key to currval('atable_acol_seq').

How? See Code for this page


Important: permissions

PHP scripts always run as the user apache. If you connect simply to the database, as I recommend, you will of course have to remember to grant permissions on your table to apache. You also must also grant select, update on your sequence
jensen=> grant insert,select on automat to apache;
GRANT
jensen=> grant select, update on automat_id_seq to apache;
GRANT

Testing function newid

This code inserts a row, and then gets the value of "id".
The returned value from the sequence is:
Warning: Undefined array key "name" in /var/www/localhost/htdocs/jensen/serial.php on line 72
10787

Now let's do something useful. Insert this:

Name