Drop primary key without the Constraint name

A

ashley

Hi,

I've created a primary key for table1 in design view and now I've discovered
the need to add another column as the primary key, but I would like to do it
in DDL.

Therefore I've tried out the query:
"ALTER TABLE table1 ADD CONSTRAINT pk_table1 PRIMARY KEY(field1, filed2);"

but it throws an error saying that a primary key already exist. May I know
what are the ways I can go about to add this extra key to table1?

I've thought of dropping the previous primary key and add both fields as key
with the ALTER TABLE statements, but I do not have the name of the primary
key constraint created in Design view.

Please give some advice. Thanks!
 
M

Marshall Barton

ashley said:
Hi,

I've created a primary key for table1 in design view and now I've discovered
the need to add another column as the primary key, but I would like to do it
in DDL.

Therefore I've tried out the query:
"ALTER TABLE table1 ADD CONSTRAINT pk_table1 PRIMARY KEY(field1, filed2);"

but it throws an error saying that a primary key already exist. May I know
what are the ways I can go about to add this extra key to table1?

I've thought of dropping the previous primary key and add both fields as key
with the ALTER TABLE statements, but I do not have the name of the primary
key constraint created in Design view.


In the table's design view, use the View - Index menu item
to see the names of all the declared indexes (constraints).

OTOH, it's not really necessary to do that. Instead of
trying to add a second PK, just make it a non-Null, Unique
index. The only(?) difference is that Compact defrags a
table with increasing PK values in adjacent records.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top