pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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.



view thread (7+ messages)  latest in thread

Message-ID: <20050528205226.GA20085@wolff.to>
Permalink:  ../20050528205226.GA20085@wolff.to/
Also on:    postgresql.org/message-id/20050528205226.GA20085@wolff.to

 · 

reply

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