According to http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html:
Note: There can be only one AUTO_INCREMENT column per table, it must be
indexed, and it cannot have a DEFAULT value.
It did not specify that the AUTO_INCREMENT column couldn't have a regular
index on it.
If you have this table:
CREATE TABLE users (
ID int auto_increment not null,
LOGIN varchar(8) not null,
.... other fields ...
Primary Key (Id)
)
And you wanted to make LOGIN your PK, just do this:
ALTER TABLE users Drop primary key, add key(ID), add primary key(LOGIN);
To create an auto_increment column on a new table that isn't a PK, just
index it.
CREATE TABLE secondsample (
id2 int auto_increment not null,
PKField1 varchar(6) not null,
PKField2 varchar(24) not null,
PKField3 datetime not null,
... other fields ...
PRIMARY KEY(PKField1, PKField2, PKField3),
KEY(id2)
)
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Seena Blace <[EMAIL PROTECTED]> wrote on 09/16/2004 12:30:29 AM:
>
> Hi,
>
> Is there any way AUTO_INCREMENT column cann't me Primary key ?
>
> One table has one column auto_increment which I want to convert into
> non primary key but I also want to maintain the column.How to do that?
thanks
>
>
>
> ---------------------------------
> Do you Yahoo!?
> vote.yahoo.com - Register online to vote today!
