How to create primary key

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

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

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

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

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

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

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

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

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