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)?
Доброго дня,
Читаю учебник по MySQL и при выполнении примеров из него, у меня не получается создать новую таблицу с AUTO_INCREMENT’ом. Код:
CREATE TABLE album (
artist_id SMALLINT(5) NOT NULL,
album_id SMALLINT(4) NOT NULL AUTO_INCREMENT,
album_name CHAR(128) DEFAULT NULL,
PRIMARY KEY (artist_id, album_id)
);Ошибка:
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a keyЯ понимаю суть того на что оно ругается, но не понимаю — почему? У меня же указан единственный столбец с AUTO_INCREMENT и он является частью индекса. В книге идентичный пример работает почему-то. Если сделать PRIMARY KEY только из album_id — таблица создается.
-
Вопрос заданболее трёх лет назад
-
3084 просмотра
В книге идентичный пример работает почему-то
Фокусы руками, найдите различие:
mysql> CREATE TABLE album ( artist_id SMALLINT(5) NOT NULL, album_id SMALLINT(4) NOT NULL AUTO_INCREMENT, album_name CHAR(128) DEFAULT NULL, PRIMARY KEY (artist_id, album_id) ) engine=innodb;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql> CREATE TABLE album ( artist_id SMALLINT(5) NOT NULL, album_id SMALLINT(4) NOT NULL AUTO_INCREMENT, album_name CHAR(128) DEFAULT NULL, PRIMARY KEY (artist_id, album_id) ) engine=myisam;
Query OK, 0 rows affected (0,00 sec)
mysql>У вас с книгой разные дефолтные движки, в будущем от этого могут ещё сюрпризы возникать. Очень разные myisam и innodb по поведению, возможностям и ограничениям.
Пригласить эксперта
-
Показать ещё
Загружается…
03 июн. 2023, в 18:21
1500 руб./за проект
03 июн. 2023, в 16:39
2000 руб./за проект
03 июн. 2023, в 16:28
1800 руб./за проект
Минуточку внимания
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
7
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
1
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,0922 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
To fix this error, you need to add PRIMARY KEY to auto_increment field. Let us now see how this error occurs −
Here, we are creating a table and it gives the same error −
mysql> create table DemoTable ( StudentId int NOT NULL AUTO_INCREMENT, StudentName varchar(40), StudentAge int ); ERROR 1075 (42000) : Incorrect table definition; there can be only one auto column and it must be defined as a key
To solve the above error, you need to add PRIMARY KEY with AUTO_INCREMENT. Let us first create a table −
mysql> create table DemoTable ( StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, StudentName varchar(40), StudentAge int ); Query OK, 0 rows affected (1.01 sec)
Insert some records in the table using insert command −
mysql> insert into DemoTable(StudentName,StudentAge) values('Chris Brown',19);
Query OK, 1 row affected (0.30 sec)
mysql> insert into DemoTable(StudentName,StudentAge) values('David Miller',18);
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable(StudentName,StudentAge) values('John Doe',20);
Query OK, 1 row affected (0.11 sec)
Display all records from the table using select statement :
mysql> select *from DemoTable;
This will produce the following output −
+-----------+--------------+------------+ | StudentId | StudentName | StudentAge | +-----------+--------------+------------+ | 1 | Chris Brown | 19 | | 2 | David Miller | 18 | | 3 | John Doe | 20 | +-----------+--------------+------------+ 3 rows in set (0.00 sec)