Postgres Pro
Downloads
Home   >   mailing lists

Thread: How to create primary key

How to create primary key

From
Reg Me Please
Date:
Hi all.

What'd be the syntax to create a primary key on an already build table?

Thanks.

--
Reg me Please

Re: How to create primary key

From
Richard Huxton
Date:
Reg Me Please wrote:
> Hi all.
>
> What'd be the syntax to create a primary key on an already build table?

Hmm - so you want to sort of alter a table and add a primary key
constraint to an existing column?

ALTER TABLE my_table ADD PRIMARY CONSTRAINT (existing_column);

All in the manuals:
http://www.postgresql.org/docs/8.2/static/sql-altertable.html

--
   Richard Huxton
   Archonet Ltd

Re: How to create primary key

From
Raymond O'Donnell
Date:
On 06/11/2007 08:54, Reg Me Please wrote:

> What'd be the syntax to create a primary key on an already build table?

ALTER TABLE test ADD CONSTRAINT test_pk PRIMARY KEY (f1);

http://www.postgresql.org/docs/8.2/static/sql-altertable.html


---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------

Re: How to create primary key

From
Samantha Atkins
Date:
On Nov 6, 2007, at 1:15 AM, Raymond O'Donnell wrote:

> On 06/11/2007 08:54, Reg Me Please wrote:
>
>> What'd be the syntax to create a primary key on an already build
>> table?
>
> ALTER TABLE test ADD CONSTRAINT test_pk PRIMARY KEY (f1);
>

So, ALTER TABLE test ADD PRMARY KEY(f1 [, ... ] )
isn't enough?  It appears in the examples.  What more does the
explicit test_pk do that wouldn't happen anyway?

- samantha

Re: How to create primary key

From
Magnus Hagander
Date:
On Tue, Nov 06, 2007 at 01:38:31AM -0800, Samantha Atkins wrote:
>
> On Nov 6, 2007, at 1:15 AM, Raymond O'Donnell wrote:
>
> >On 06/11/2007 08:54, Reg Me Please wrote:
> >
> >>What'd be the syntax to create a primary key on an already build
> >>table?
> >
> >ALTER TABLE test ADD CONSTRAINT test_pk PRIMARY KEY (f1);
> >
>
> So, ALTER TABLE test ADD PRMARY KEY(f1 [, ... ] )
> isn't enough?  It appears in the examples.  What more does the
> explicit test_pk do that wouldn't happen anyway?

It's not needed. It gives a name to the primary key. If you don't specify
one, the system will pick one for you automatically.

//Magnus

Re: How to create primary key

From
Raymond O'Donnell
Date:
On 06/11/2007 09:38, Samantha Atkins wrote:

> So, ALTER TABLE test ADD PRMARY KEY(f1 [, ... ] )
> isn't enough?  It appears in the examples.  What more does the explicit
> test_pk do that wouldn't happen anyway?

<confession>
To make sure I got it right, I tried it out using PgAdmin and copied the
generated SQL into my email - hence the pedantically complete statement. :-)
</confession>

Ray.

---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------

Re: How to create primary key

From
"Albe Laurenz"
Date:
Reg Me Please wrote:
> What'd be the syntax to create a primary key on an already 
> build table?

As described in the documentation:

ALTER TABLE <name> ADD [CONSTRAINT <constraint name>]
PRIMARY KEY (<colname> [, ...])
[WITH (FILLFACTOR = <number>)] [USING INDEX TABLESPACE <tbsp name>]

Yours,
Laurenz Albe

Re: How to create primary key

From
Ron Johnson
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 11/06/07 03:15, Richard Huxton wrote:
> Reg Me Please wrote:
>> Hi all.
>>
>> What'd be the syntax to create a primary key on an already build table?
>
> Hmm - so you want to sort of alter a table and add a primary key
> constraint to an existing column?

While I understand your feelings, I must disagree.

That's because adding a secondary index does not have the same sort
of syntax:
ALTER TABLE foo ADD INDEX (some, existing, columns);

> ALTER TABLE my_table ADD PRIMARY CONSTRAINT (existing_column);
>
> All in the manuals:
> http://www.postgresql.org/docs/8.2/static/sql-altertable.html
>


- --
Ron Johnson, Jr.
Jefferson LA  USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHMFr/S9HxQb37XmcRAuqfAKCzmYeBU4dVXorvQxBUYIoQuw9YcgCeOve1
NUtFo4N/sC4julpZmMvLN0s=
=qcCO
-----END PGP SIGNATURE-----

Re: How to create primary key

From
"Albe Laurenz"
Date:
Richard Huxton wrote:
>> What'd be the syntax to create a primary key on an already 
>> build table?
> 
> ALTER TABLE my_table ADD PRIMARY CONSTRAINT (existing_column);

I'm sure it's just a typo, but that is wrong.
It is not ADD PRIMARY CONSTRAINT, but ADD PRIMARY KEY.

Yours,
Laurenz Albe

Re: How to create primary key

From
Richard Huxton
Date:
Albe Laurenz wrote:
> Richard Huxton wrote:
>>> What'd be the syntax to create a primary key on an already
>>> build table?
>> ALTER TABLE my_table ADD PRIMARY CONSTRAINT (existing_column);
>
> I'm sure it's just a typo, but that is wrong.
> It is not ADD PRIMARY CONSTRAINT, but ADD PRIMARY KEY.

It is indeed a typo. Thanks Albe.

Particularly irritating as I'd bothered give a link to the right manual
page too...

--
   Richard Huxton
   Archonet Ltd