Drop table if exists error

lurtnowski · September 27, 2022, 2:47am

I tried resetting my database and I get

I on know if this is right but I thought I had to drop the tables in the opposite order that I create them…,heres the sql file, why doe it work when its imported, but not if I do it again.

DROP TABLE IF EXISTS data_connections;
DROP TABLE IF EXISTS pocs;
DROP TABLE IF EXISTS cables;
DROP TABLE IF EXISTS power_trails;
DROP TABLE IF EXISTS unknowns;
DROP TABLE IF EXISTS unknown_types;
DROP TABLE IF EXISTS periphial_storages;
DROP TABLE IF EXISTS periphial_storage_types;
DROP TABLE IF EXISTS shelfs;
DROP TABLE IF EXISTS servers;
DROP TABLE IF EXISTS server_types;
DROP TABLE IF EXISTS network_panels;
DROP TABLE IF EXISTS network_panel_types;
DROP TABLE IF EXISTS network_modules;
DROP TABLE IF EXISTS network_module_types;
DROP TABLE IF EXISTS network_cards;
DROP TABLE IF EXISTS network_card_types;
DROP TABLE IF EXISTS network_standards;
DROP TABLE IF EXISTS network_standard_types;
DROP TABLE IF EXISTS monitors;
DROP TABLE IF EXISTS monitor_types;
DROP TABLE IF EXISTS kvms;
DROP TABLE IF EXISTS kvm_types;
DROP TABLE IF EXISTS blade_servers;
DROP TABLE IF EXISTS blade_server_types;
DROP TABLE IF EXISTS chassises;
DROP TABLE IF EXISTS chassis_types;
DROP TABLE IF EXISTS brush_plates;
DROP TABLE IF EXISTS cable_trays;
DROP TABLE IF EXISTS blank_plates;
DROP TABLE IF EXISTS racks;
DROP TABLE IF EXISTS rack_types;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS furnishings;
DROP TABLE IF EXISTS ac_units;
DROP TABLE IF EXISTS power_strips;
DROP TABLE IF EXISTS receptacles;
DROP TABLE IF EXISTS bays;
DROP TABLE IF EXISTS columns;
DROP TABLE IF EXISTS circuit_breakers;
DROP TABLE IF EXISTS power_panels;
DROP TABLE IF EXISTS pdus;
DROP TABLE IF EXISTS upss;
DROP TABLE IF EXISTS atss;
DROP TABLE IF EXISTS generators;
DROP TABLE IF EXISTS utilities;
DROP TABLE IF EXISTS rooms;
DROP TABLE IF EXISTS buildings;


CREATE TABLE buildings (
   building_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
   name VARCHAR(25) NOT NULL,
   number SMALLINT 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 ( building_id )
);
CREATE TABLE rooms (
   room_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   building_id TINYINT UNSIGNED,
   zoom_factor TINYINT UNSIGNED,
   command VARCHAR(50),
   name VARCHAR(25) NOT NULL,
   length SMALLINT UNSIGNED,
   width SMALLINT UNSIGNED,
   height SMALLINT UNSIGNED,
   number SMALLINT 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,
   FOREIGN KEY (building_id) REFERENCES buildings (building_id),
   PRIMARY KEY ( room_id )
);
CREATE TABLE utilities (
   utility_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
   room_id SMALLINT UNSIGNED,   name VARCHAR(25) NOT NULL,
   asset_type VARCHAR(20) DEFAULT 'utility',
   type VARCHAR(25),
   serving VARCHAR(100),
   capacity INT,
   circuit TINYINT,
   voltage INT,
   frequency FLOAT(8,2),
   current FLOAT(8,2),
   active_power FLOAT(8,2),
   apparent_power FLOAT(8,2),
   active_energy FLOAT(8,2),
   power_factor DECIMAL(5,4),
   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 (room_id) REFERENCES rooms (room_id),
   PRIMARY KEY ( utility_id )
);
CREATE TABLE generators (
   generator_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
   room_id SMALLINT UNSIGNED,
   name VARCHAR(25) NOT NULL,
   asset_type VARCHAR(20) DEFAULT 'generator',
   prime_or_standby BOOLEAN DEFAULT 1,
   manufacturer VARCHAR(50),
   model VARCHAR(50),
   capacity INT,
   output_voltage DECIMAL(6,2),
   trip_rating INT,
   fuel_type ENUM ('Gas','Wind','Hope','Solar'),
   max_fuel_usage_rate DECIMAL(6,2),
   quantity_of_fuel_storage DECIMAL(6,2),
   frequency DECIMAL(6,2),
   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 (room_id) REFERENCES rooms (room_id),
   PRIMARY KEY ( generator_id )
);
CREATE TABLE atss (
   ats_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
   utility_id TINYINT UNSIGNED,
   generator_id TINYINT UNSIGNED,
   room_id SMALLINT UNSIGNED,
   name VARCHAR(25),
   asset_type VARCHAR(20) DEFAULT 'ats',
   manufacturer VARCHAR(50),
   model VARCHAR(50),
   type VARCHAR(25),
   poles 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 ( utility_id ) REFERENCES utilities( utility_id ),
   FOREIGN KEY (room_id) REFERENCES rooms (room_id),
   FOREIGN KEY ( generator_id ) REFERENCES generators( generator_id ),
   PRIMARY KEY ( ats_id )
);
create table upss (
   ups_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
   ats_id TINYINT UNSIGNED,
   room_id SMALLINT UNSIGNED,
   asset_type VARCHAR(20) DEFAULT 'ups',
   name VARCHAR(50) NOT NULL,
   manufacturer VARCHAR(50),
   model VARCHAR(50),
   capacity INT,
   frequency DECIMAL(6,2),
   voltage DECIMAL(6,2),
   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 (room_id) REFERENCES rooms (room_id),
   FOREIGN KEY ( ats_id ) REFERENCES atss( ats_id ),
   PRIMARY KEY ( ups_id )
);
CREATE TABLE pdus (
   pdu_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
   asset_type VARCHAR(20) DEFAULT 'pdu',
   phase ENUM ('1','2','3'),
   name VARCHAR(25) NOT NULL,
   manufacturer VARCHAR(50),
   model VARCHAR(50),
   rotated BINARY DEFAULT 0,
   x_coord SMALLINT UNSIGNED,
   y_coord SMALLINT UNSIGNED,   
   room_id SMALLINT UNSIGNED,
   width SMALLINT UNSIGNED,
   height SMALLINT UNSIGNED,
   depth SMALLINT UNSIGNED,
   voltage VARCHAR(25),
   notes TEXT DEFAULT 'None',
   created_by VARCHAR(50) DEFAULT 'lurtnowski@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 (room_id) REFERENCES rooms (room_id),
   PRIMARY KEY ( pdu_id )
);
CREATE TABLE power_panels (
   power_panel_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   pdu_id TINYINT UNSIGNED,
   room_id SMALLINT UNSIGNED,
   x_coord SMALLINT UNSIGNED,
   y_coord SMALLINT UNSIGNED,   
   rotated BINARY DEFAULT 0,
   command VARCHAR(50),
   name VARCHAR(25) NOT NULL,
   manufacturer VARCHAR(50),
   model VARCHAR(50),
   asset_type VARCHAR(20) DEFAULT 'power_panel',
   voltage VARCHAR(25),
   format BOOLEAN DEFAULT 0,
   slots TINYINT UNSIGNED,
   columns TINYINT UNSIGNED DEFAULT 2,
   width DECIMAL(5,2),
   height DECIMAL(5,2),
   depth DECIMAL(5,2),
   phase ENUM ('1','2','3'),
   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 (room_id) REFERENCES rooms (room_id),
   PRIMARY KEY ( power_panel_id )
);   
CREATE TABLE circuit_breakers (
   circuit_breaker_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   power_panel_id SMALLINT UNSIGNED,
   asset_type VARCHAR(20) DEFAULT 'circuit_breaker',
   manufacturer VARCHAR(50),
   model VARCHAR(50),
   slot VARCHAR(10),
   x_coord SMALLINT UNSIGNED,
   y_coord SMALLINT UNSIGNED,   
   poles TINYINT UNSIGNED,
   voltage SMALLINT UNSIGNED,
   phase ENUM ('1','2','3'),
   amp_rating SMALLINT 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,
   FOREIGN KEY ( power_panel_id ) REFERENCES power_panels( power_panel_id ),
   PRIMARY KEY ( circuit_breaker_id )
); 
CREATE TABLE columns (
   column_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
   room_id SMALLINT UNSIGNED,
   name VARCHAR(25),
   rotated BINARY DEFAULT 0,
   x_coord SMALLINT UNSIGNED,
   y_coord SMALLINT UNSIGNED,   
   width SMALLINT UNSIGNED,
   height SMALLINT UNSIGNED,
   notes TEXT DEFAULT NULL,
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   created_by VARCHAR(50) DEFAULT 'test@industechnology.com',
   updated_date TIMESTAMP NULL,
   updated_by VARCHAR(50),
   operational_status BOOLEAN DEFAULT 1,
   FOREIGN KEY ( room_id ) REFERENCES rooms( room_id ),
   PRIMARY KEY ( column_id )
  );
