Issue with Auto increment in MYSQL

Question

Current Structure in MYSQL:

CREATE TABLE `JOB_ONPREM` (
 `JOB_ID` bigint(20) NOT NULL AUTO_INCREMENT,
 `JOB_NAME` varchar(255) NOT NULL DEFAULT '',
 `SANDBOX` varchar(100) NOT NULL DEFAULT '',
 `JOB_SUB_TYPE` varchar(500) DEFAULT NULL,
 `PARENT_JOB_NAME` varchar(255) NOT NULL DEFAULT '',
 PRIMARY KEY (JOB_ID,`JOB_NAME`,`SANDBOX`,`PARENT_JOB_NAME`)
 ) ENGINE=InnoDB AUTO_INCREMENT=10493 DEFAULT CHARSET=utf8

I want to keep JOB_NAME,SANDBOX,PARENT_JOB_NAME as Primary KEY and JOB_ID as Auto Increment because I am using "ON DUPLICATE KEY UPDATE" and because of auto-increment it is inserting new rows and creating duplicate in table.

And while removing job_id from primary key. I am getting error as "Incorrect table definition; there can be only one auto column and it must be defined as a key"

• nbk: Thta wouldn't come, during deletion, could you provide and example for that behaviour

Answer 1

The CREATE TABLE documentation says much the same as what your error message says:

There can be only one AUTO_INCREMENT column per table, it must be indexed, and it cannot have a DEFAULT value.

You need to index the JOB_ID column.

ALTER TABLE JOB_ONPREM
ADD INDEX `dba269102` (`JOB_ID`)

Try that and see if afterwards you can alter the primary key. You could also make it a unique index instead, to help avoid duplicates.

ALTER TABLE JOB_ONPREM
ADD UNIQUE INDEX `dba269102` (`JOB_ID`)

• nbk: no the code runs without a problem adding is not problem is no problem, that is why i asked to clarify
• Ashu: Used above query and dropped existing primary key and create primary of 3 fields

Answer 2

Your question is incomplete.

JOB_ID as Auto Increment because I am using "ON DUPLICATE KEY UPDATE"

Rarely is an AUTO_INCREMENT necessary for IODKU. Please provide an example of why you need it.

Is some other table JOINing to this table on job_id? If not, can't you get rid of it.

Are there duplicate values of this combination (JOB_NAME,SANDBOX,PARENT_JOB_NAME) in the table? If no dups, then you should be able to have it as the sole PRIMARY KEY.

IODKU checks all unique keys (that includes the PK) when checking for ON DUPLICATE KEY. Do you need the 3-col test? 4-col? the auto_inc column? Or more than one of those? Show us the IODKU statement for starters.

While you are at it, do those columns really need to be 255 and 500 characters?