Using SERIAL to choose a unique surrogate key
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'
- 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."
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
- Insert a new row in the primary table (such as all the single-valued items from a form)
- 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)
- 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').
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;
jensen=> grant select, update on automat_id_seq to apache;
Testing function newid
This code inserts a row, and then gets the value of "id".
The returned value from the sequence is:
Now let's do something useful. Insert this: