Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 378
  • Last Modified:

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

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
santocki
Asked:
santocki
  • 3
  • 3
1 Solution
 
houssam_balloutCommented:
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
 
santockiAuthor Commented:
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
 
santockiAuthor Commented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
steelseth12Commented:
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
 
santockiAuthor Commented:
The script they are using is MyISAM table  without views or stored procedure.
0
 
steelseth12Commented:
It can not be done using a single sql statement
0
 
steelseth12Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now