Jump to content

Archived

This topic is now archived and is closed to further replies.

erron

set primary key in existing table and field

Recommended Posts

hi, no matter how much i google search this, i cant find the answer.

how do i set the primary key and auto_increment on an already existing field in a table.

For the history, im copying 35 tables in a database with different table names using:

$table = "CREATE TABLE IF NOT EXISTS ".$username."_banner SELECT * FROM default_banner";
mysql_query($table);

but this does not copy over the primary key and auto_increment settings.

Share this post


Link to post
Share on other sites
Below are examples for specifying a primary key by altering a table:

MySQL:
ALTER TABLE Customer ADD PRIMARY KEY (SID);

Oracle:
ALTER TABLE Customer ADD PRIMARY KEY (SID);

SQL Server:
ALTER TABLE Customer ADD PRIMARY KEY (SID);

Note: Before using the ALTER TABLE command to add a primary key, you'll need to make sure that the field is defined as 'NOT NULL' -- in other words, NULL cannot be an accepted value for that field.

(taken from http://www.1keydata.com/sql/sql-primary-key.html)

Share this post


Link to post
Share on other sites
Thankyou for that,

after a lot experimenting and questions, the following works a charm:

$fix = "alter table `".$username."_core_acl_aro` change `aro_id` `aro_id` int (11) NOT NULL AUTO_INCREMENT , add primary key (`aro_id`)";
mysql_query($fix);

Thanks again.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.