CODEWITHSUNDEEP
mysqlsqlmysql-error-1075
user1888406
user1888406

Reputation: 97

Mysql Error: #1075

SQL query:

ALTER TABLE  `blog` CHANGE  `id`  `id` BIGINT NOT NULL AUTO_INCREMENT

MySQL said: 

#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

I am trying to create a blog, and I got the code done. Now, I need to make the id auto increase, but I get this error. Why am I getting this?

Upvotes: 9

Views: 41514

Answers (4)

Emil Kiurpekov
Emil Kiurpekov

Reputation: 1

Also be aware if you put auto_increment on two columns it may still produce the error

Upvotes: 0

Anirudh Sood
Anirudh Sood

Reputation: 1428

To resolve #1075 error message you need to mark atleast one column as primary_key or unique_key. that you have forgot to do.

By defining Primary _key on ID column my error is resolved guys.

thanks

Upvotes: 1

spencer7593
spencer7593

Reputation: 108370

MySQL is returning that error (most likely) because there is no unique index defined on the id column. (MySQL requires that there be a unique index. The other possibility, which you would have already figured out, is that there can be only one column defined as AUTO_INCREMENT within the table.)

To get that column to be an AUTO_INCREMENT, you can add either a UNIQUE constraint or a PRIMARY KEY constraint on the id column. For example:

ALTER TABLE `blog` ADD CONSTRAINT `blog_ux` UNIQUE (`id`) ;

(Note that this statement will return an error if any duplicate values exist for the id column.)

Alternatively, you can make the id column the PRIMARY KEY of the table (if the table doesn't already have a PRIMARY KEY constraint defined).

ALTER TABLE `blog` ADD PRIMARY KEY (`id`) ;

(Note that this statement will return an error if any duplicate value exist for the id column, OR if there are any NULL values stored in that column, of if there is already a PRIMARY KEY constraint defined on the table.)

Upvotes: 11

codingbiz
codingbiz

Reputation: 26376

MySQL requires you to make auto increment column the primary key of a table. Add the primary key constraint at the end

ALTER TABLE  `blog` MODIFY COLUMN `id` BIGINT NOT NULL AUTO_INCREMENT primary key

Upvotes: 7

Related Questions