Ошибка 1075 mysql - Решение и исправление самых разных ошибок на TopOshibok.ru

Here is a table in MySQL 5.3.X+ db:

CREATE TABLE members` (
  `id` int(11)  UNSIGNED NOT NULL AUTO_INCREMENT,
  `memberid` VARCHAR( 30 ) NOT NULL ,
  `Time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  `firstname` VARCHAR( 50 ) NULL ,
  `lastname` VARCHAR( 50 ) NULL ,
  UNIQUE (memberid),
  PRIMARY KEY (id) 
) ENGINE = MYISAM;

Id column is never used in queries, it is just for visual convenience (so it’s easy to see how the table grows). Memberid is an actual key, is unique, and memberid is used in queries to identify any member (WHERE memberid=’abcde’).

My question is: how to keep auto_increment, but make memberid as a primary key? Is that possible?
When I try to create this table with PRIMARY KEY (memberid), I get an error:

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

What is the best choice (Hopefully, there is a way to keep id column so performance is good and queries identify any user by memberid, not by id), if the performance is very important (although the disk space is not)?

OMG Ponies

326k82 gold badges523 silver badges502 bronze badges

asked Nov 13, 2011 at 20:35

HaradzieniecHaradzieniec

9,08631 gold badges117 silver badges215 bronze badges

You can have an auto-Incrementing column that is not the PRIMARY KEY, as long as there is an index (key) on it:

CREATE TABLE members ( 
  id int(11)  UNSIGNED NOT NULL AUTO_INCREMENT,
  memberid VARCHAR( 30 ) NOT NULL , 
  `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP , 
  firstname VARCHAR( 50 ) NULL , 
  lastname VARCHAR( 50 ) NULL , 
  PRIMARY KEY (memberid) ,
  KEY (id)                          --- or:    UNIQUE KEY (id)
) ENGINE = MYISAM;

answered Nov 13, 2011 at 21:44

ypercubeᵀᴹypercubeᵀᴹ

113k19 gold badges174 silver badges235 bronze badges

First create table without auto_increment,

CREATE TABLE `members`(
    `id` int(11) NOT NULL,
    `memberid` VARCHAR( 30 ) NOT NULL ,
    `Time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
    `firstname` VARCHAR( 50 ) NULL ,
    `lastname` VARCHAR( 50 ) NULL
    PRIMARY KEY (memberid) 
) ENGINE = MYISAM;

after set id as index,

ALTER TABLE `members` ADD INDEX(`id`);

after set id as auto_increment,

ALTER TABLE `members` CHANGE `id` `id` INT(11) NOT NULL AUTO_INCREMENT;

