Thread: Create Primary Key?

Create Primary Key?

From
Webb Sprague
Date:
Apropos of my last question:

Is there syntax to create a primary key after the
table has been defined and populated?  I think I could
speed things up quite a bit by not having any indexes
at all when I do my mass copies.

Thanks, and my apologies if that is a totally stupid
question.

W

__________________________________________________
Do You Yahoo!?
Yahoo! Mail - Free email you can access from anywhere!
http://mail.yahoo.com/


Re: Create Primary Key?

From
Stephan Szabo
Date:
We don't currently support the SQL syntax for adding
a PK to a table.  However, if you have the columns
as NOT NULL already, adding a unique index to the
columns in question has the same general effect.

Stephan Szabo
sszabo@bigpanda.com

On Tue, 29 Aug 2000, Webb Sprague wrote:

> Apropos of my last question:
> 
> Is there syntax to create a primary key after the
> table has been defined and populated?  I think I could
> speed things up quite a bit by not having any indexes
> at all when I do my mass copies.



Re: Create Primary Key?

From
Jie Liang
Date:
Hi,

It suppose to use ALTER TABLE tablename ADD constraint xxx primary
key(columnname),
unforturatly, Pg7.0 still haven't implemented it yet, so except foreign
key ,other constarints,
you have to choose:
1. rename your old table, recreate an new one with primary key, then use

insert into newtable select * from oldtable.
2. use pg_dump -t tablename -f scriptfile dbname
in shell , edit that scriptfile, add primary key there,
drop your table, then reload it again:
psql dbname<scriptfile

Webb Sprague wrote:

> Apropos of my last question:
>
> Is there syntax to create a primary key after the
> table has been defined and populated?  I think I could
> speed things up quite a bit by not having any indexes
> at all when I do my mass copies.
>
> Thanks, and my apologies if that is a totally stupid
> question.
>
> W
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Mail - Free email you can access from anywhere!
> http://mail.yahoo.com/

--
Jie LIANG

Internet Products Inc.

10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873

jliang@ipinc.com
www.ipinc.com





Re: Create Primary Key?

From
darcy@druid.net (D'Arcy J.M. Cain)
Date:
Thus spake Stephan Szabo
> We don't currently support the SQL syntax for adding
> a PK to a table.  However, if you have the columns
> as NOT NULL already, adding a unique index to the
> columns in question has the same general effect.

Except for interfaces such as PyGreSQL that recognize the primary key
and use it.

> > Is there syntax to create a primary key after the
> > table has been defined and populated?  I think I could
> > speed things up quite a bit by not having any indexes
> > at all when I do my mass copies.

Why not just use the copy function?  That's what it's for.

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.


Re: Create Primary Key?

From
Stephan Szabo
Date:
On Wed, 30 Aug 2000, D'Arcy J.M. Cain wrote:

> Thus spake Stephan Szabo
> > We don't currently support the SQL syntax for adding
> > a PK to a table.  However, if you have the columns
> > as NOT NULL already, adding a unique index to the
> > columns in question has the same general effect.
> 
> Except for interfaces such as PyGreSQL that recognize the primary key
> and use it.

True.  You'd have to see what it was doing to determine the primary key
and try to do the same things.