On Wed, 21 Aug 2002, Paul Maine wrote: > The following table uses order_id as the primary key. My problem is that I > need to be able to change order_id so that it is not a primary key. Next, I > want to add a new auto incrementing key. What sql commands should I use to > first back up the table and then to accomplish this modification. This is a > production table so I must be very careful. > > > mysql> describe transaction_response; > +---------------+---------------+------+-----+---------+-------+ > | Field | Type | Null | Key | Default | Extra | > +---------------+---------------+------+-----+---------+-------+ > | order_id | int(11) | | PRI | 0 | | > | result | tinyint(16) | YES | | NULL | | > | pnref | varchar(12) | YES | | NULL | | > | respmsg | varchar(128) | YES | | NULL | | > | authcode | varchar(6) | YES | | NULL | | > | avsaddr | char(1) | YES | | NULL | | > | avszip | char(1) | YES | | NULL | | > | timestamp | timestamp(14) | YES | | NULL | | > | merchant_name | varchar(20) | YES | | NULL | | > +---------------+---------------+------+-----+---------+-------+ Hi Paul,
# Remove the AUTO_INCREMENT characteristic from the order_id column ALTER TABLE transaction_response CHANGE order_id order_id INT(11) NOT NULL; # Remove the primary key ALTER TABLE transaction_response DROP PRIMARY KEY; # Add a new column called "id" at the top and make it primary key ALTER TABLE transaction_response ADD COLUMN id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST; May I strongly suggest that you try this on a phantom table first, before you perform this on a production table? I did perform these successfully on my end; however, I can't stress how important it is that you know for sure it works on your end first. Regards, Neil Mansilla whatUseek.com --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
