From: Bruno Wolff III <bruno@wolff.to>
To: David Pratt <fairwinds@eastlink.ca>
Cc: pgsql-admin@postgresql.org
Subject: Re: Adding Serial Type
Date: Sat, 28 May 2005 15:52:26 -0500
Message-ID: <20050528205226.GA20085@wolff.to> (raw)
In-Reply-To: <46D712FF-CFAD-11D9-AEE7-000A27B3B070@eastlink.ca>
References: <20050528182754.GA11909@wolff.to>
<46D712FF-CFAD-11D9-AEE7-000A27B3B070@eastlink.ca>
On Sat, May 28, 2005 at 16:18:30 -0300,
David Pratt <fairwinds@eastlink.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@eastlink.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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: bruno@wolff.to, fairwinds@eastlink.ca
Subject: Re: Adding Serial Type
In-Reply-To: <20050528205226.GA20085@wolff.to>
References: <20050528182754.GA11909@wolff.to> <46D712FF-CFAD-11D9-AEE7-000A27B3B070@eastlink.ca> <20050528205226.GA20085@wolff.to>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox