Using automatically generated primary key (SERIAL)

See also older example

Often we would like the dbms to create primary keys for us, when we only need them to form a relationship between tables. One could argue that Name, even though it might serve the purpose of a primary key, is too long and error prone to be practical. But we don't want the trouble of "making up" a shorter key.
SERIAL is an SQL  "data type" that autoincrements, and can be used to create a primary key when either there is no "natural" primary key, or you want a short one for convenience.

Actually, SERIAL can only be used in a create table statement, and the data type is really INT. In fact, it is short for int, creating a sequence, and declaring a default of the nextval( the sequence ). Associated rows of another table may  Here is an example, for ordering a pizza, which may have several toppings. Once a row is inserted into pizza, rows inserted into toping may be assigned by default, to relate to that pizza.

create table pizza(
	phone 	varchar(20) not null,
	address	varchar(30),
	size 	char(1) default 'L',
	orderno serial primary key,
	at 	timestamp default now()
);
create table top (
	top 	varchar(15) not null,
	orderno int default currval('pizza_orderno_seq') references pizza
);
GRANT select, insert ON pizza, top TO apache;
GRANT select, update ON pizza_orderno_seq TO apache;

Note also the GRANT statements needed if apache is going to place the orders. You must also grant permission on the sqquence. In the insert statements, we don't even mention orderno. The default is only used when no value is supplied. Here is my first order, and the natural join for the result.

jensen=> insert into pizza values ('123-456','16 Bowen','M');
INSERT 0 1
jensen=> insert into top values ('cheese');
INSERT 0 1
jensen=> insert into top values ('black olives');
INSERT 0 1
jensen=> insert into top values ('pepperoni');
INSERT 0 1
jensen=> insert into top values ('broccoli');
INSERT 0 1
jensen=> select * from pizza natural join top;
 orderno |  phone  | address  | size |             at             |     top     
---------+---------+----------+------+----------------------------+--------------
       1 | 123-456 | 16 Bowen | M    | 2010-02-18 19:56:37.920877 | cheese
       1 | 123-456 | 16 Bowen | M    | 2010-02-18 19:56:37.920877 | black olives
       1 | 123-456 | 16 Bowen | M    | 2010-02-18 19:56:37.920877 | pepperoni
       1 | 123-456 | 16 Bowen | M    | 2010-02-18 19:56:37.920877 | broccoli
(4 rows)

To see how SERIAL is implemented, we can look at the description given by psql for table pizza:

 phone   | character varying(20)       | not null
 address | character varying(30)       |
 size    | character(1)                | default 'L'::bpchar
 orderno | integer                     | not null default nextval('pizza_orderno_seq'::regclass)
 at      | timestamp without time zone | default now()
Indexes:
    "pizza_pkey" PRIMARY KEY, btree (orderno)

Using this in PHP

... is straightforward. Try it.

The code for the php page to insert the order is:

$phone = $_GET['phone']; $address = $_GET['address']; $size = $_GET['size'];
if (!$phone) die ("We need your phone number.");
$conn=pg_connect ('dbname=jensen') or die ('Connect failed ');
$query1 = 'INSERT into pizza values ($1, $2, $3) returning orderno';
$query2 = 'INSERT into top values ($1)';
$res = pg_query_params($query1, array($phone, $address, $size))
		 or die("pizza query failed".pg_last_error());
foreach ($_GET['topping'] as $top)
   pg_query_params($query2, array($top))
		 or die("topping query failed".pg_last_error());
// query 1 returned the new order number...
$orderno = pg_fetch_result($res,0,0);
setcookie("pizza", $orderno);	// remember the order number
?>
Thank you. 
<a href=pizzasee.php>Check on your order</a>

$order = $_COOKIE['pizza']; 
settype($order, 'int');		// no Bobby Tables
if (!$order) die ("Your pizza is lost.");
$conn=pg_connect ('dbname=jensen') or die ('Connect failed ');
$res = pg_query("select * from pizza natural join top where orderno=$order");
$first = pg_fetch_assoc($res) or die ('no such order');
print $first['phone']."<br>\n";
print $first['address']."<br>\n";
$sizes = array('S'=>'SMALL', 'M'=>'MEDIUM','L'=>'LARGE');
$size=$sizes[$first['size']];		// expand size
print "You have ordered a $size pizza on<br>\n";
print date("d F Y  h:i a", strtotime($first['at']))."<hr>\n";  // nice date 

print $first['top']."<br>\n";		// now the toppings
while ($next = pg_fetch_assoc($res))
   print $next['top']."<br>\n";