Hi,
I understand what the error is, but how do I delete a row anyway?
Basically I want to clear out the entire table but keep its structure, i.e., start over with an empty table, which I understand the truncate function does.
However, my tables are all interrelated and have foreign keys in them, so I'm guessing that's the problem. But how do you start from scratch in such cases like these without recreating the table structures all over again?
Thanks!
If you are doing it from the command-line interface, then you'll need to enter the command you used to create the table in the first place. If you are using phpMyAdmin or SQLYog or some other client tool to interface with the database, they usually have a copy command.
Another alternative is that you could go and delete the records in the table to which the foreign key relates first.
Hello,
What do you mean by entering the command I used to create the table in the first place?
Do you mean that I shoud recreate the table, like overwriting it? i.e. CREATE TABLE roster (user_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(20), last_name VARCHAR(20) NOT NULL);
I was imagining you would create it with a different name...like roster_copy or something.
There is a command to turn off foreign key checks but it varies by DBMS and I don't know what it is.
There should be a way to empty your tables in a particular sequence to avoid the foreign key check problem. like if you want to empty your 'products' table you have to empty your 'shopping_cart' table first.
pufftissue wrote:Hi,
I understand what the error is, but how do I delete a row anyway?
Basically I want to clear out the entire table but keep its structure, i.e., start over with an empty table, which I understand the truncate function does.
However, my tables are all interrelated and have foreign keys in them, so I'm guessing that's the problem. But how do you start from scratch in such cases like these without recreating the table structures all over again?Thanks!
Standard way is "truncate tablename cascade" But I don't know if mysql supports truncate with cascade. also you can try "delete from table cascade"
For cascaded deletes / truncates to work you might need to have defined your foreign keys as "on delete cascade"
IF not, then you'll have to delete the child records first.