pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Adding Serial Type
7+ messages / 3 participants
[nested] [flat]

* Adding Serial Type
@ 2005-05-28 17:27  David Pratt <fairwinds@eastlink.ca>
  0 siblings, 2 replies; 7+ messages in thread

From: David Pratt @ 2005-05-28 17:27 UTC (permalink / raw)
  To: pgsql-admin

Pretty basic question. Is it necessary to add NOT NULL or UNIQUE NOT 
NULL to SERIAL or is this implicit and unnecessary?

ie.

CREATE TABLE new_table (
	id                             SERIAL UNIQUE NOT NULL,
	description             TEXT NOT NULL
);

Or should I just use below because Serial type implies this.

CREATE TABLE new_table (
	id                             SERIAL,
	description             TEXT NOT NULL
);


Regards,
David



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Adding Serial Type
@ 2005-05-28 18:27  Bruno Wolff III <bruno@wolff.to>
  parent: David Pratt <fairwinds@eastlink.ca>
  1 sibling, 1 reply; 7+ messages in thread

From: Bruno Wolff III @ 2005-05-28 18:27 UTC (permalink / raw)
  To: David Pratt <fairwinds@eastlink.ca>; +Cc: pgsql-admin

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.



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Adding Serial Type
@ 2005-05-28 19:18  David Pratt <fairwinds@eastlink.ca>
  parent: Bruno Wolff III <bruno@wolff.to>
  0 siblings, 2 replies; 7+ messages in thread

From: David Pratt @ 2005-05-28 19:18 UTC (permalink / raw)
  To: Bruno Wolff III <bruno@wolff.to>; +Cc: pgsql-admin


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,
	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?

Regards,
David






^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Adding Serial Type
@ 2005-05-28 20:35  John DeSoi <desoi@pgedit.com>
  parent: David Pratt <fairwinds@eastlink.ca>
  1 sibling, 1 reply; 7+ messages in thread

From: John DeSoi @ 2005-05-28 20:35 UTC (permalink / raw)
  To: David Pratt <fairwinds@eastlink.ca>; +Cc: pgsql-admin


On May 28, 2005, at 1:27 PM, David Pratt wrote:

> CREATE TABLE new_table (
> 	id                             SERIAL UNIQUE NOT NULL,
> 	description             TEXT NOT NULL
> );
>
> Or should I just use below because Serial type implies this.
>
> CREATE TABLE new_table (
> 	id                             SERIAL,
> 	description             TEXT NOT NULL
> );


The first one because it is possible for the serial to wrap around or 
some other bad thing could happen if the serial is accidently reset.

But if id is really the primary key, then I would use SERIAL PRIMARY 
KEY which implies UNIQUE NOT NULL. From the CREATE TABLE documentation:

The primary key constraint specifies that a column or columns of a 
table  may contain only unique (non-duplicate), nonnull values. 
Technically, PRIMARY KEY is merely a  combination of UNIQUE and NOT 
NULL, but  identifying a set of columns as primary key also provides  
metadata about the design of the schema, as a primary key  implies that 
other tables  may rely on this set of columns as a unique identifier 
for rows.


John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Adding Serial Type
@ 2005-05-28 20:52  Bruno Wolff III <bruno@wolff.to>
  parent: David Pratt <fairwinds@eastlink.ca>
  1 sibling, 0 replies; 7+ messages in thread

From: Bruno Wolff III @ 2005-05-28 20:52 UTC (permalink / raw)
  To: David Pratt <fairwinds@eastlink.ca>; +Cc: pgsql-admin

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.



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Adding Serial Type
@ 2005-05-28 20:54  Bruno Wolff III <bruno@wolff.to>
  parent: David Pratt <fairwinds@eastlink.ca>
  1 sibling, 0 replies; 7+ messages in thread

From: Bruno Wolff III @ 2005-05-28 20:54 UTC (permalink / raw)
  To: David Pratt <fairwinds@eastlink.ca>; +Cc: pgsql-admin

On Sat, May 28, 2005 at 16:18:30 -0300,
  David Pratt <fairwinds@eastlink.ca> wrote:
> 
> Serial should always give me an incremented value that's different so I 
> am assuming it is  unnecessary to use UNIQUE.  Am I correct?

Unless someone uses setval to lower the value or if you change the
configuration of the sequence to allow it to wrap around.



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Adding Serial Type
@ 2005-05-28 21:52  David Pratt <fairwinds@eastlink.ca>
  parent: John DeSoi <desoi@pgedit.com>
  0 siblings, 0 replies; 7+ messages in thread

From: David Pratt @ 2005-05-28 21:52 UTC (permalink / raw)
  To: pgsql-admin

Many thanks John and Bruno for your responses.  This helps clarify this 
for me.

Regards,
David



^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2005-05-28 21:52 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-05-28 17:27 Adding Serial Type David Pratt <fairwinds@eastlink.ca>
2005-05-28 18:27 ` Bruno Wolff III <bruno@wolff.to>
2005-05-28 19:18   ` David Pratt <fairwinds@eastlink.ca>
2005-05-28 20:52     ` Bruno Wolff III <bruno@wolff.to>
2005-05-28 20:54     ` Bruno Wolff III <bruno@wolff.to>
2005-05-28 20:35 ` John DeSoi <desoi@pgedit.com>
2005-05-28 21:52   ` David Pratt <fairwinds@eastlink.ca>

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