1

I have several tables in my schema that I'm having a problem truncating. My structure is:

country
->PK countryId
->name

city
->PK cityId
->FK countryId
->name

address
->PK addressId
->FK cityId

customer
->PK customerId
->FK addressId

appointment
->PK appointmentId
->FK customerId

Now I am not the one who made this table, it was supplied to me, nor am I any sort of expert in SQL, hardly a beginner actually, but I need to be able to truncate the data as it's for a test application and I have simulated data that I no longer need.

This is the error I am getting:

TRUNCATE TABLE country  Error Code: 1701. Cannot truncate a table referenced in a foreign key constraint (`U05FKQ`.`city`, CONSTRAINT `city_ibfk_1` FOREIGN KEY (`countryId`) REFERENCES `U05FKQ`.`country` (`countryId`))  0.547 sec

I have also attempted to run this:

TRUNCATE TABLE appointment;
TRUNCATE TABLE customer;
TRUNCATE TABLE address;
TRUNCATE TABLE city;
TRUNCATE TABLE country;

Which succeeds on the appointment truncation but fails on customer, and doesn't continue.

Another large issue I have noticed while doing single deletions on data from my application is that the auto incremented id's do not adjust. Nor does deleting an entire table full of data, IE if I delete a full table of 3 entries even though I only have a single entry in the table after addition it still shows me 4 for the ID, is there a fix for this?

I don't have access to the original DB creation script.

Joe
  • 269
  • 3
  • 13
  • 1
    Delete does not adjust Auto increment counter; Truncate resets the counter to 1. – Madhur Bhaiya Nov 30 '18 at 10:02
  • Possible duplicate of [MySQL: Truncate Table vs Delete From Table](https://stackoverflow.com/questions/27184221/mysql-truncate-table-vs-delete-from-table) – Madhur Bhaiya Nov 30 '18 at 10:04
  • I appreciate the help, I've been extremely lost in this project overall trying to grasp pretty much everything. This was actually the first time I have ever picked up SQL, or Java so it's all fairly new to me. – Joe Nov 30 '18 at 10:05
  • Also, you need to truncate those tables first which dont have any child key, referencing to some other table. Then move down one level, truncate and so on... – Madhur Bhaiya Nov 30 '18 at 10:05
  • @MadhurBhaiya that's what I'm doing in the truncation SQL I posted is it not? – Joe Nov 30 '18 at 10:06
  • Please do `SHOW CREATE TABLE ` for all the tables and post their results to the question. That will help in determining correct order for truncation. – Madhur Bhaiya Nov 30 '18 at 10:07

1 Answers1

2

A very data unsafe solution is:

SET FOREIGN_KEY_CHECKS=0;

Then, set it back up with:

SET FOREIGN_KEY_CHECKS=1;
Oscar Azpeitia
  • 176
  • 7
  • As long as I reset the foreign key checks flag there will I be ok? Like I said I just need to preserve the table structure/declarations I don't need the data. – Joe Nov 30 '18 at 10:07