TRUNCATE woes

I am getting an error when I

SET foreign_key_checks = 0;

DELETE FROM blade_server_types WHERE blade_server_type_id = 454;
ALTER TABLE blade_server_types AUTO_INCREMENT = 454;

DELETE FROM chassis_types WHERE chassis_type_id = 885;
ALTER TABLE chassis_types AUTO_INCREMENT = 885;

DELETE FROM kvm_types WHERE kvm_type_id = 321;
ALTER TABLE kvm_types AUTO_INCREMENT = 321;

DELETE FROM monitor_types WHERE monitor_type_id = 303;
ALTER TABLE monitor_types AUTO_INCREMENT = 303;

DELETE FROM network_card_types WHERE network_card_type_id = 252;
ALTER TABLE network_card_types AUTO_INCREMENT = 252;

DELETE FROM network_panel_types WHERE network_panel_type_id = 209;
ALTER TABLE network_panel_types AUTO_INCREMENT = 209;

DELETE FROM network_module_types WHERE network_module_type_id = 2158;
ALTER TABLE network_module_types AUTO_INCREMENT = 2158;

DELETE FROM network_standard_types WHERE network_standard_type_id = 5596;
ALTER TABLE network_standard_types AUTO_INCREMENT = 5596;

DELETE FROM periphial_storage_types WHERE periphial_storage_type_id = 2401;
ALTER TABLE periphial_storage_types AUTO_INCREMENT = 2401;

DELETE FROM server_types WHERE server_type_id = 3548;
ALTER TABLE server_types AUTO_INCREMENT = 3548;

TRUNCATE TABLE countries;
ALTER TABLE countries AUTO_INCREMENT = 1;

TRUNCATE TABLE states;
ALTER TABLE states AUTO_INCREMENT = 1;
...

Why am I getting that error if I turned that relationship off?

SET foreign_key_checks = 0;

got it

1 Like

Please share your solution for the benefit of others with a similar problem.

1 Like

i am curious why you are messing with auto_increment numbers

the whole purpose of auto_increment numbers is to provide uniqueness – not gapless or consecutive numbers

whatever you think you are achieving by doing this, it’s very likely futile

i would really like to know what’s going on

3 Likes

Sur, I’m really not sure why it did check the foreign key constraint,
anyway, it worked when I did

SET FOREIGN_KEY_CHECKS  = 0;

TRUNCATE TABLE blade_servers;
ALTER TABLE blade_servers AUTO_INCREMENT = 1;
DELETE FROM blade_server_types WHERE blade_server_type_id = 454;
ALTER TABLE blade_server_types AUTO_INCREMENT = 454;

TRUNCATE TABLE chassises;
ALTER TABLE chassises AUTO_INCREMENT = 1;
DELETE FROM chassis_types WHERE chassis_type_id = 885;
ALTER TABLE chassis_types AUTO_INCREMENT = 885;

TRUNCATE TABLE kvms;
ALTER TABLE kvms AUTO_INCREMENT = 1;
DELETE FROM kvm_types WHERE kvm_type_id = 321;
ALTER TABLE kvm_types AUTO_INCREMENT = 321;

TRUNCATE TABLE monitors;
ALTER TABLE monitors AUTO_INCREMENT = 1;
DELETE FROM monitor_types WHERE monitor_type_id = 303;
ALTER TABLE monitor_types AUTO_INCREMENT = 303;

TRUNCATE TABLE network_cards;
ALTER TABLE network_cards AUTO_INCREMENT = 1;
DELETE FROM network_card_types WHERE network_card_type_id = 252;
ALTER TABLE network_card_types AUTO_INCREMENT = 252;

TRUNCATE TABLE network_panels;
ALTER TABLE network_modules AUTO_INCREMENT = 1;
DELETE FROM network_panel_types WHERE network_panel_type_id = 209;
ALTER TABLE network_panel_types AUTO_INCREMENT = 209;

TRUNCATE TABLE network_modules;
ALTER TABLE network_panels AUTO_INCREMENT = 1;
DELETE FROM network_module_types WHERE network_module_type_id = 2158;
ALTER TABLE network_module_types AUTO_INCREMENT = 2158;

TRUNCATE TABLE network_standards;
ALTER TABLE network_standards AUTO_INCREMENT = 1;
DELETE FROM network_standard_types WHERE network_standard_type_id = 5596;
ALTER TABLE network_standard_types AUTO_INCREMENT = 5596;

TRUNCATE TABLE periphial_storages;
ALTER TABLE periphial_storages AUTO_INCREMENT = 1;
DELETE FROM periphial_storage_types WHERE periphial_storage_type_id = 2401;
ALTER TABLE periphial_storage_types AUTO_INCREMENT = 2401;

TRUNCATE TABLE servers;
ALTER TABLE servers AUTO_INCREMENT = 1;
DELETE FROM server_types WHERE server_type_id = 3548;
ALTER TABLE server_types AUTO_INCREMENT = 3548;


TRUNCATE TABLE countries;
ALTER TABLE countries AUTO_INCREMENT = 1;

I guess the table server_types gave me the error as that tables foreign key was the primary of the servers table.
I thought you had to auto_ increment any primary key? Heres a table, is it good?

CREATE TABLE countries (
   country_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
   name VARCHAR(25) NOT NULL,
   created_by VARCHAR(50) DEFAULT 'test@industechnology.com',
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   updated_by VARCHAR(50) NULL,
   updated_date TIMESTAMP NULL,
   operational_status BOOLEAN DEFAULT 1,
   PRIMARY KEY ( country_id )
);

Oh, I think I know abut the gaps…
I have some tables I fill with a 100 records and I just want to ensure the tables auto_increment starts at 101

That much was clear. But why do you want that? What’s the problem with having gaps?

Its just my OCD kicking in.
I like numbers to be sequential

you are wasting your time for no benefit whatsoever, sorry

but i know what you mean about OCD

i have CDO, which is very similar, but the letters are in the right alphabetic order!!

3 Likes

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.