Solved

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

Posted on 2007-11-23
7
366 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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.

Join & Write a Comment

Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

759 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

19 Experts available now in Live!

Get 1:1 Help Now