Solved

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

Posted on 2007-11-23
7
370 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

680 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