CREATE TABLE bays (
   bay_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   column_id TINYINT UNSIGNED,
   name VARCHAR(20),
   x_coord SMALLINT UNSIGNED,
   y_coord SMALLINT UNSIGNED,   
   width SMALLINT UNSIGNED,
   height SMALLINT 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,
   FOREIGN KEY ( column_id ) REFERENCES columns( column_id ),
   PRIMARY KEY ( bay_id )
);

lurtnowski · September 27, 2022, 2:48am

CREATE TABLE receptacles (
   receptacle_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   circuit_breaker_id SMALLINT UNSIGNED NULL,
   type VARCHAR(25),
   name VARCHAR(25),
   manufacturer VARCHAR(25),
   model VARCHAR(25),
   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,
   assigned BOOLEAN DEFAULT 0,
   operational_status BOOLEAN DEFAULT 1,
   FOREIGN KEY ( circuit_breaker_id ) REFERENCES circuit_breakers( circuit_breaker_id ),
   PRIMARY KEY ( receptacle_id )
);
CREATE TABLE power_strips (
   power_strip_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   receptacle_id SMALLINT UNSIGNED,
   asset_type VARCHAR(20) DEFAULT 'power_strip',
   rack_id INT UNSIGNED NOT NULL,
   location VARCHAR(25),
   system VARCHAR(50),
   name VARCHAR(25),
   manufacturer VARCHAR(25),
   model VARCHAR(25),
   rack_placement ENUM('Back','Front') DEFAULT 'Back',
   beginning_ru DECIMAL(3,1),
   ending_ru DECIMAL(3,1),
   group1_receptacle_type VARCHAR(25),
   group1_receptacles TINYINT UNSIGNED,
   group1_voltage ENUM('240','120','208'),
   group2_receptacle_type VARCHAR(25),
   group2_receptacles TINYINT UNSIGNED DEFAULT '0',
   group2_voltage ENUM('240','120','208'),
   group3_receptacle_type VARCHAR(25),
   group3_receptacles TINYINT UNSIGNED DEFAULT '0',
   group3_voltage ENUM('240','120','208'),
   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 ( receptacle_id ) REFERENCES receptacles ( receptacle_id ),
   PRIMARY KEY ( power_strip_id )
);
CREATE TABLE ac_units (
   ac_unit_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
   asset_type VARCHAR(20) DEFAULT 'ac_unit',
   room_id SMALLINT UNSIGNED,
   x_coord SMALLINT UNSIGNED,
   y_coord SMALLINT UNSIGNED,   
   rotated BINARY DEFAULT 0,
   name VARCHAR(25),
   manufacturer VARCHAR(50),
   model VARCHAR(50),
   height DECIMAL(6,2),   
   width DECIMAL(6,2),
   depth DECIMAL(6,2),
   weight DECIMAL(8,2),
   max_cooling_capacity INT UNSIGNED,
   operating_cooling_capacity INT UNSIGNED,
   cooling_area INT UNSIGNED,
   notes TEXT DEFAULT 'None',
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   created_by VARCHAR(50) DEFAULT 'test@industechnology.com',
   updated_date TIMESTAMP NULL,
   updated_by VARCHAR(50),
   operational_status BOOLEAN DEFAULT 1,
   FOREIGN KEY (room_id) REFERENCES rooms (room_id),
   PRIMARY KEY ( ac_unit_id )
);
CREATE TABLE furnishings (
   furniture_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
   name VARCHAR(25) NOT NULL,
   asset_type VARCHAR(20) DEFAULT 'furniture',
   room_id TINYINT UNSIGNED,
   x_coord SMALLINT UNSIGNED,
   y_coord SMALLINT UNSIGNED,   
   type VARCHAR(25),
   manufacturer VARCHAR(50),
   model VARCHAR(50),
   height DECIMAL(6,2),   
   width DECIMAL(6,2),
   depth DECIMAL(6,2),
   notes TEXT DEFAULT 'None',
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   created_by VARCHAR(50) DEFAULT 'test@industechnology.com',
   updated_date TIMESTAMP NULL,
   updated_by VARCHAR(50),
   operational_status BOOLEAN DEFAULT 1,
   PRIMARY KEY ( furniture_id )
);
CREATE TABLE users (
   user_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
   name VARCHAR(50) NOT NULL,
   email VARCHAR(50) NOT NULL,   
   pass VARCHAR(50) NOT NULL,
   role ENUM ('Administrator','Surveyer','Moderator'),
   created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   created_by VARCHAR(50) DEFAULT 'test@industechnology.com',
   updated TIMESTAMP,
   updated_by VARCHAR(50),
   operational_status BOOLEAN DEFAULT 1,
   UNIQUE KEY (email),
   PRIMARY KEY ( user_id )
);

