[SQLObject] question on SQL schemas

[Bud P. B. <bu...@si...>]

Ian and all,

I am playing with ther person.py example with postgres and would like
to ask some questions to better understand what's happening.

For reference, see the sql for creating the tables [that I dumped using
the following postgresql command: pg_dump -F p -s <database>]
(see attached file)

The person table:

CREATE TABLE person (
    id serial NOT NULL,
    username character varying(20) NOT NULL,
    first_name character varying(30) NOT NULL,
    middle_initial character varying(1),
    last_name character varying(50) NOT NULL
);

What I am surprised about is the lack of use of UNIQUE and PRIMARY KEY
that I would have expected for username (since the alternateID option
is set) and id, respectively.  Note that in PostgreSQL, serial does
not automatically set UNIQUE (see
http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=datatype.html#DATATYPE-SERIAL)
Also, in the phone_number table, I would have expected person_id to
have a "REFERENCES person" option to indicate that it is a foreign
key...  

I'm not 100% sure, but wouldn't the use of UNIQUE and PRIMARY KEY also
imply that the dbms automatically creates indices and thus queries run
much faster?

thanks for the explanations!

--bud

/-----------------------------------------------------------------
| Bud P. Bruegger, Ph.D. 
| Sistema (www.sistema.it)
| Via U. Bassi, 54
| 58100 Grosseto, Italy
| +39-0564-411682 (voice and fax)
\-----------------------------------------------------------------