How to drop a primary key? I get incorrect table definition
I am trying to run this query:
ALTER TABLE table DROP PR开发者_如何学编程IMARY KEY, ADD PRIMARY KEY( `CUSTNO` , `DEPTNO` , `PRODNO` , `DT` );
I get
Incorrect table definition; there can be only one auto column and it must be defined as a key
You have to alter your pk column so that it hasn't auto_increment modifier anymore.
you'll have to do this in 3 (or 4) steps:
- remove the "auto-incremet" attribute from your current primary key
- drop your primary key
- set the new primary key
- (reset "auto_incremet" to your orl primary-key-column)
EDIT: maybe setting a new primary isn't what you realy want to do. please take a look at unique indexes - i think thats waht you want to set on your other columns to make sure they don't occur more than once.
First and foremost, if I'm correct you're defining a compound key? This is usually bad practice. It's better to have an extra ID column and to add a separate constraint to check you have a unique combination. And as codymanix suggested, you need to first alter the column to not have auto_increment anymore and then drop it.
精彩评论