Menu
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?:)
472
To add a new AUTO_INCREMENT integer column named c : ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (c); We indexed c (as a PRIMARY KEY ) because AUTO_INCREMENT columns must be indexed, and we declare c as NOT NULL because primary key columns cannot be NULL .
The MS SQL Server uses the IDENTITY keyword to perform an auto-increment feature. In the example above, the starting value for IDENTITY is 1, and it will increment by 1 for each new record. Tip: To specify that the "Personid" column should start at value 10 and increment by 5, change it to IDENTITY(10,5) .
You can double click the name of the column or click on the 'Properties' button. Column Properties dialog box appears. Select the General Tab (Default Selection for the first time). Then select both the 'Auto Increment' and 'Identity Column' check boxes.
Try this
ALTER TABLE `users` ADD `id` INT NOT NULL AUTO_INCREMENT;
for an existing primary key
131
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.
21
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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
