Jump to content


Photo

set primary key in existing table and field


  • Please log in to reply
3 replies to this topic

#1 erron

erron
  • New Members
  • Pip
  • Newbie
  • 9 posts

Posted 17 July 2006 - 12:22 AM

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.

#2 realjumper

realjumper
  • Members
  • PipPipPip
  • Advanced Member
  • 399 posts

Posted 17 July 2006 - 02:32 AM

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....rimary-key.html)

#3 erron

erron
  • New Members
  • Pip
  • Newbie
  • 9 posts

Posted 17 July 2006 - 04:45 AM

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.

#4 realjumper

realjumper
  • Members
  • PipPipPip
  • Advanced Member
  • 399 posts

Posted 17 July 2006 - 04:51 AM

No worries....googled it in 1 minute!!!




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users