setting a foreign key to an empty string

Question

I'm trying to set a foreign key to an empty string but i keep getting this error.

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`msis230`.`deptchair`, CONSTRAINT `deptchair_ibfk_1` FOREIGN KEY (`PROFESSORID`) REFERENCES `professor` (`PROFID`))

this is my code:

INSERT INTO DEPTCHAIR (PROFESSORID, DEPTID)
VALUES ('SJ001', 'MSIS');
INSERT INTO DEPTCHAIR (PROFESSORID, DEPTID)
VALUES ('', 'BIO');
INSERT INTO DEPTCHAIR 
VALUES ('JS001', 'PHY');
INSERT INTO DEPTCHAIR 
VALUES ('NS001', 'MKT');
INSERT INTO DEPTCHAIR 
VALUES ('', 'ECO');

• Mike Petri: Do you have a record in the professor table with a blank for PROFID?
• red Sword: @MikePetri I have set PROFID as a not null primary key.

Answer 1

There are two approaches you can take

APPROACH #1 : Add to professor table

INSERT INTO professor (PROFID) VALUES ('');

Then, all INSERTs to DEPTCHAIR will work.

APPROACH #2 : Disable Foreign Key Check

SET foreign_key_checks = 0;
INSERT INTO DEPTCHAIR (PROFESSORID, DEPTID)
VALUES ('', 'BIO');
SET foreign_key_checks = 1;

Answer 2

Even '' is a value that has to exist in the parent table.

If you don't want to add that to your professor table use NULL instead like Akina already said in the comments

INSERT INTO DEPTCHAIR (PROFESSORID, DEPTID)
VALUES ('SJ001', 'MSIS');
INSERT INTO DEPTCHAIR (PROFESSORID, DEPTID)
VALUES (NULL, 'BIO');
INSERT INTO DEPTCHAIR 
VALUES ('JS001', 'PHY');
INSERT INTO DEPTCHAIR 
VALUES ('NS001', 'MKT');
INSERT INTO DEPTCHAIR 
VALUES (NULL, 'ECO');

Answer 3

I'm trying to set a foreign key to an empty string

An empty string is still a string and, therefore, a valid value that will be looked up in the referenced table.

To "get rid" of the field, you must set it to NULL.

INSERT INTO DEPTCHAIR (PROFESSORID, DEPTID) VALUES ( '', 'BIO' );

Assuming you could create this row, what would it represent?

If you want to denote that this department has no chair, then the row should not exist at all.
A Left Join query between Department and this table would show those departments without chairs.

select * 
from department 
order by 1 ; 
+------+
| dept | 
+------+
| BIO |
| ECO |
| MKT |
| MSIS | 
| PHY |
+------+
select * 
from deptchair 
order by 1, 2 ; 
+------+-------+
| dept | chair | 
+------+-------+
| MKT | NS001 | 
| MSIS | SJ001 | 
| PHY | JS001 |
+------+-------+
select 
 d.code 
from department d
left join deptchair dc 
 on d.dept = dc.dept 
where dc.chair is null 
order by 1 ;
+------+
| dept | 
+------+
| BIO |
| ECO |
+------+