CREATE TABLE rack_types (
   rack_type_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   model VARCHAR(25),
   manufacturer VARCHAR(25),
   external_width DECIMAL(6,3) DEFAULT 18,
   external_depth DECIMAL(6,3) DEFAULT 24,
   external_height DECIMAL(6,3) DEFAULT 78.815,
   weight DECIMAL(6,3),
   internal_rail_width DECIMAL(6,3),
   internal_rail_depth DECIMAL(6,3),
   internal_rail_height DECIMAL(6,3),
   height_ru TINYINT UNSIGNED,
   max_wattage TINYINT UNSIGNED,	
   total_plugs TINYINT UNSIGNED,
   required_plugs TINYINT UNSIGNED,
   requires_diverse_power BOOLEAN,
   PRIMARY KEY ( rack_type_id )
);
CREATE TABLE racks (
   rack_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   rack_type_id SMALLINT UNSIGNED,
   bay_id INT UNSIGNED,
   asset_type VARCHAR(20) DEFAULT 'rack',
   system VARCHAR(50),
   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 ( rack_type_id ) REFERENCES rack_types ( rack_type_id ),
   FOREIGN KEY ( bay_id ) REFERENCES bays ( bay_id ),
   PRIMARY KEY ( rack_id )
);
CREATE TABLE blank_plates (
   blank_plate_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   asset_type VARCHAR(20) DEFAULT 'blank_plate',
   rack_id INT UNSIGNED NOT NULL,
   system VARCHAR(50),
   beginning_ru DECIMAL(3,1),
   ending_ru DECIMAL(3,1),
   notes TEXT DEFAULT 'None',
   rack_mounting_direction ENUM('Front','Back') DEFAULT 'Front',
   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 ( rack_id ) REFERENCES racks ( rack_id ),
   PRIMARY KEY ( blank_plate_id )
);
CREATE TABLE cable_trays (
   cable_tray_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   asset_type VARCHAR(20) DEFAULT 'cable_tray',
   rack_id INT UNSIGNED NOT NULL,
   system VARCHAR(50),
   beginning_ru DECIMAL(3,1),
   ending_ru DECIMAL(3,1),
   notes TEXT DEFAULT 'None',
   rack_mounting_direction ENUM('Front','Back') DEFAULT 'Front',
   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 ( rack_id ) REFERENCES racks ( rack_id ),
   PRIMARY KEY ( cable_tray_id )
);
CREATE TABLE brush_plates (
   brush_plate_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   asset_type VARCHAR(20) DEFAULT 'brush_plate',
   rack_id INT UNSIGNED NOT NULL,
   system VARCHAR(50),
   beginning_ru DECIMAL(3,1),
   ending_ru DECIMAL(3,1),
   notes TEXT DEFAULT 'None',
   rack_mounting_direction ENUM('Front','Back') DEFAULT 'Front',
   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 ( rack_id ) REFERENCES racks ( rack_id ),
   PRIMARY KEY ( brush_plate_id )
);
CREATE TABLE chassis_types (
   chassis_type_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   name VARCHAR(50),
   model VARCHAR(50),
   manufacturer VARCHAR(50),
   record_status VARCHAR(15),
   airflow_type ENUM('Ambient','Front To Back'),
   external_width DECIMAL(6,3),
   external_depth DECIMAL(6,3),
   external_height DECIMAL(6,3),
   weight DECIMAL(6,3),
   number_of_columns_front TINYINT UNSIGNED,
   number_of_columns_back TINYINT UNSIGNED,
   number_of_rows_front TINYINT UNSIGNED,
   number_of_rows_back TINYINT UNSIGNED,
   total_plugs TINYINT UNSIGNED,
   required_plugs TINYINT UNSIGNED,
   plug_type ENUM('1','2'),
   input_power_type ENUM('One Phase','Two Phase','Three Phase','Direct Current'),
   mounting_type ENUM('Full Width Shelf','Rack 19 Inch','Floor Standing'),
   requires_diverse_power ENUM('Yes','No') DEFAULT NULL,
   power_consumption DECIMAL(4,2),
   PRIMARY KEY ( chassis_type_id )
);
CREATE TABLE chassises (
   chassis_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   chassis_type_id INT UNSIGNED,
   asset_type VARCHAR(20) DEFAULT 'Chassis',
   rack_id INT UNSIGNED,   
   system VARCHAR(50),
   name VARCHAR(25),
   rack_mounting_direction ENUM('Front','Back') DEFAULT 'Front',
   beginning_ru DECIMAL(3,1),
   ending_ru DECIMAL(3,1),
   beginning_x DECIMAL(4,1),
   notes TEXT DEFAULT 'None',
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   created_by VARCHAR(50) DEFAULT 'test@industechnology.com',
   updated_date TIMESTAMP NULL,
   updated_by VARCHAR(50),
   operational_status BOOLEAN DEFAULT 1,
   FOREIGN KEY ( chassis_type_id ) REFERENCES chassis_types ( chassis_type_id ),
   FOREIGN KEY ( rack_id ) REFERENCES racks ( rack_id ),
   PRIMARY KEY ( chassis_id )
);
CREATE TABLE blade_server_types (
   blade_server_type_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   name VARCHAR(50),
   model VARCHAR(50),
   manufacturer VARCHAR(50),
   external_width DECIMAL(6,3),
   external_depth DECIMAL(6,3),
   external_height DECIMAL(6,3),
   power_consumption DECIMAL(4,2),
   weight DECIMAL(6,3),
   number_of_rows TINYINT UNSIGNED,
   number_of_columns TINYINT UNSIGNED,
   PRIMARY KEY ( blade_server_type_id )
);
CREATE TABLE blade_servers (
   blade_server_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   blade_server_type_id INT UNSIGNED,
   asset_type VARCHAR(20) DEFAULT 'blade_server',
   chassis_id SMALLINT UNSIGNED,
   chassis_row TINYINT UNSIGNED,
   chassis_slot TINYINT UNSIGNED,
   chassis_col TINYINT UNSIGNED,
   chassis_mounting_direction  ENUM('Front','Back') DEFAULT 'Front',
   name VARCHAR(25),
   notes TEXT DEFAULT 'None',
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   created_by VARCHAR(50) DEFAULT 'test@industechnology.com',
   updated_date TIMESTAMP NULL,
   updated_by VARCHAR(50),
   operational_status BOOLEAN DEFAULT 1,
   FOREIGN KEY ( blade_server_type_id ) REFERENCES blade_server_types ( blade_server_type_id ),
   FOREIGN KEY ( chassis_id ) REFERENCES chassises ( chassis_id ), 
   PRIMARY KEY ( blade_server_id )
);
CREATE TABLE kvm_types (
   kvm_type_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   model VARCHAR(50),
   manufacturer VARCHAR(50),
   number_of_kvm_outlets TINYINT UNSIGNED,
   external_width DECIMAL(6,3),
   external_depth DECIMAL(6,3),
   external_height DECIMAL(6,3),
   weight DECIMAL(6,3),
   mounting_type ENUM('Full width Shelf','Rack 19 inch'),
   input_power_type ENUM('One Phase'),
   total_plugs TINYINT UNSIGNED,
   required_plugs TINYINT UNSIGNED,
   plug_type ENUM('1','2'),
   requires_diverse_power ENUM('Yes','No'),
   power_consumption DECIMAL(4,2),
   PRIMARY KEY ( kvm_type_id )
);
CREATE TABLE kvms (
   kvm_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   kvm_type_id INT UNSIGNED,
   asset_type VARCHAR(20) DEFAULT 'kvm',
   rack_id INT UNSIGNED,
   system VARCHAR(50),
   rack_mounting_direction ENUM('Front','Back') DEFAULT 'Front',
   name VARCHAR(25),
   beginning_ru DECIMAL(3,1),
   ending_ru DECIMAL(3,1),
   beginning_x DECIMAL(4,1),
   notes TEXT DEFAULT 'None',
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   created_by VARCHAR(50) DEFAULT 'test@industechnology.com',
   updated_date TIMESTAMP NULL,
   updated_by VARCHAR(50),
   operational_status BOOLEAN DEFAULT 1,
   FOREIGN KEY ( kvm_type_id ) REFERENCES kvm_types ( kvm_type_id ),
   FOREIGN KEY ( rack_id ) REFERENCES racks ( rack_id ),
   PRIMARY KEY ( kvm_id )
);

