[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

Dropping Foreign keys that don't exist ERROR 1025/1064

I have an install routine for a php script that basically just imports a mysql file line by line. The file just contains CREATE, DROP and ALTER statements.

My tables are InnoDB and contain foreign keys. My first time through installing, everything works fine, but if someone wants to reinstall the database. I want to be able to clear everything out without having to drop the database.

When I drop the tables and referential integrity is compromised, I get an error and the install aborts. To get around this. I am dropping all foreign keys with a statement such as this: ALTER TABLE arpEntries DROP FOREIGN KEY `arpEntries_ibfk_1`;

This works fine for a reinstall but fails if there isn't anything to drop. Is there a way that I can only execute this statement if the foreign key exists? Or can I force the table to drop?

-ryan
0
dignified
Asked:
dignified
  • 7
  • 3
  • 3
2 Solutions
 
Mehul ShahIT consultantCommented:
Have you tried using Cascade constraints while dropping the tables
0
 
Mehul ShahIT consultantCommented:
Try the below query replacing student with your table name.

drop table student cascade constraints;
0
 
ellandrdCommented:
MehulS78 is right use:

drop table <YOUR_TABLE_NAME> cascade constraints;

Ellandrd
0
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
dignifiedAuthor Commented:
DROP TABLE IF EXISTS `archive_control` CASCADE CONSTRAINTS;

Doing this and I get this error:
ERROR 1064 at line 24: You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONSTRAINTS' at line 1
0
 
ellandrdCommented:
no just use:

drop table archive_control cascade constraints;

if it doesnt exist, it would get dropped, so nothing will happen...

Ellandrd

p.s what version of mysql do you use?
0
 
dignifiedAuthor Commented:
still get the same error...

Using this version:
mysql  Ver 14.5 Distrib 4.1.2-alpha, for pc-linux (i686)

0
 
dignifiedAuthor Commented:
very annoying... maybe I should just drop all the children tables first.
0
 
ellandrdCommented:
any luck now? did you drop the children tables first?
0
 
dignifiedAuthor Commented:
no, my tables have a sort of circular logic. Could perhaps try to drop the foreign keys  one by one with ALTER statements
0
 
Mehul ShahIT consultantCommented:
Its not better to have circular logic as it will always be a problem while inserting or deleting data. Please see if you can avoid the circular relationship.

There is one more way, you can disable the constraints if you want to delete or insert the data. But if you want to drop the tables than you will have to drop the foreign keys one by one.
0
 
dignifiedAuthor Commented:
How do I disable the constraints?
0
 
dignifiedAuthor Commented:
Well according to the mysql site you are supposed to just be able to drop a mysql innodb table regardless of constraints. In mysql 3.x it lets you do this apparently, but in 4.x you must go

SET FOREIGN_KEY_CHECKS = 0;

before using DROP TABLE. Afterwards I used:

SET FOREIGN_KEY_CHECKS = 1;

see user comments here:
http://dev.mysql.com/doc/mysql/en/innodb-foreign-key-constraints.html
0
 
dignifiedAuthor Commented:
Thank you for all who tried to help. In the end, the last comment of mine is the answer I found to my problem.
0

Featured Post

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.

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