Solved

Check existence of key before adding it using alter table to avoid error messages?

Posted on 2007-11-23
7
367 Views
Last Modified: 2008-02-01
When I execute the MySQL command

ALTER TABLE `forum_reply`
     MODIFY `type` int(11),
    ADD KEY `fid` (`fid`);

it generates the error:

Error Code : 1061
Duplicate key name 'fid'

which is obvious because the same key 'fid' already exist. Is there a way to check the existence of a key before executing the ADD KEY `fid` (`fid`) command? Because some of my clients does have key created and some don't depending on the version of a script they are using and this error is really bugging me because of the overwhelming support requests I get.

Or is there a way to just ignore and force creating of a key by overwriting an exist key? Either way will solve the problem for me. Thanks.
0
Comment
Question by:santocki
  • 3
  • 3
7 Comments
 
LVL 17

Expert Comment

by:houssam_ballout
ID: 20341102
create a query, and select fid
select fid from table where fid = '$fid'
execute the query,
then use the following
$resultNumber =  mysql_num_rows($queryResult)

This wil display the number of existence of fid in the table.

then make an if statement on it

if($resultNumber == 0)
/// The there is no field in the db so you can use ur code
ALTER TABLE `forum_reply`
     MODIFY `type` int(11),
    ADD KEY `fid` (`fid`);

else
{
//This means that there is ID
so just edit, the field called type
update table 'forum_reply' set type = .... where fid = '$fid'

}

Be sure about the variables you are using?

0
 

Author Comment

by:santocki
ID: 20341399
Thanks for the solution, but I am not trying to use PHP, I just wanted to know if there is a way to use pure MySQL commands, because I think I seen somewhere that somebody used the IF() command within the SQL to make some kind of decision because executing a command.
0
 

Author Comment

by:santocki
ID: 20341403
The reason I need pure SQL command is because I give the sql extension file for my clients to execute on their MySQL client.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 20

Expert Comment

by:steelseth12
ID: 20341541
You could check if the key existed by checking the nformation_schema.statistics table but you can not use conditional statements with alter table syntax.

The only way that this could be done is using a stored procedure.

Is this acceptable ?
0
 

Author Comment

by:santocki
ID: 20341692
The script they are using is MyISAM table  without views or stored procedure.
0
 
LVL 20

Expert Comment

by:steelseth12
ID: 20343249
It can not be done using a single sql statement
0
 
LVL 20

Accepted Solution

by:
steelseth12 earned 500 total points
ID: 20343261
You could use

ALTER TABLE `forum_reply`
     MODIFY `type` int(11),
    ADD KEY  (`fid`);

But if KEY exist it will add another KEY fid_2 , fid_3 etc
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now