CREATE TABLE monitor_types (
   monitor_type_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   model VARCHAR(50),
   manufacturer VARCHAR(50),
   airflow_type ENUM('Ambient','Front To Back'),
   mounting_type ENUM('Full Width Shelf','Rack 19 Inch','Floor Standing'),
   external_width DECIMAL(6,3),
   external_depth DECIMAL(6,3),
   external_height DECIMAL(6,3),
   weight DECIMAL(6,3),
   input_power_type ENUM('One Phase','Two Phase','Three Phase','Direct Current'),
   total_plugs TINYINT(1),
   required_plugs TINYINT(1),
   requires_diverse_power ENUM('Yes','No'),
   power_consumption DECIMAL(4,2),
   PRIMARY KEY ( monitor_type_id )
);
CREATE TABLE monitors (
   monitor_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   monitor_type_id INT UNSIGNED,
   asset_type VARCHAR(20) DEFAULT 'monito',
   rack_id INT UNSIGNED,   
   system VARCHAR(50),
   name VARCHAR(25),
   rack_mounting_direction ENUM('Front','Back') DEFAULT 'Front',
   mounting_location ENUM('External','Internal'),
   beginning_ru DECIMAL(3,1),
   ending_ru DECIMAL(3,1),
   beginning_x DECIMAL(4,1),
   notes TEXT DEFAULT 'None',
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   created_by VARCHAR(50) DEFAULT 'test@industechnology.com',
   updated_date TIMESTAMP NULL,
   updated_by VARCHAR(50),
   operational_status BOOLEAN DEFAULT 1,
   FOREIGN KEY ( monitor_type_id ) REFERENCES monitor_types ( monitor_type_id ),
   FOREIGN KEY ( rack_id ) REFERENCES racks ( rack_id ),
   PRIMARY KEY ( monitor_id )
);
CREATE TABLE network_standard_types (
   network_standard_type_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   model VARCHAR(50),
   manufacturer VARCHAR(50),
   external_width DECIMAL(6,3),
   external_depth DECIMAL(6,3),
   external_height DECIMAL(6,3),
   weight DECIMAL(6,3),
   standard_copper_ports TINYINT UNSIGNED,
   standard_fiber_optic_ports TINYINT UNSIGNED,
   unidentified_ports TINYINT UNSIGNED,
   total_plugs TINYINT(1),
   required_plugs TINYINT(1),
   requires_diverse_power ENUM('Yes','No'),
   power_consumption DECIMAL(4,2),
   mounting_type ENUM('Full Width Shelf','Rack 19 Inch','Floor Standing'),
   input_power_type ENUM('One Phase','Two Phase','Three Phase','Direct Current'),
   PRIMARY KEY ( network_standard_type_id )
);
CREATE TABLE network_standards (
   network_standard_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   network_standard_type_id INT UNSIGNED,
   asset_type VARCHAR(20) DEFAULT 'network_standard',
   rack_id INT UNSIGNED,   
   system VARCHAR(50),
   chassis_id SMALLINT UNSIGNED,
   chassis_row TINYINT UNSIGNED,
   chassis_col TINYINT UNSIGNED,
   chassis_mounting_direction ENUM('Front','Back') DEFAULT 'Front',
   rack_mounting_direction ENUM('Front','Back') DEFAULT 'Front',
   name VARCHAR(25) DEFAULT 'Unknown',
   beginning_ru DECIMAL(3,1),
   ending_ru DECIMAL(3,1),
   beginning_x DECIMAL(4,1),
   beginning_y DECIMAL(4,1),
   notes TEXT DEFAULT 'None',
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   created_by VARCHAR(50) DEFAULT 'test@industechnology.com',
   updated_date TIMESTAMP NULL,
   updated_by VARCHAR(50),
   operational_status BOOLEAN DEFAULT 1,
   FOREIGN KEY ( network_standard_type_id ) REFERENCES network_standard_types ( network_standard_type_id ),
   FOREIGN KEY ( rack_id ) REFERENCES racks ( rack_id ),
   FOREIGN KEY ( chassis_id ) REFERENCES chassises ( chassis_id ),
   PRIMARY KEY ( network_standard_id )
);
CREATE TABLE network_card_types (
   network_card_type_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   model VARCHAR(50),
   manufacturer VARCHAR(50),
   standard_copper_ports TINYINT UNSIGNED,
   standard_fiber_optic_ports TINYINT UNSIGNED,
   unidentified_ports TINYINT UNSIGNED,
   network_speed TINYINT UNSIGNED,
   PRIMARY KEY ( network_card_type_id )
);
CREATE TABLE network_cards (
   network_card_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   network_card_type_id INT UNSIGNED,
   asset_type VARCHAR(20) DEFAULT 'network_card',
   name VARCHAR(25),
   chassis_id SMALLINT UNSIGNED,
   chassis_row TINYINT UNSIGNED,
   chassis_col TINYINT UNSIGNED,
   chassis_mounting_direction ENUM('Front','Back') DEFAULT 'Front',
   notes TEXT DEFAULT 'None',
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   created_by VARCHAR(50) DEFAULT 'test@industechnology.com',
   updated_date TIMESTAMP NULL,
   updated_by VARCHAR(50),
   operational_status BOOLEAN DEFAULT 1,
   FOREIGN KEY ( network_card_type_id ) REFERENCES network_card_types ( network_card_type_id ),
   FOREIGN KEY ( chassis_id ) REFERENCES chassises ( chassis_id ),
   PRIMARY KEY ( network_card_id )
);
CREATE TABLE network_module_types (
   network_module_type_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   model VARCHAR(50),
   manufacturer VARCHAR(50),
   external_width DECIMAL(6,3),
   external_depth DECIMAL(6,3),
   external_height DECIMAL(6,3),
   weight DECIMAL(6,3),
   standard_copper_ports TINYINT UNSIGNED,
   standard_fiber_optic_ports TINYINT UNSIGNED,
   unidentified_ports TINYINT UNSIGNED,
   number_of_rows TINYINT UNSIGNED,
   number_of_columns TINYINT UNSIGNED,
   network_speed DECIMAL(4,2),
   power_consumption DECIMAL(4,2),
   PRIMARY KEY ( network_module_type_id )
);
CREATE TABLE network_modules (
   network_module_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   network_module_type_id INT UNSIGNED, 
   asset_type VARCHAR(20) DEFAULT 'network_module',
   chassis_id SMALLINT UNSIGNED,
   chassis_row TINYINT UNSIGNED,
   chassis_col TINYINT UNSIGNED,
   chassis_mounting_direction  ENUM('Front','Back') DEFAULT 'Front',
   name VARCHAR(100) DEFAULT 'Unknown',
   notes TEXT DEFAULT 'None',
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   created_by VARCHAR(50) DEFAULT 'test@industechnology.com',
   updated_date TIMESTAMP NULL,
   updated_by VARCHAR(50),
   operational_status BOOLEAN DEFAULT 1,
   FOREIGN KEY ( network_module_type_id ) REFERENCES network_module_types ( network_module_type_id ),
   FOREIGN KEY ( chassis_id ) REFERENCES chassises ( chassis_id ),
   PRIMARY KEY ( network_module_id )
);
CREATE TABLE network_panel_types (
   network_panel_type_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   model VARCHAR(50),
   manufacturer VARCHAR(50),
   external_width DECIMAL(6,3),
   external_depth DECIMAL(6,3),
   external_height DECIMAL(6,3),
   weight DECIMAL(6,3),
   standard_copper_ports TINYINT UNSIGNED,
   standard_fiber_optic_ports TINYINT UNSIGNED,
   unidentified_ports TINYINT UNSIGNED,
   mounting_type ENUM('Full Width Shelf','Rack 19 Inch','Floor Standing'),
   network_speed DECIMAL(4,2),
   PRIMARY KEY ( network_panel_type_id )
);
CREATE TABLE network_panels (
   network_panel_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   network_panel_type_id INT UNSIGNED,
   asset_type VARCHAR(20) DEFAULT 'network_panel',
   rack_id INT UNSIGNED,   
   system VARCHAR(50),
   name VARCHAR(25),
   rack_mounting_direction ENUM('Front','Back') DEFAULT 'Front',
   beginning_ru DECIMAL(3,1),
   ending_ru DECIMAL(3,1),
   beginning_x DECIMAL(4,1),
   notes TEXT DEFAULT 'None',
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   created_by VARCHAR(50) DEFAULT 'test@industechnology.com',
   updated_date TIMESTAMP NULL,
   updated_by VARCHAR(50),
   operational_status BOOLEAN DEFAULT 1,
   FOREIGN KEY ( network_panel_type_id ) REFERENCES network_panel_types ( network_panel_type_id ),
   FOREIGN KEY ( rack_id ) REFERENCES racks ( rack_id ),
   PRIMARY KEY ( network_panel_id )
);
CREATE TABLE server_types (
   server_type_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   model VARCHAR(50),
   manufacturer VARCHAR(50),
   mounting ENUM('Full width Shelf','Rack 19 inch','External','Internal'),
   airflow_type ENUM('Ambient','Front To Back'),
   external_width DECIMAL(6,3),
   external_depth DECIMAL(6,3),
   external_height DECIMAL(6,3),
   weight DECIMAL(6,3),
   input_power_type ENUM('One Phase'),
   requires_kvm_switch BOOLEAN,
   total_plugs TINYINT UNSIGNED,
   required_plugs TINYINT UNSIGNED,
   plug_type ENUM('1','2'),
   requires_diverse_power ENUM('Yes','No') DEFAULT NULL,
   power_consumption DECIMAL(4,2) DEFAULT NULL,
   PRIMARY KEY ( server_type_id )
);
CREATE TABLE servers (
   server_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   server_type_id INT UNSIGNED,
   asset_type VARCHAR(20) DEFAULT 'server',
   rack_id INT UNSIGNED,   
   system VARCHAR(50),
   name VARCHAR(25),
   rack_mounting_direction ENUM('Front','Back') DEFAULT 'Front',
   beginning_ru DECIMAL(3,1),
   ending_ru DECIMAL(3,1),
   beginning_x DECIMAL(4,1),
   notes TEXT DEFAULT 'None',
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   created_by VARCHAR(50) DEFAULT 'test@industechnology.com',
   updated_date TIMESTAMP NULL,
   updated_by VARCHAR(50),
   operational_status BOOLEAN DEFAULT 1,
   FOREIGN KEY ( server_type_id ) REFERENCES server_types ( server_type_id ),
   FOREIGN KEY ( rack_id ) REFERENCES racks ( rack_id ),
   PRIMARY KEY ( server_id )
);
CREATE TABLE shelfs (
   shelf_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   asset_type TINYINT DEFAULT 10,
   rack_id INT UNSIGNED,
   system VARCHAR(50),
   rack_mounting_direction ENUM('Front','Back') DEFAULT 'Front',
   model VARCHAR(50) DEFAULT NULL,
   manufacturer VARCHAR(50) DEFAULT NULL,
   external_width DECIMAL(6,3) DEFAULT 17.5,
   external_height DECIMAL(6,3) DEFAULT .1,
   beginning_ru DECIMAL(3,1),
   ending_ru DECIMAL(3,1),
   notes TEXT DEFAULT 'None',
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   created_by VARCHAR(50) DEFAULT 'lurtnowski@industechnology.com',
   updated_date TIMESTAMP NULL,
   updated_by VARCHAR(50),
   operational_status BOOLEAN DEFAULT 1,
   FOREIGN KEY ( rack_id ) REFERENCES racks ( rack_id ),
   PRIMARY KEY ( shelf_id )
);
CREATE TABLE periphial_storage_types (
   periphial_storage_type_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   model VARCHAR(50),
   manufacturer VARCHAR(50),
   external_width DECIMAL(6,3),
   external_depth DECIMAL(6,3),
   external_height DECIMAL(6,3),
   weight DECIMAL(6,3),
   total_plugs TINYINT UNSIGNED,
   required_plugs TINYINT UNSIGNED,
   requires_diverse_power ENUM('Yes','No'),
   power_consumption DECIMAL(4,2),
   PRIMARY KEY ( periphial_storage_type_id )
);
CREATE TABLE periphial_storages (
   periphial_storage_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   periphial_storage_type_id INT UNSIGNED,
   asset_type VARCHAR(20) DEFAULT 'periphial_storage',
   rack_id INT UNSIGNED,
   system VARCHAR(50),
   name VARCHAR(100) DEFAULT 'Unknown',
   rack_mounting_direction ENUM('Front','Back') DEFAULT 'Front',
   beginning_ru DECIMAL(3,1),
   ending_ru DECIMAL(3,1),
   beginning_x DECIMAL(4,1),
   notes TEXT DEFAULT 'None',
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   created_by VARCHAR(50) DEFAULT 'test@industechnology.com',
   updated_date TIMESTAMP NULL,
   updated_by VARCHAR(50),
   operational_status BOOLEAN DEFAULT 1,
   FOREIGN KEY ( periphial_storage_type_id ) REFERENCES periphial_storage_types ( periphial_storage_type_id ),
   FOREIGN KEY ( rack_id ) REFERENCES racks ( rack_id ),
   PRIMARY KEY ( periphial_storage_id )
);
CREATE TABLE unknown_types (
   unknown_type_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   model VARCHAR(50),
   manufacturer VARCHAR(50),
   ports TINYINT UNSIGNED,
   plugs TINYINT UNSIGNED,
   width DECIMAL(6,2),
   depth DECIMAL(6,2),
   height DECIMAL(6,2),
   PRIMARY KEY ( unknown_type_id )
);
CREATE TABLE unknowns (
   unknown_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   unknown_type_id INT UNSIGNED,
   asset_type VARCHAR(20) DEFAULT 'Unknown',
   rack_id INT UNSIGNED,
   system VARCHAR(50),
   name VARCHAR(100) DEFAULT NULL,
   chassis_id SMALLINT UNSIGNED,
   chassis_row TINYINT UNSIGNED,
   chassis_col TINYINT UNSIGNED,
   chassis_mounting_direction  ENUM('Front','Back'),
   rack_mounting_direction ENUM('Front','Back') DEFAULT 'Front',
   beginning_ru DECIMAL(3,1),
   ending_ru DECIMAL(3,1),
   beginning_x DECIMAL(4,1),
   notes TEXT DEFAULT NULL,
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   created_by VARCHAR(50) DEFAULT 'test@industechnology.com',
   updated_date TIMESTAMP NULL,
   updated_by VARCHAR(50),
   operational_status BOOLEAN DEFAULT 1,
   FOREIGN KEY ( unknown_type_id ) REFERENCES unknown_types ( unknown_type_id ),
   FOREIGN KEY ( rack_id ) REFERENCES racks ( rack_id ),
   FOREIGN KEY ( chassis_id ) REFERENCES chassises ( chassis_id ),
   PRIMARY KEY ( unknown_id )
);
CREATE TABLE power_trails (
   power_trail_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   power_strip_id SMALLINT UNSIGNED,
   outlett_placement TINYINT UNSIGNED,
   receptacle_id SMALLINT UNSIGNED,
   circuit_breaker_id SMALLINT UNSIGNED,
   ups_id TINYINT UNSIGNED,
   pdu_id TINYINT UNSIGNED,
   power_panel_id SMALLINT UNSIGNED,
   unknown_id SMALLINT UNSIGNED NULL,
   periphial_storage_id SMALLINT UNSIGNED NULL,  
   server_id SMALLINT UNSIGNED NULL,  
   network_standard_id SMALLINT UNSIGNED NULL,  
   monitor_id SMALLINT UNSIGNED NULL,  
   kvm_id SMALLINT UNSIGNED NULL,  
   chassis_id SMALLINT UNSIGNED NULL,  
   operational_status BOOLEAN DEFAULT 1,
   FOREIGN KEY ( unknown_id ) REFERENCES unknowns( unknown_id ),
   FOREIGN KEY ( periphial_storage_id ) REFERENCES periphial_storages( periphial_storage_id ),
   FOREIGN KEY ( server_id ) REFERENCES servers( server_id ),
   FOREIGN KEY ( network_standard_id ) REFERENCES network_standards( network_standard_id ),
   FOREIGN KEY ( monitor_id ) REFERENCES monitors( monitor_id ),
   FOREIGN KEY ( kvm_id ) REFERENCES kvms( kvm_id ),
   FOREIGN KEY ( chassis_id ) REFERENCES chassises( chassis_id ),
   FOREIGN KEY ( power_strip_id ) REFERENCES power_strips( power_strip_id ),
   FOREIGN KEY ( receptacle_id ) REFERENCES receptacles( receptacle_id ),
   FOREIGN KEY ( circuit_breaker_id ) REFERENCES circuit_breakers( circuit_breaker_id ),
   FOREIGN KEY ( power_panel_id ) REFERENCES power_panels( power_panel_id ),
   FOREIGN KEY ( pdu_id ) REFERENCES pdus( pdu_id ),
   FOREIGN KEY ( ups_id ) REFERENCES upss( ups_id ),
   PRIMARY KEY ( power_trail_id )
);
CREATE TABLE cables (
   cable_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   from_blade_server_id SMALLINT UNSIGNED NULL,
   from_chassis_id SMALLINT UNSIGNED NULL,  
   from_kvm_id SMALLINT UNSIGNED NULL,  
   from_monitor_id SMALLINT UNSIGNED NULL,  
   from_network_card_id SMALLINT UNSIGNED NULL,  
   from_network_module_id SMALLINT UNSIGNED NULL,  
   from_network_panel_id SMALLINT UNSIGNED NULL,  
   from_network_standard_id SMALLINT UNSIGNED NULL,  
   from_periphial_storage_id SMALLINT UNSIGNED NULL,  
   from_server_id SMALLINT UNSIGNED NULL,  
   from_unknown_id SMALLINT UNSIGNED NULL,
   to_blade_server_id SMALLINT UNSIGNED NULL,
   to_chassis_id SMALLINT UNSIGNED NULL,  
   to_kvm_id SMALLINT UNSIGNED NULL,  
   to_monitor_id SMALLINT UNSIGNED NULL,  
   to_network_card_id SMALLINT UNSIGNED NULL,  
   to_network_module_id SMALLINT UNSIGNED NULL,  
   to_network_panel_id SMALLINT UNSIGNED NULL,  
   to_network_standard_id SMALLINT UNSIGNED NULL,  
   to_periphial_storage_id SMALLINT UNSIGNED NULL,  
   to_server_id SMALLINT UNSIGNED NULL,  
   to_unknown_id SMALLINT UNSIGNED NULL,
   operational_status BOOLEAN DEFAULT 1,
   FOREIGN KEY ( to_blade_server_id ) REFERENCES blade_servers( blade_server_id ),
   FOREIGN KEY ( to_chassis_id ) REFERENCES chassises( chassis_id ),
   FOREIGN KEY ( to_kvm_id ) REFERENCES kvms( kvm_id ),
   FOREIGN KEY ( to_monitor_id ) REFERENCES monitors( monitor_id ),
   FOREIGN KEY ( to_network_card_id ) REFERENCES network_cards( network_card_id ),
   FOREIGN KEY ( to_network_module_id ) REFERENCES network_modules( network_module_id ),
   FOREIGN KEY ( to_network_panel_id ) REFERENCES network_panels( network_panel_id ),
   FOREIGN KEY ( to_network_standard_id ) REFERENCES network_standards( network_standard_id ),
   FOREIGN KEY ( to_periphial_storage_id ) REFERENCES periphial_storages( periphial_storage_id ),
   FOREIGN KEY ( to_server_id ) REFERENCES servers( server_id ),
   FOREIGN KEY ( to_unknown_id ) REFERENCES unknowns( unknown_id ),
   PRIMARY KEY ( cable_id )
);

