MySQL Error 1068 multiple primary key defined

Question

There is an existed table that, and I want to alter the table by adding only 1 primary key directly, but the system shows Error 1068 multiple primary key defined. Where are the multiple primary keys??? I tried to drop the table and create+alter again, and it works. I just wonder why I can't run them separately？

Here is my code:

CREATE TABLE MY_DB.EMPLOYEE_INFORMATION (
EMP_ID VARCHAR(75) NOT NULL ,
EMP_NAME VARCHAR(75) NOT NULL ,
EMP_NUMBER VARCHAR(75) NOT NULL ,
EMP_SEX VARCHAR(75) NOT NULL ,
EMP_BIRTH TIMESTAMP NOT NULL ,
EMP_TEL VARCHAR(75) NOT NULL ,
EMP_WECHAT VARCHAR(75) NOT NULL ,
EMP_EMAIL VARCHAR(75) NOT NULL ,
EMP_SCHOOL VARCHAR(75) NOT NULL ,
EMP_GRADE VARCHAR(75) NOT NULL ,
EMP_MAJOR VARCHAR(75) NOT NULL ,
UPDAT_DATE TIMESTAMP NOT NULL );

ALTER TABLE MY_DB.EMPLOYEE_INFORMATION 
ADD CONSTRAINT PK_EMPLOYEE_INFORMATION PRIMARY KEY (EMP_NUMBER)

Error:

Error Code: 1068. Multiple primary key defined

Answer 1

You are trying to add a primary key twice.

Somewhere, somehow, you have already added a primary key to the EMPLOYEE_TIME_STATUS table. We can't tell how. Also the CREATE TABLE statement you showed us up top is for a table EMPLOYEE_INFORMATION, which will have no bearing on the EMPLOYEE_TIME_STATUS table.

Answer 2

Which Mysql version are you using? If it's 8.0 ver, MySQL supports generated invisible primary keys for any InnoDB table that is created without an explicit primary key, When the sql_generate_invisible_primary_key server system variable is set to ON, the MySQL server automatically adds a generated invisible primary key (GIPK) to any such table:

mysql> SELECT @@sql_generate_invisible_primary_key;

And should query:

mysql> SHOW COLUMNS FROM EMPLOYEE_TIME_STATUS;

to determine if another PK exists.