Mysql Set NULL value in Foreign key column

Question

I'm trying to set a NULL value in the deserved_slope_2 column when the user edits the value in a form. Unfortunately this column is a foreign key that links to a primary key (auto increment index) of a column of another table.

When running the request I get:

Error Number: 1452

Cannot add or update a child row: a foreign key constraint fails (`game_skisimulation`.`game_created_lifts`, CONSTRAINT `fk_game_created_lifts_game_created_slopes2` FOREIGN KEY (`deserved_slope_2`) REFERENCES `game_created_slopes` (`id_created_slopes`) ON DE)

UPDATE game_created_lifts SET deserved_slope_2 = '0' WHERE id_created_lifts = '200' LIMIT 1

I've read that this is because the NULL ID doesn't exist in the referred table. Unfortunately it seems that I cannot set NULL either in this column:

A primary key column cannot contain NULL values.

How can I solve this problem? I want to set NULL in the deserved_slope_2 column (to reset it).

-- Table structure for table game_created_lifts

    CREATE TABLE `game_created_lifts` (
  `id_created_lifts` int(11) NOT NULL,
  `id_player` int(11) NOT NULL,
  `deserved_slope_1` int(11) DEFAULT NULL,
  `deserved_slope_2` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- RELATIONS FOR TABLE `game_created_lifts`:
--   `deserved_slope_1`
--       `game_created_slopes` -> `id_created_slopes`
--   `deserved_slope_2`
--       `game_created_slopes` -> `id_created_slopes`

-- Table structure for table game_created_slopes

CREATE TABLE `game_created_slopes` (
  `id_created_slopes` int(11) NOT NULL,
  `id_player` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The primary key can't contain `NULL`, but a foreign key can. You just need to declare the `deserved_slope_1` column `NULL` in the `CREATE TABLE`. — Barmar, Apr 11 '16 at 19:22
If that update in the first block is the one you are trying `deserved_slope_2 = '0'` is not an attempt to set the field to `NULL`; it is an attempt to set it to `0`, which is not the same. — Uueerdo, Apr 11 '16 at 19:24

score 0 · Answer 1 · answered Apr 11 '16 at 19:22

Setting a value to null in a column that has a foreign key constraint which depends on a unique and not null column in should be pretty straight-forward

UPDATE game_created_lifts SET deserved_slope_2 = NULL WHERE id_created_lifts = '200' LIMIT 1;

Go to SQL Fiddle to see it online.

Test data:

create table a (a int primary key);
create table b (a int references a(a));

insert into a(a) values (1);
insert into b(a) values (1);

update b set a = null;

Query:

select * from b

Result:

(null)

Mysql Set NULL value in Foreign key column

1 Answers1