mysql: how to change column to be PK Auto_Increment

Question

is there a single select statement that will do all three of those: primary key, unique, and auto_increment?

Answer 1

Primary key is always unique.

Answer 2

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.

Answer 3

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.