Menu
If you're looking to add auto increment to an existing table by changing an existing int column to IDENTITY , SQL Server will fight you. You'll have to either: Add a new column all together with new your auto-incremented primary key, or. Drop your old int column and then add a new IDENTITY right after.
Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table. Often this is the primary key field that we would like to be created automatically every time a new record is inserted.
There can be only one AUTO_INCREMENT column per table, it must be indexed, and it cannot have a DEFAULT value. So you can indeed have an AUTO_INCREMENT column in a table that is not the primary key.
To change the value of the AUTO_INCREMENT the table must be using the MyISAM engine. So go to the table properties, change the engine from say InnoDB to MyISAM, then change the AUTO_INCREMENT value to whatever should be next in your sequence.
ALTER TABLE document MODIFY COLUMN document_id INT auto_increment
Roman is right, but note that the auto_increment column must be part of the PRIMARY KEY or a UNIQUE KEY (and in almost 100% of the cases, it should be the only column that makes up the PRIMARY KEY):
ALTER TABLE document MODIFY document_id INT AUTO_INCREMENT PRIMARY KEY
In my case it only worked when I put not null. I think this is a constraint.
ALTER TABLE document MODIFY COLUMN document_id INT NOT NULL AUTO_INCREMENT;
The SQL to do this would be:
ALTER TABLE `document` MODIFY COLUMN `document_id` INT AUTO_INCREMENT;
There are a couple of reasons that your SQL might not work. First, you must re-specify the data type (INT in this case). Also, the column you are trying to alter must be indexed (it does not have to be the primary key, but usually that is what you would want). Furthermore, there can only be one AUTO_INCREMENT column for each table. So, you may wish to run the following SQL (if your column is not indexed):
ALTER TABLE `document` MODIFY `document_id` INT AUTO_INCREMENT PRIMARY KEY;
You can find more information in the MySQL documentation: http://dev.mysql.com/doc/refman/5.1/en/alter-table.html for the modify column syntax and http://dev.mysql.com/doc/refman/5.1/en/create-table.html for more information about specifying columns.
AUTO_INCREMENT is part of the column's datatype, you have to define the complete datatype for the column again:
ALTER TABLE document
ALTER COLUMN document_id int AUTO_INCREMENT
(int taken as an example, you should set it to the type the column had before)
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