Postgres Pro
Downloads
Home   >   mailing lists

Thread: SERIAL and Primary Key

SERIAL and Primary Key

From
"Mike Gould"
Date:
CREATE TABLE "fav5"."batchnumber" (
  "batchnumberid" SERIAL NOT NULL,
  "processtype" SMALLINT NOT NULL,
  "termloc" CHAR(3) NOT NULL,
  "batchno" INTEGER NOT NULL
) WITHOUT OIDS;

With the above table definition, is batchnumberid by default also defined as the primary key or do I still need to define a separate Primary Key constraint?

 


Best Regards,

Michael Gould
All Coast Intermodal Services, Inc.
904-226-0978

Re: SERIAL and Primary Key

From
Erik Jones
Date:
On Feb 29, 2008, at 2:28 PM, Mike Gould wrote:

> CREATE TABLE "fav5"."batchnumber" (
>   "batchnumberid" SERIAL NOT NULL,
>   "processtype" SMALLINT NOT NULL,
>   "termloc" CHAR(3) NOT NULL,
>   "batchno" INTEGER NOT NULL
> ) WITHOUT OIDS;
> With the above table definition, is batchnumberid by default also
> defined as the primary key or do I still need to define a separate
> Primary Key constraint?
>

No, you need to add the PRIMARY KEY bit which means you can also drop
the NOT NULL bit for that field.

Erik Jones

DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com

Re: SERIAL and Primary Key

From
hubert depesz lubaczewski
Date:
On Fri, Feb 29, 2008 at 03:28:11PM -0500, Mike Gould wrote:
> CREATE TABLE "fav5"."batchnumber" (
>   "batchnumberid" SERIAL NOT NULL,
>   "processtype" SMALLINT NOT NULL,
>   "termloc" CHAR(3) NOT NULL,
>   "batchno" INTEGER NOT NULL
> ) WITHOUT OIDS;
> With the above table definition, is batchnumberid by default also defined as the primary key or do I still need to
definea separate Primary Key constraint?   

wouldn't it be faster to simply try it?

it will be just a simple column. you have to add primary key
separately.

depesz

--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA.  here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)

Re: SERIAL and Primary Key

From
"Scott Marlowe"
Date:
On Fri, Feb 29, 2008 at 2:28 PM, Mike Gould <mgould@allcoast.net> wrote:
>
>
> CREATE TABLE "fav5"."batchnumber" (
>   "batchnumberid" SERIAL NOT NULL,
>   "processtype" SMALLINT NOT NULL,
>   "termloc" CHAR(3) NOT NULL,
>   "batchno" INTEGER NOT NULL
> ) WITHOUT OIDS;
>
> With the above table definition, is batchnumberid by default also defined as
> the primary key or do I still need to define a separate Primary Key
> constraint?

Once upon a time a serial type was assumed to be a PK (or was that
unique not null???) automagically.  However, this was considered bad
form since some uses of sequence did not need those extra settings.

Serial is JUST serial nowadays, and has been for quite some time.