Alter table add primary key not recognised MySQL

user_12788827 0
Viewed 363

I am trying to run the statement ALTER TABLE invoice_partitioned DROP PRIMARY KEY, ADD PRIMARY KEY(invoice_partitioned.id, invoice_partitioned.department_code);

This produces a syntax error (Error Code 1064) when run. MySQL Workbench is highlighting the bracket after ADD PRIMARY KEY, with the message '"(" is not valid at this position for this server version'.

Is there any way to resolve this issue? I am on MySQL Community Server 8.0.19

alter primary-key mysql sql
2 Answers

For example if we had a table:

create table invoice_partitioned (id int
                                  , department_code INT
                                  , primary key(ID));

Then this is the way:

ALTER TABLE invoice_partitioned DROP PRIMARY KEY;

ALTER TABLE invoice_partitioned ADD PRIMARY KEY(id, department_code);

Here is a demo

This will also work:

ALTER TABLE invoice_partitioned
DROP PRIMARY KEY,
ADD PRIMARY KEY(id, department_code);

Demo

user_65650380

you can do it in single statement

just don't add table name before column name as shown below

replace 

ALTER TABLE invoice_partitioned DROP PRIMARY KEY, ADD PRIMARY KEY(invoice_partitioned.id, invoice_partitioned.department_code);

to this

ALTER TABLE invoice_partitioned DROP PRIMARY KEY, ADD PRIMARY KEY (id, department_code);
user_114847940
Related