I have a pre-existing table, containing 'fname', 'lname', 'email', 'password' and 'ip'. But now I want an auto-increment column. However, when I enter:
ALTER TABLE users
ADD id int NOT NULL AUTO_INCREMENT
I get the following:
#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key
Any advice?:)
Try this
ALTER TABLE `users` ADD `id` INT NOT NULL AUTO_INCREMENT;
for an existing primary key
FIRST to the query to make it the first in this table. –
Grolier ALTER TABLE users DROP PRIMARY KEY, ADD id INT NOT NULL AUTO_INCREMENT PRIMARY KEY –
Guiltless FIRST e.g ALTER TABLE tbl1 ADD id INT PRIMARY KEY AUTO_INCREMENT FIRST –
Locution users ADD id INT NOT NULL AUTO_INCREMENT , ADD PRIMARY KEY (id); –
Marden If you don't care whether the auto-id is used as PRIMARY KEY, you can just do
ALTER TABLE `myTable` ADD COLUMN `id` INT AUTO_INCREMENT UNIQUE FIRST;
I just did this and it worked a treat.
FIRST do there? –
Marginalia If you want to add AUTO_INCREMENT in an existing table, need to run following SQL command:
ALTER TABLE users ADD id int NOT NULL AUTO_INCREMENT primary key
Multiple primary key defined because you already have a primary key on the table. A table can only have one primary key. Retry the above command on the table when it doesn't have a primary key –
Mistranslate ALTER TABLE users DROP PRIMARY KEY, ADD id INT NOT NULL AUTO_INCREMENT PRIMARY KEY –
Guiltless First you have to remove the primary key of the table
ALTER TABLE nametable DROP PRIMARY KEY
and now yo can add the autoincrement ...
ALTER TABLE nametable ADD id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
Duplicate column name 'id' –
Hafnium Well, you must first drop the auto_increment and primary key you have and then add yours, as follows:
-- drop auto_increment capability
alter table `users` modify column id INT NOT NULL;
-- in one line, drop primary key and rebuild one
alter table `users` drop primary key, add primary key(id);
-- re add the auto_increment capability, last value is remembered
alter table `users` modify column id INT NOT NULL AUTO_INCREMENT;
id INT AUTO_INCREMENT UNIQUE FIRST; Query OK, 2 rows affected (0.27 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> desc domains; +--------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | UNI | NULL | auto_increment | | domain | varchar(128) | NO | PRI | NULL | | +--------+--------------+------+-----+---------+----------------+ –
Lysippus If you run the following command :
ALTER TABLE users ADD id int NOT NULL AUTO_INCREMENT PRIMARY KEY;
This will show you the error :
ERROR 1060 (42S21): Duplicate column name 'id'
This is because this command will try to add the new column named id to the existing table.
To modify the existing column you have to use the following command :
ALTER TABLE users MODIFY id int NOT NULL AUTO_INCREMENT PRIMARY KEY;
This should work for changing the existing column constraint....!
Delete the primary key of a table if it exists:
ALTER TABLE `tableName` DROP PRIMARY KEY;
Adding an auto-increment column to a table :
ALTER TABLE `tableName` ADD `Column_name` INT PRIMARY KEY AUTO_INCREMENT;
Modify the column which we want to consider as the primary key:
alter table `tableName` modify column `Column_name` INT NOT NULL AUTO_INCREMENT PRIMARY KEY;
Drop the primary index from the table:
ALTER TABLE `tableName` DROP INDEX `PRIMARY`;
Then add the id column (without a primary index). I have used a big int because I am going to have lots of data but INT(11) should work just as well:
ALTER TABLE `tableName` ADD COLUMN `id` BIGINT(11) NOT NULL FIRST;
Then modify the column with auto-increment (thanks php). It needs to be a primary key:
ALTER TABLE `tableName ` MODIFY COLUMN `id` BIGINT(11) UNSIGNED PRIMARY KEY AUTO_INCREMENT;
I have just tried this on a table of mine and it appears to have worked.
ALTER TABLE PremiumPackages ADD COLUMN id MEDIUMINT(8) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT FIRST; –
Doctrinaire Just change the ADD to MODIFY and it will works !
Replace
ALTER TABLE users ADD id int NOT NULL AUTO_INCREMENT
To
ALTER TABLE users MODIFY id int NOT NULL AUTO_INCREMENT;
ALTER TABLE users CHANGE id int( 30 ) NOT NULL AUTO_INCREMENT
the integer parameter is based on my default sql setting have a nice day
ALTER TABLE users ADD id int NOT NULL AUTO_INCREMENT primary key FIRST
If you want to add an id with a primary key and identity:
ALTER TABLE user ADD id INT NOT NULL AUTO_INCREMENT FIRST , ADD PRIMARY KEY (id);
For PostgreSQL you have to use SERIAL instead of auto_increment.
ALTER TABLE your_table_name ADD COLUMN id SERIAL NOT NULL PRIMARY KEY
This SQL request works for me :
ALTER TABLE users
CHANGE COLUMN `id` `id` INT(11) NOT NULL AUTO_INCREMENT ;
ALTER TABLE `table` ADD `id` INT NOT NULL AUTO_INCREMENT unique
Try this. No need to drop your primary key.
ALTER TABLE yourDbName.YourTableName
CHANGE COLUMN ID ID INT NOT NULL AUTO_INCREMENT ,
ADD PRIMARY KEY (ID),
ADD UNIQUE INDEX ID_UNIQUE (ID ASC) VISIBLE;
;
Check for already existing primary key with different column. If yes, drop the primary key using:
ALTER TABLE Table1
DROP CONSTRAINT PK_Table1_Col1
GO
and then write your query as it is.
Proceed like that :
Make a dump of your database first
Remove the primary key like that
ALTER TABLE yourtable DROP PRIMARY KEY
Add the new column like that
ALTER TABLE yourtable add column Id INT NOT NULL AUTO_INCREMENT FIRST, ADD primary KEY Id(Id)
The table will be looked and the AutoInc updated.
© 2022 - 2024 — McMap. All rights reserved.
describe users– Octad