Re: [SQLObject] question on SQL schemas

[Ian B. <ia...@co...>]

On Wed, 2003-04-09 at 12:59, Bud P.Bruegger wrote:
> 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...  

Quite true, quite true.  I thought SERIAL implied PRIMARY KEY, but
apparently not.  I'll change the ID column to be SERIAL PRIMARY KEY.

As far as UNIQUE, that requires another option to Col.  But that's
reasonable.

For foreign keys, the KeyCol needs to be expanded.  I'm used to MySQL,
which has very simple (primitive?) joins, where it's entirely implied. 
Postgres goes further, but I'm not familiar with the syntax.  KeyCol
needs to be expanded in general, because its arguments should be
different than Col's arguments.

  Ian