CREATE TABLE IF NOT EXISTS `members` (
    `id` int(11) NOT NULL,
    `memberid` VARCHAR( 30 ) NOT NULL ,
    `Time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
    `firstname` VARCHAR( 50 ) NULL ,
    `lastname` VARCHAR( 50 ) NULL,
      PRIMARY KEY (`memberid`),
      KEY `id` (`id`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

answered Jan 20, 2017 at 6:30

Thilina SampathThilina Sampath

3,6156 gold badges39 silver badges65 bronze badges

You can make the id the primary key, and set member_id to NOT NULL UNIQUE. (Which you’ve done.) Columns that are NOT NULL UNIQUE can be the target of foreign key references, just like a primary key can. (I’m pretty sure that’s true of all SQL platforms.)

At the conceptual level, there’s no difference between PRIMARY KEY and NOT NULL UNIQUE. At the physical level, this is a MySQL issue; other SQL platforms will let you use a sequence without making it the primary key.

But if performance is really important, you should think twice about widening your table by four bytes per row for that tiny visual convenience. In addition, if you switch to INNODB in order to enforce foreign key constraints, MySQL will use your primary key in a clustered index. Since you’re not using your primary key, I imagine that could hurt performance.

answered Nov 13, 2011 at 21:40

I think i understand what the reason of your error.
First you click auto AUTO INCREMENT field then select it as a primary key.

The Right way is First You have to select it as a primary key then you
have to click auto AUTO INCREMENT field.

Very easy.
Thanks

answered Oct 16, 2016 at 14:12

For the above issue, first of all if suppose tables contains more than 1 primary key then first remove all those primary keys and add first AUTO INCREMENT field as primary key then add another required primary keys which is removed earlier. Set AUTO INCREMENT option for required field from the option area.

answered Jan 28, 2015 at 9:16

Identified this solution while reading this thread. Figured id post this for the next guy possibly.

When dealing with Laravel migration file from a package, I Ran into this issue.

My old value was

$table->increments('id');

My new

$table->integer('id')->autoIncrement();

answered Oct 12, 2020 at 1:37

levilevi

1,5663 gold badges21 silver badges37 bronze badges

Источник

I try to «alter Table»
I need one more AI field, not key…
«List»

ID INT(11):PK Not Null AutoIn..
Name VARCHAR
UserID INT(11):FK Not Null
edit BOOL

and now i need one more field «sortpos» as AI.
I try it with MySQL Workbench

ALTER TABLE `**mydb**`.`List` 
ADD COLUMN `sortpos` INT(11) NOT NULL AUTO_INCREMENT AFTER `edit`;

Can u help me?

Thx

asked Mar 12, 2014 at 21:32

You can’t get better error message than this one. You already have ID defined as Auto Increment in your table. Now you are trying to add another field sortpos as auto increment which is not allowed. One table can only have one auto increment which must be defined as primary key.

Remove AUTO_INCREMENT from the alter statement and create a trigger to increment the new column.

answered Mar 12, 2014 at 23:26

RizRiz

1,11915 silver badges23 bronze badges

Based on your comments, you are confusing user interface with table data. The table only needs to have one ID, if you want you can create a query like this:

SELECT ID, ID AS SORTPOS, NAME FROM List

But you don’t even need a query for that, you should do it only at user interface level.

Plus, what you show in your comment is merely the heading of a list, not the list itself.

answered Mar 14, 2014 at 6:41

korianderkoriander

3,1102 gold badges15 silver badges23 bronze badges

Источник

MySQL is a popular open-source relational database management system used by developers to store and manage data. It is known for its excellent performance, scalability, and reliability. However, it is not without its quirks and issues. One of the most common errors that MySQL users encounter is the «Incorrect table definition; there can be only one auto column and it must be defined as a key» error. This error message can be frustrating, but fortunately, there is a straightforward solution that we’ll discuss in this guide.

What Causes the «Incorrect Table Definition» Error?

The «Incorrect table definition; there can be only one auto column and it must be defined as a key» error occurs when you try to create a table in MySQL with more than one column set to auto-increment. MySQL requires that there be only one auto-increment column per table, and that column must be defined as a key. If you try to create a table with more than one auto-increment column or forget to define the auto-increment column as a key, you’ll see this error message.

How to Fix the «Incorrect Table Definition» Error

To fix the «Incorrect table definition» error, you need to ensure that you have only one auto-increment column per table and that column is defined as a key. Follow these steps to fix the error:

Identify the table causing the error: The first step is to identify the table that is causing the error. Look for the table name in the error message, which should be something like «Error Code: 1075. Incorrect table definition; there can be only one auto column and it must be defined as a key.»

Remove the auto-increment attribute from the non-key column(s): Once you’ve identified the table causing the error, remove the auto-increment attribute from any column(s) that are not keys. You can do this by modifying the table definition using the ALTER TABLE statement. For example, if your table has columns named id and name, and you want id to be the auto-increment key, run the following command:

ALTER TABLE table_name MODIFY COLUMN name data_type;

Replace table_name with the name of your table, name with the name of the non-key column you want to remove the auto-increment attribute from, and data_type with the data type of the column.

Define the auto-increment column as a key: The last step is to define the auto-increment column as a key. You can do this by modifying the table definition using the ALTER TABLE statement. For example, if your table has a column named id that you want to define as the auto-increment key, run the following command:

ALTER TABLE table_name MODIFY COLUMN id INT AUTO_INCREMENT PRIMARY KEY;

Replace table_name with the name of your table and id with the name of the auto-increment column.

After following these steps, you should be able to create the table without encountering the «Incorrect table definition» error.

FAQ

Q1. Can I have multiple auto-increment columns in a MySQL table?

No, you can only have one auto-increment column per table in MySQL.

Q2. What data types can I use for the auto-increment column in MySQL?

You can use the following data types for the auto-increment column in MySQL: TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT.

Q3. Can I change the auto-increment value in MySQL?

Yes, you can change the auto-increment value in MySQL using the ALTER TABLE statement. For example, if you want to set the auto-increment value to 100, run the following command:

ALTER TABLE table_name AUTO_INCREMENT = 100;

Replace table_name with the name of your table and 100 with the value you want to set.

Q4. What is a primary key in MySQL?

A primary key is a column or a set of columns in a table that uniquely identifies each row. It is used for indexing and to ensure data integrity.

Q5. What are some common MySQL errors?

Some common MySQL errors include syntax errors, connection errors, and permission errors. Other common errors include the «Table ‘table_name’ already exists» error and the «Unknown column ‘column_name’ in ‘field list'» error.

MySQL ALTER TABLE Statement
MySQL Data Types
MySQL Primary Key

Источник

MySQL возвращает эту ошибку (скорее всего), потому что нет уникального индекса, определенного в столбце id. (MySQL требует, чтобы был уникальный индекс. Другая возможность, которую вы уже поняли, состоит в том, что в таблице может быть только один столбец, определенный как AUTO_INCREMENT.)

Чтобы этот столбец был AUTO_INCREMENT, вы можете добавить ограничение UNIQUE или ограничение PRIMARY KEY в столбце id. Например:

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

(Обратите внимание, что этот оператор возвращает ошибку, если для столбца id существуют одинаковые значения.)

В качестве альтернативы вы можете сделать столбец id основным символом таблицы (если в таблице еще нет ограничения PRIMARY KEY).

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

(Обратите внимание, что этот оператор возвращает ошибку, если для столбца id существует любое дублирующее значение, или если в этом столбце есть значения NULL, если в таблице есть ограничение PRIMARY KEY.)

Источник

Here is a table in MySQL 5.3.X+ db:

CREATE TABLE members` (
  `id` int(11)  UNSIGNED NOT NULL AUTO_INCREMENT,
  `memberid` VARCHAR( 30 ) NOT NULL ,
  `Time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  `firstname` VARCHAR( 50 ) NULL ,
  `lastname` VARCHAR( 50 ) NULL ,
  UNIQUE (memberid),
  PRIMARY KEY (id) 
) ENGINE = MYISAM;

My question is: how to keep auto_increment, but make memberid as a primary key? Is that possible?
When I try to create this table with PRIMARY KEY (memberid), I get an error:

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

This question is related to
mysql
primary-key
auto-increment
mysql-error-1075

The answer is

You can have an auto-Incrementing column that is not the PRIMARY KEY, as long as there is an index (key) on it:

CREATE TABLE members ( 
  id int(11)  UNSIGNED NOT NULL AUTO_INCREMENT,
  memberid VARCHAR( 30 ) NOT NULL , 
  `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP , 
  firstname VARCHAR( 50 ) NULL , 
  lastname VARCHAR( 50 ) NULL , 
  PRIMARY KEY (memberid) ,
  KEY (id)                          --- or:    UNIQUE KEY (id)
) ENGINE = MYISAM;

First create table without auto_increment,

CREATE TABLE `members`(
    `id` int(11) NOT NULL,
    `memberid` VARCHAR( 30 ) NOT NULL ,
    `Time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
    `firstname` VARCHAR( 50 ) NULL ,
    `lastname` VARCHAR( 50 ) NULL
    PRIMARY KEY (memberid) 
) ENGINE = MYISAM;

after set id as index,

ALTER TABLE `members` ADD INDEX(`id`);

after set id as auto_increment,

ALTER TABLE `members` CHANGE `id` `id` INT(11) NOT NULL AUTO_INCREMENT;

CREATE TABLE IF NOT EXISTS `members` (
    `id` int(11) NOT NULL,
    `memberid` VARCHAR( 30 ) NOT NULL ,
    `Time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
    `firstname` VARCHAR( 50 ) NULL ,
    `lastname` VARCHAR( 50 ) NULL,
      PRIMARY KEY (`memberid`),
      KEY `id` (`id`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

I think i understand what the reason of your error.
First you click auto AUTO INCREMENT field then select it as a primary key.

The Right way is First You have to select it as a primary key then you
have to click auto AUTO INCREMENT field.

Very easy.
Thanks

Identified this solution while reading this thread. Figured id post this for the next guy possibly.

When dealing with Laravel migration file from a package, I Ran into this issue.

My old value was

$table->increments('id');

My new

$table->integer('id')->autoIncrement();

Источник