mysql: how to change column to be PK Auto_Increment

is there a single select statement that will do all three of those: primary key开发者_JAVA百科, unique, and auto_increment?

Here we create a little table:

mysql> CREATE TABLE test2 (id int);

Note Null is YES, and id is not a primary key, nor does it auto_increment.

mysql> DESCRIBE test2;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       | 
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

Here is the alter command:

mysql> ALTER TABLE test2 MODIFY COLUMN id INT NOT NULL auto_increment, ADD primary key (id);

Now Null is NO, and id is a primary key with auto_increment.

mysql> describe test2;
describe test2;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment | 
+-------+---------+------+-----+---------+----------------+
1 row in set (0.00 sec)

Primary keys are always unique.

Primary key is always unique.

You cannot alter the database structure with DML (data manipulation language) statements like SELECT. You need DDL (data definition language) statements. Table columns can be modified with ALTER TABLE:

http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

The syntax is basically this:

ALTER TABLE `foo`
CHANGE COLUMN `bar` `bar` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (`foo`);

As about the exact changes you want to do, you must know that in relational databases a primary key is a value that serves to uniquely identify each row in a table. It won't serve such purpose if it's not unique or NULL.