Drop primary key without the Constraint name

[Guest]

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!

 

[Marshall Barton]

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.

 

[Guest]

Hi Marshall,

Thank you for your reply. It helps! =)