i’m trying to populate a foreign key column ‘boss_id’ from a junction table called ‘clown_boss’ boss_id is new self-referencing foreign key that is meant to hold the ‘id’ value of the clown’s boss. i already have a populated junction table that contains ‘id’ from clown_info table and newly created ‘boss_id’ that also references ‘id’ from clown.
this is what clown_info looks like:
CREATE TABLE clown_info (
id int NOT NULL AUTO_INCREMENT,
name varchar(50) DEFAULT NULL,
gender char(1) DEFAULT NULL,
last_seen varchar(50) DEFAULT NULL,
address varchar(30) DEFAULT NULL,
city varchar(30) DEFAULT NULL,
state char(2) DEFAULT NULL,
appearance varchar(50) DEFAULT NULL,
shirt_colour varchar(20) DEFAULT NULL,
pant_colour varchar(20) DEFAULT NULL,
hat_type varchar(20) DEFAULT NULL,
musical_instrument varchar(20) DEFAULT NULL,
transportation varchar(20) DEFAULT NULL,
boss_id int NOT NULL,
PRIMARY KEY (id),
KEY boss_id_sr_fk (boss_id),
CONSTRAINT boss_id_sr_fk FOREIGN KEY (boss_id)
REFERENCES clown_boss (boss_id)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
i’m using the following code to try and insert values when i get the error
UPDATE clown_info ci SET boss_id = (SELECT boss_id FROM clown_boss cb WHERE ci.id = cb.id) ;
so i just realised that boss_id is meant to reference id in the same table. it’s supposed to be a lesson on self-referencing foreign keys. so i dropped the column recreated it . recreated clown_boss put in the values and still get the same problem when trying to put values into boss_id from clown_boss table
Advertisement
Answer
The problem is because you have set the column ‘boss_id’ in table clown_info as not null.
I assume your ‘boss_id’ column in table clown_boss does accept null value, right? You can’t insert any null value into a ‘not null’ column, that is why it’s showing error.
If you change your column ‘boss_id’ in table clown_info to default null, your issue should be solved. Or you can modify the column boss_id in clown_boss to not null.
You can update your table with the following query
ALTER TABLE clown_info MODIFY boss_id int;
Column are nullable by default if you dont declare it as not null.