CREATE TABLE pocs (
   poc_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
   name VARCHAR(50),
   phone INT UNSIGNED,
   email VARCHAR(50),
   position VARCHAR(50),
   PRIMARY KEY ( poc_id )
);

CREATE TABLE port_connections (
   port_connection_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   port_a_name VARCHAR(25) NULL,
   port_a_type VARCHAR(25),
   port_b_name VARCHAR(25) NULL,
   port_b_type VARCHAR(25),
   asset_a_type VARCHAR(16),
   asset_b_type VARCHAR(16),  
   chassis_id_a SMALLINT UNSIGNED NULL,
   chassis_id_b SMALLINT UNSIGNED NULL,
   kvm_id_a SMALLINT UNSIGNED NULL,
   kvm_id_b SMALLINT UNSIGNED NULL,
   network_panel_id_a SMALLINT UNSIGNED NULL,
   network_panel_id_b SMALLINT UNSIGNED NULL,
   network_standard_id_a SMALLINT UNSIGNED NULL,
   network_standard_id_b SMALLINT UNSIGNED NULL,
   monitor_id_a SMALLINT UNSIGNED NULL,
   monitor_id_b SMALLINT UNSIGNED NULL,
   periphial_storage_id_a SMALLINT UNSIGNED NULL,
   periphial_storage_id_b SMALLINT UNSIGNED NULL,
   server_id_a SMALLINT UNSIGNED NULL,
   server_id_b SMALLINT UNSIGNED NULL,
   blade_server_id_a SMALLINT UNSIGNED NULL,
   blade_server_id_b SMALLINT UNSIGNED NULL,
   network_module_id_a SMALLINT UNSIGNED NULL,
   network_module_id_b SMALLINT UNSIGNED NULL,
   network_card_id_a SMALLINT UNSIGNED NULL,
   network_card_id_b SMALLINT UNSIGNED NULL,
   unknown_id_a SMALLINT UNSIGNED NULL,
   unknown_id_b SMALLINT UNSIGNED NULL,
   cable_type VARCHAR(50),
   cable_length DECIMAL(6,2),
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   created_by VARCHAR(50) DEFAULT 'test@industechnology.com',
   updated_date TIMESTAMP NULL,
   updated_by VARCHAR(50),
   notes TEXT DEFAULT 'None',
   operational_status BOOLEAN DEFAULT 1,
   FOREIGN KEY ( blade_server_id_a ) REFERENCES blade_servers( blade_server_id ),
   FOREIGN KEY ( blade_server_id_b ) REFERENCES blade_servers( blade_server_id ),
   FOREIGN KEY ( chassis_id_a ) REFERENCES chassises( chassis_id ),
   FOREIGN KEY ( chassis_id_b ) REFERENCES chassises( chassis_id ),
   FOREIGN KEY ( kvm_id_a ) REFERENCES kvms( kvm_id ),
   FOREIGN KEY ( kvm_id_b ) REFERENCES kvms( kvm_id ),
   FOREIGN KEY ( network_panel_id_a ) REFERENCES network_panels( network_panel_id ),
   FOREIGN KEY ( network_panel_id_b ) REFERENCES network_panels( network_panel_id ),
   FOREIGN KEY ( network_card_id_a ) REFERENCES network_cards( network_card_id ),
   FOREIGN KEY ( network_card_id_b ) REFERENCES network_cards( network_card_id ),
   FOREIGN KEY ( network_standard_id_a ) REFERENCES network_standards( network_standard_id ),
   FOREIGN KEY ( network_standard_id_b ) REFERENCES network_standards( network_standard_id ),
   FOREIGN KEY ( network_module_id_a ) REFERENCES network_modules( network_module_id ),
   FOREIGN KEY ( network_module_id_b ) REFERENCES network_modules( network_module_id ),
   FOREIGN KEY ( monitor_id_a ) REFERENCES monitors( monitor_id ),
   FOREIGN KEY ( monitor_id_b ) REFERENCES monitors( monitor_id ),
   FOREIGN KEY ( periphial_storage_id_a ) REFERENCES periphial_storages( periphial_storage_id ),
   FOREIGN KEY ( periphial_storage_id_b ) REFERENCES periphial_storages( periphial_storage_id ),
   FOREIGN KEY ( unknown_id_a ) REFERENCES unknowns( unknown_id ),
   FOREIGN KEY ( unknown_id_b ) REFERENCES unknowns( unknown_id ),
   PRIMARY KEY ( port_connection_id )
);

