cant delete table

Hi,

I cant delete a table and I have tried everything.
I have  a table called users with a field called role_id which wont delete. Then I have a table called roles which wont delete.

For the users table I do this and #1091 - Can't DROP 'role_id'; check that column/key exists
ALTER TABLE users DROP FOREIGN KEY role_id

if I drop table roles I get this
1217 - Cannot delete or update a parent row: a foreign key constraint fails
jagguyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
This "error" is telling you that you have rows that are linked ( the foreign key constraint is indicated in the message )
You need to update some rows first so that the relevant links are no longer present before you can delete the rows...
0
jagguyAuthor Commented:
I dont have any rows linked with data , it isnt that straightforward.
 
I have no FK data and where can i find any rule about constraint in phpmyadmin.
In fact the FK values are  NULL and I cant drop this field .

I dont get this!
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Do you want to delete just  the rows or drop the table?
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
And can you run
ALTER TABLE users DROP FOREIGN KEY role_id
0
jagguyAuthor Commented:
I want to drop the table which I cant.

On the users table with fk I cant do this as I said before.
mysql is tricky and this filed exists.

ALTER TABLE users DROP FOREIGN KEY role_id
#1091 - Can't DROP 'role_id'; check that column/key exists
0
Ray PaseurCommented:
Not sure if this will work, but it's easy enough to try.  You might try TRUNCATE table to remove as much of the data as can be removed, then look to see what is left.  The remainder will probably be the subset of rows that are somehow linked elsewhere.
0
jagguyAuthor Commented:
The Roles  table has NO data. Nothing! mysql just doesnt want to delete it. is there somewhere where rules are set up because I have no idea what to do?

I drop the table and get this message below. The fk has been changed in another table . is this a bug in mysql?

 #1217 - Cannot delete or update a parent row: a foreign key constraint fails
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I think that's the point where I would try to REPAIR the tables.... could you rename the table?
0
jagguyAuthor Commented:
you can rename but yet again you cant delete the table because of FK constraints.
How can i get a list of these constraints ? Can i delete the rule somehow?

#1271 error on deleting the table
0
jagguyAuthor Commented:
ok this gives me the users table as I expect but i cant delete the fk there so i am stuck again.

SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE (REFERENCED_TABLE_SCHEMA, REFERENCED_TABLE_NAME) = ('aptutori_aptut', 'blah')
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
You need to do this:
SET FOREIGN_KEY_CHECKS=0
Drop your tables
SET FOREIGN_KEY_CHECKS=1
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

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.