I was updating a website some minutes back, and then, i proceeded to overwrite the database.
So on my local server, i exported all my tables and used the "drop if exists option".

And the phpmyadmin on the remote server complained of foreign key constraint. I was pissed off
naturally, so i decided to drop all the databases on the webserver, out of over 200 tables, 193
tables were deleted leaving only 7 tables.

Reason: foregn key constraint again. I was not in the mood to investigate primary and foreign keys,
so i took the easy way out =>

Lets assume, i have the tables in the following order:

table 1

table 2:

table 4

table 5

table 6

table 7


In what order will you drop the tables without getting the foreign key constraint error and why will you use that order?

I'll be back. I forgot my thinking cap in church.

afam4eva:

I'll be back. I forgot my thinking cap in church.

Hehn? oya go back and bring am now now!

Vague question. This is like asking, "I have 7 children and they all did something bad. In what order do I spank them?"

1) Where is the database schema?

2) There is a mysql query to run, an extra condition, that would cause the drop to ignore the constraints but was the "other query" that would ensure this other query running without error, ran first?

3) What is the relationship b/w these 7 tables (this is probably related to #1 above)?

Sorry if it appears as if I think too much,or did I miss anything obvious? I don't just jump to answer questions, I need to know more

All your questions were professionally asked, and i am happy that you did.

1. Ehm, the database schema is ehm a standard livezilla.com database installation.
The tables were created by livezilla.

2. pues, i dropped the tables using phpmyadmin without any extra coding

3. The relationship between the tables is not known.

I tried to do a multiple drop for all the 7 tables. And i know that phpmyadmin
will drop them starting from table 1, table 2 to table 7 and not the reverse.

All the tables can be dropped one after the other without getting the constraint bug.
The order of dropping can be done in one of the following ways:
a. By selection at random
b. By dropping table 1 first, followed by table 2 till 7
c. By starting from the middle, take a mean and start deleting in any direction
d. Reversal of method b
e. delete two tables at a time


Based on all i have said, and remembering the rules of foreign key constraint, which
method do you think will be best? and why?

I don't remember either and I have never done it but I have a MySQL book and I remember reading it on there 4 years or so ago.

If you create a website like this:

departments
fields: department_id, department name. . .

and you set the department_id as primary key which we do all the time

then, you create something like employees table
and create the following field:
department_id, employee name. . . .

Now, you tie this department_id field in the employees table with the department_id field in departments table.
Since the department_id really belongs to departments table, it is called a foreign key in any other table.

Using the foreign constraint,
before you can say insert into employees(department_id) values(1)

It will first check if a department_id of value 1 exists in the foreign table (departments table)


There is another aspect of the foreign constraint, if you try to drop departments table without first deleting the
employees that reference it, then you will get a foreign key constraint error.


Based on all these, when i tried deleting the tables together, it gave me foreign constraint, so i just reversed the
order - and started dropping the tables from the last one to the first one.

The idea is that, you need to drop the table referencing a foreign key before dropping the parent table. I think
the key is to drop the tables from extremes.

This might look like fluke, but, if you have worked with these foreign keys before, you will know that you must
create the parent tables first before the child table that references it.


The truth is that, i have never used foreign key constraint before in mysql, i dont have that kinda time, but i used
to be an oracle 10G tutor just last year, and always teach and demonstrate these things in class. Interestingly,
some nairaland members too happened to be in that class. Hola mis estudiantes!

Since you need to create the table you are referencing first, so it follows common sense to drop them in the reverse
order. That is the general rule for dropping tables tied with the foreign key.

If the tables were tied using the inodb engine, i think the tables should drop based on their relationship, but this one
i am not so sure of - i only used innobdb once in a real-life project.

Okay but this isn't what I had in mind sha. There is mysql syntax that you could have run when you set up the tables initially, something that would boil down to this in english, "this is the primary key and this is the secondary key and when you want to drop table with primary key, delete all tables with secondary key as well since they are related".

Again, this might not fit into your situation but I remember reading this 3 or so years ago.

It might fit into it if it were tried. the key thing is just that, whether you are dumping or creating the tables one by one,
the table with the primary keys must be created first before the keys with constraint,
so if you want to drop and not be "constrained", it is better to start in the reverse order of table creation.

Why drop if exists? If you're working on an application and need to merge changes in table structure, you can always keep a list of your 'ALTER TABLE' statements.

Zahymaka:

Why drop if exists? If you're working on an application and need to merge changes in table structure, you can always keep a list of your 'ALTER TABLE' statements.

if you read all the jargons i posted, i mentioned that the tables were created by another application, and i was not aware of the constraints until i needed to overrite the database on the live server.