Many foreign keys

lurtnowski · March 10, 2022, 5:43am

I have a table

CREATE TABLE ports (
   port_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
   type ENUM('1','2','3','4','5','6','7','8','9','10','11','12'),
   material_type TINYINT UNSIGNED,  
   material_id SMALLINT UNSIGNED,  
   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 ( material_id ) REFERENCES periphial_storages( periphial_storage_id ),
   FOREIGN KEY ( material_id ) REFERENCES servers( server_id ),
   FOREIGN KEY ( material_id ) REFERENCES network_panels( network_panel_id ),
   FOREIGN KEY ( material_id ) REFERENCES network_modules( network_module_id ),
   FOREIGN KEY ( material_id ) REFERENCES network_cards( network_card_id ),
   FOREIGN KEY ( material_id ) REFERENCES network_standards( network_standard_id ),
   FOREIGN KEY ( material_id ) REFERENCES monitors( monitor_id ),
   FOREIGN KEY ( material_id ) REFERENCES kvms( kvm_id ),
   FOREIGN KEY ( material_id ) REFERENCES blade_servers( blade_server_id ),
   FOREIGN KEY ( material_id ) REFERENCES chassises( chassis_id ),
   PRIMARY KEY ( port_id )
);

All seems good as PHPMyAdmin seems cool with it (no errors)
I get this though when I try and INSERT

Am I setting it up wrong,

lurtnowski · March 10, 2022, 5:56am

i used

