[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2007-11-23
7
Medium Priority
?
374 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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 2000 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

649 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