• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 309
  • Last Modified:

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
0
jagguy
Asked:
jagguy
  • 5
  • 5
1 Solution
 
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

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