Re: Adding Serial Type

On Sat, May 28, 2005 at 16:18:30 -0300,
David Pratt <fairwinds(at)eastlink(dot)ca> wrote:
>
> On Saturday, May 28, 2005, at 03:27 PM, Bruno Wolff III wrote:
>
> >On Sat, May 28, 2005 at 14:27:17 -0300,
> > David Pratt <fairwinds(at)eastlink(dot)ca> wrote:
> >>Pretty basic question. Is it necessary to add NOT NULL or UNIQUE NOT
> >>NULL to SERIAL or is this implicit and unnecessary?
> >
> >Serials no longer generate a uniqie index by default. So in practice
> >you will normally want to declare them as PRIMARY KEYs. However there
> >are cases where you don't need this and the index is extra overhead.
> >
>
> Alright. so would it be better form for me to to this in a
> create_tables.sql
>
> CREATE TABLE new_table (
> id SERIAL,

Its simpler to use:
id SERIAL PRIMARY KEY,

> description TEXT NOT NULL
> );
>
> And then in a create_primary_keys.sql do this for the tables requiring
> it.
>
> ALTER TABLE new_table ADD CONSTRAINT new_table_pkey PRIMARY KEY (id);
>
> Does the PRIMARY KEY declaration ensure that the id values are unique?
> Serial should always give me an incremented value that's different so I
> am assuming it is unnecessary to use UNIQUE. Am I correct?

PRIMARY KEY implies UNIQUE and NOT NULL and in Postgres will result in a
unique index being created to enforce this. It will also make id the
default column in new_table for foreign key references to that table.