combine both snippets into a sql file and you can see the error

Gandalf · September 27, 2022, 7:34am

I’m not sure how anyone is expected to read that. It would be more helpful if you posted the error as plain text.

DaveMaxwell · September 27, 2022, 12:27pm

That error means you’re trying to delete a table where the primary key is a foreign key on another table. Look at the tables that have FOREIGN_KEY defined on them. They need to be deleted FIRST.

power_strips has to be deleted for receptacles
receptacles has to be deleted before circuit_breakers
ac_units has to be deleted before rooms

and so on.

r937 · September 27, 2022, 2:47pm

it works the first time because the tables did not exist

so the DROP TABLE IF EXISTS doesn’t attempt to drop any of them, because they don’t exist

when you do it the second time, you had by then established FK-PK dependencies, and you are not allowed to drop a table if some other table is pointing to it with a FK reference

how to fix this? why, just turn off the FK checks!!!

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS data_connections;
DROP TABLE IF EXISTS pocs;
DROP TABLE IF EXISTS cables;
DROP TABLE IF EXISTS power_trails;
...
SET FOREIGN_KEY_CHECKS = 1;

pro tip – it is not necessary to dump your entire schema script to debug a single error

when you post a wall of text like that, the typical response will be TL;DR (too long; didn’t read)

so if you’ve ever wondered where “TL;DR” came from, now you know

system · December 27, 2022, 9:47pm

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