CREATE TABLE ports (
   port_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
   type ENUM('1','2','3','4','5','6','7','8','9','10','11','12'),
   periphial_storage_id SMALLINT UNSIGNED,  
   server_id SMALLINT UNSIGNED,  
   network_panel_id SMALLINT UNSIGNED,  
   network_module_id SMALLINT UNSIGNED,  
   network_card_id SMALLINT UNSIGNED,  
   network_standard_id SMALLINT UNSIGNED,  
   monitor_id SMALLINT UNSIGNED,  
   kvm_id SMALLINT UNSIGNED,  
   blade_server_id SMALLINT UNSIGNED,  
   chassis_id SMALLINT UNSIGNED,  
   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 ( periphial_storage_id ) REFERENCES periphial_storages( periphial_storage_id ),
   FOREIGN KEY ( server_id ) REFERENCES servers( server_id ),
   FOREIGN KEY ( network_panel_id ) REFERENCES network_panels( network_panel_id ),
   FOREIGN KEY ( network_module_id ) REFERENCES network_modules( network_module_id ),
   FOREIGN KEY ( network_card_id ) REFERENCES network_cards( network_card_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 ( blade_server_id ) REFERENCES blade_servers( blade_server_id ),
   FOREIGN KEY ( chassis_id ) REFERENCES chassises( chassis_id ),
   PRIMARY KEY ( port_id )
);

but

r937 · March 10, 2022, 3:36pm

do you understand what the error message is telling you?

please don’t just post an error message – ask a question

and make it a real question, not something vague like your earlier “Am I setting it up wrong” because you are going to get replies that say “yes, yes you are”

benanamen · March 10, 2022, 4:11pm

Your DB design could use improvement. Learn about Database Normalization.

r937 · March 10, 2022, 5:32pm

can you point out where OP’s database design is not normalized?

because i think his tables are fine

benanamen · March 10, 2022, 5:36pm

He is creating a separate table for every product item and a separate table for every products type. The amount of DB tables will grow to infinity as he keeps adding products. It is not scalable at all. The tables double every time he adds a product. A thousand products and he will have two thousand tables. He has also quadrupled the tables for servers.

r937 · March 10, 2022, 5:42pm

what products? i don’t see any products

benanamen · March 10, 2022, 5:44pm

C’mon, you know what I am talking about. Blade servers, monitors, kvms,network cards, racks…

Ok, hardware, is that better for you?

r937 · March 10, 2022, 6:25pm

no, sorry, i do not

streets, cities, counties, provinces, countries – they’re all geographic units, you gonna try to normalize them too?

besides, normalization has very specific meaning with regard to the relationship between keys and attributes (you could look up the rules for 2nd normal form, for example, and be mildly surprised what it actually means)

so yeah, normalizattion… i do not think it means what you think it means

benanamen · March 10, 2022, 6:37pm

I didn’t say a single word about the “geographic units”. Seems you are just deflecting from the point I made. Are you saying a good DB schema should keep adding two tables every single time a new piece of hardware is added to the DB?

r937 · March 10, 2022, 6:38pm

i didn’t say a single word about adding new pieces of hardware

seems you are just deflecting from the point I made about what normalization actually means

benanamen · March 10, 2022, 6:43pm

I did, and that’s the ENTIRE point of what I have been talking about which you refuse to address.

merry

Gandalf · March 10, 2022, 6:45pm

@r937 and @benanamen your “discussion” becoming off-topic. If you want to continue this line of argument, please do so elsewhere and remember the OP’s original query.

r937 · March 10, 2022, 6:46pm

but my dear benanamen, i ~did~ address it

i told you that what you’re suggesting is ~not~ normalization

i think we’re done here

windbeneathmywings · March 10, 2022, 11:22pm

I agree with benanamen. The database architecture does not lend itself to scaling the business as it relates to adding new hardware products. It might seem fine for a small amount of products but that will slowly degrade as more and more products are added. The database is one of the most difficult and risk involved things to change once deployed. Therefore, it is critical to think about how the model will scale as the business does. It kind of seems it’s to late here for this poor soul but maybe others can learn from these mistakes. There are reasons why commerce platforms don’t use this model.

rpkamp · March 11, 2022, 1:03pm

All of these have different properties and different requirements. For example, you can connect a kvm to a server, but you can’t connect it to a rack.

So I do see the point of putting those in different tables.

DaveMaxwell · March 11, 2022, 1:12pm

Regardless of the “discussion” of database normalization, the fundamental problem that @lurtnowski is having is an issue with understanding how foreign keys work, at least in this context.

Foreign keys reference the primary key on another table. There are two ways that foreign keys can be defined - optional or required.

In your table structure, your FKs are required. This means you MUST enter a valid value in the column or the table insert will fail. To get around that you need to make the FK fields nullable (add the NULL keyword to each field in the table definition.

CREATE TABLE ports (
   port_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
   type ENUM('1','2','3','4','5','6','7','8','9','10','11','12'),
   periphial_storage_id SMALLINT UNSIGNED NULL,  
   server_id SMALLINT UNSIGNED NULL,  
   network_panel_id SMALLINT UNSIGNED NULL,  
   network_module_id SMALLINT UNSIGNED NULL,  
   network_card_id SMALLINT UNSIGNED NULL,  
   network_standard_id SMALLINT UNSIGNED NULL,  
   monitor_id SMALLINT UNSIGNED NULL,  
   kvm_id SMALLINT UNSIGNED NULL,  
   blade_server_id SMALLINT UNSIGNED NULL,  
   chassis_id SMALLINT UNSIGNED NULL,  
   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 ( periphial_storage_id ) REFERENCES periphial_storages( periphial_storage_id ),
   FOREIGN KEY ( server_id ) REFERENCES servers( server_id ),
   FOREIGN KEY ( network_panel_id ) REFERENCES network_panels( network_panel_id ),
   FOREIGN KEY ( network_module_id ) REFERENCES network_modules( network_module_id ),
   FOREIGN KEY ( network_card_id ) REFERENCES network_cards( network_card_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 ( blade_server_id ) REFERENCES blade_servers( blade_server_id ),
   FOREIGN KEY ( chassis_id ) REFERENCES chassises( chassis_id ),
   PRIMARY KEY ( port_id )
);

lurtnowski · March 12, 2022, 5:46am

I added the NULL keyword an am still getting the error at the network_modules with a constraint fail.

When I look at the ports table I notice the NULL thing is working, but it stops after 6

DaveMaxwell · March 12, 2022, 3:32pm

Does the net_module table have a record with a PK of 1 in it?

benanamen · March 12, 2022, 4:44pm

I have reviewed the OP’s DB schema. Aside from the now verified normalization issues, the specific problem in the OP appears to be the Foreign Key is in the Parent table instead of the Child table. Waiting on test data from OP to confirm. Same problem will likely manifest in several other places.