How to drop a primary key? I get incorrect table definition.

Question

I am trying to run this query:

ALTER TABLE table DROP PRIMARY 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

Answer 1

You have to alter your pk column so that it hasn't auto_increment modifier anymore.

Answer 2

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.

Answer 3

you'll have to do this in 3 (or 4) steps:

1. remove the "auto-incremet" attribute from your current primary key
2. drop your primary key
3. set the new primary key
4. (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.