Nagging error

Databases
1

I am trying to reset my mysql database and got

image
image1654Ă—399 26.8 KB

But im dropping each table in the opposite oder im creating them, so how is this possible?

SET foreign_key_checks = 0;

DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS drawing_section_templates;
DROP TABLE IF EXISTS dynamic_blocks;
DROP TABLE IF EXISTS drawings;
DROP TABLE IF EXISTS parts;
DROP TABLE IF EXISTS racks;
DROP TABLE IF EXISTS materials;
DROP TABLE IF EXISTS projects;
DROP TABLE IF EXISTS rooms;
DROP TABLE IF EXISTS buildings;
DROP TABLE IF EXISTS sites;
DROP TABLE IF EXISTS locations;

SET foreign_key_checks = 1;

CREATE TABLE locations (
   location_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
   name VARCHAR(25) NOT NULL,
   x_coord INT UNSIGNED,
   y_coord INT UNSIGNED,
   notes TEXT DEFAULT 'None',
   created_by VARCHAR(50) DEFAULT 'test@industechnology.com',
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   updated_by VARCHAR(50),
   updated_date TIMESTAMP NULL,
   operational_status BOOLEAN DEFAULT 1,
   PRIMARY KEY ( location_id )
);
CREATE TABLE sites (
   site_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
   location_id TINYINT UNSIGNED,
   name VARCHAR(25) NOT NULL,
   notes TEXT DEFAULT 'None',
   created_by VARCHAR(50) DEFAULT 'test@industechnology.com',
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   updated_by VARCHAR(50),
   updated_date TIMESTAMP NULL,
   operational_status BOOLEAN DEFAULT 1,
   FOREIGN KEY ( location_id ) REFERENCES locations ( location_id ),
   PRIMARY KEY ( site_id )
);
CREATE TABLE buildings (
   building_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
   site_id TINYINT UNSIGNED,
   name VARCHAR(25) NOT NULL,
   floors ENUM ('1','2','3','4'),  
   address TEXT NOT NULL,
   notes TEXT DEFAULT 'None',
   created_by VARCHAR(50) DEFAULT 'test@industechnology.com',
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   updated_by VARCHAR(50),
   updated_date TIMESTAMP NULL,
   operational_status BOOLEAN DEFAULT 1,
   FOREIGN KEY ( site_id ) REFERENCES sites ( site_id ),
   PRIMARY KEY ( building_id )
);
CREATE TABLE rooms (
   room_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   building_id TINYINT UNSIGNED,
   name VARCHAR(25) NOT NULL,
   notes TEXT DEFAULT 'None',
   created_by VARCHAR(50) DEFAULT 'test@industechnology.com',
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   updated_by VARCHAR(50),
   updated_date TIMESTAMP NULL,
   operational_status BOOLEAN DEFAULT 1,
   FOREIGN KEY ( building_id ) REFERENCES buildings (building_id),
   PRIMARY KEY ( room_id )
);
create table projects (
   project_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
   site_id TINYINT UNSIGNED,
   name VARCHAR(50) NOT NULL,   
   notes TEXT DEFAULT 'None',
   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,
   FOREIGN KEY (site_id ) REFERENCES sites( site_id ),
   PRIMARY KEY ( project_id )
);
CREATE TABLE materials (
   material_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   name VARCHAR(50),
   type VARCHAR(50),
   height TINYINT UNSIGNED,
   ports TINYINT UNSIGNED DEFAULT 0,
   manufacturer VARCHAR(50),
   model VARCHAR(50),
   notes TEXT DEFAULT 'None',
   created_by VARCHAR(50) DEFAULT 'test@industechnology.com',
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   updated_by VARCHAR(50),
   updated_date TIMESTAMP NULL,
   operational_status BOOLEAN DEFAULT 1,
   PRIMARY KEY ( material_id )
);
CREATE TABLE racks (
   rack_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
   material_id INT UNSIGNED,
   room_id SMALLINT UNSIGNED,
   name VARCHAR(50),
   ru_height TINYINT UNSIGNED,
   notes TEXT DEFAULT 'None',
   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,
   FOREIGN KEY (material_id) REFERENCES materials (material_id),
   FOREIGN KEY (room_id) REFERENCES rooms (room_id),
   PRIMARY KEY ( rack_id )
);
CREATE TABLE parts (
   part_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   material_id INT UNSIGNED,
   rack_id TINYINT UNSIGNED,
   beginning_ru TINYINT UNSIGNED,
   name VARCHAR(50),
   notes TEXT DEFAULT 'None',
   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,
   FOREIGN KEY (material_id) REFERENCES materials (material_id),
   FOREIGN KEY (rack_id) REFERENCES racks (rack_id),
   PRIMARY KEY ( part_id )
);
CREATE TABLE data_connections (
   data_connection_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
   to_part_id INT UNSIGNED,
   from_part_id INT UNSIGNED,
   name VARCHAR(50),
   notes TEXT DEFAULT 'None',
   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,
   FOREIGN KEY (to_part_id) REFERENCES parts (part_id),
   FOREIGN KEY (from_part_id) REFERENCES parts (part_id),
   PRIMARY KEY ( data_connection_id )
);
CREATE TABLE drawings (
   drawing_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
   project_id TINYINT UNSIGNED,
   type ENUM('Elevations','Cable Block Diagram','Power Block Diagram') ,
   completed BOOLEAN DEFAULT 0,
   notes TEXT DEFAULT 'None',
   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,
   FOREIGN KEY (project_id) REFERENCES projects (project_id),
   PRIMARY KEY ( drawing_id )
);
CREATE TABLE dynamic_blocks (
   dynamic_block_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
   project_id TINYINT UNSIGNED,
   name VARCHAR(50),
   notes TEXT DEFAULT 'None',
   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,
   FOREIGN KEY (project_id) REFERENCES projects (project_id),
   PRIMARY KEY ( dynamic_block_id )
);
CREATE TABLE drawing_section_templates (
   drawing_section_template_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
   project_id TINYINT UNSIGNED,
   name VARCHAR(50),
   notes TEXT DEFAULT 'None',
   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,
   FOREIGN KEY (project_id) REFERENCES projects (project_id),
   PRIMARY KEY ( drawing_section_template_id )
);
2

I think you must create them in a certain order as they relay on each other. Just test with using db fiddle to try find errors. https://www.db-fiddle.com/f/eaQG8H4yqY9hnQBZjzJgz/146

2 Likes
3

Like @sibertius says, it’s an order thing.

Take a look at all of your create statements and compare them to the drop statements… If you see FOREIGN_KEY on it, those tables must be gone BEFORE the table referenced in the foreign key.

So:

  • data_connections has to be dropped before parts
  • parts has to be dropped before materials and racks
  • racks has to be dropped before materials and rooms
  • rooms has to be dropped before buildings
  • drawings, dynamic_blocks and drawing_section_templates has to be dropped before projects
  • buildings and projects has to be dropped before sites
  • sites has to be dropped before locations can be dropped

So looking at all that and comparing it to your drop statements, you’re left with

* data_connections has to be dropped before parts

3 Likes
4

step 1 –

DROP DATABASE mydatabase;

wait a couple minutes, then do step 2 –

CREATE DATABASE mydatabase;

vwalah

2 Likes
5

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