Can a foreign key be NULL?

I have


I set the foreign key to default to NULL (to be set later by me)

CREATE TABLE power_panels (
   power_panel_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   pdu_id TINYINT UNSIGNED DEFAULT NULL,

I get this error
Array
(
[Name] => fgy
[Manufacturer] => Test Manufacturer
[Model] => Test Model
[Phase] => 1
[Slots] => 2
[Columns] => 2
[pdu_id] =>
[Width] => 3
[Height] => 4
[Voltage] => 6
[Notes] =>
)
INSERT INTO power_panels (name,pdu_id,manufacturer,model,slots,columns,width,height,voltage,phase,created_by,created_date,notes ) VALUES ( ‘fgy’,0,‘Test Manufacturer’,‘Test Model’,2,2,3,4,‘6’,1,‘ssam@industechnology.com’,CURRENT_TIMESTAMP,‘’)ERROR: Could not able to execute INSERT INTO power_panels (name,pdu_id,manufacturer,model,slots,columns,width,height,voltage,phase,created_by,created_date,notes ) VALUES ( ‘fgy’,0,‘Test Manufacturer’,‘Test Model’,2,2,3,4,‘6’,1,‘ssam@industechnology.com’,CURRENT_TIMESTAMP,‘’). SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (dct.power_panels, CONSTRAINT power_panels_ibfk_1 FOREIGN KEY (pdu_id) REFERENCES pdus (pdu_id))
you can see, pdu_id is not set,
Can the foreign key be NULL?

Is the power_panel(s) table defining power panels (items) that can be selected/picked or it is holding instances of power panels that are related to a specific pdu?

If it’s the first case, you are doing this wrong. The power_panel(s) table should hold the unique/one-time information about the different power panels that can be picked for use. If it’s the second case, you would never be inserting an instance of a power panel, related to a specific pdu, unless you already know which pdu it was for. Also, for the second case, you would be inserting the power_panel_id of a previously defined power panel (the first case usage) into a table holding the pdu_power_panel relationships, not all the repeated characteristics of a selected power panel.

2 Likes

i love this so much – it sounds exactly like my Cantonese boss

but to answer the general question you asked in your title – yes of course a foreign key can be null*

* if you want it to be, and if you know how to do it