Why constraint fails on preventing the insert statement execution?

Question

I have the following table:

-- laravel.spies definition

CREATE TABLE `spies` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `surname` varchar(255) NOT NULL,
  `agency` varchar(255) NOT NULL DEFAULT 'NO-AGENCY',
  `country_of_operation` varchar(255) DEFAULT NULL,
  `birth_date` date NOT NULL,
  `death_date` date DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_spy` (`name`,`surname`,`agency`,`birth_date`),
) ENGINE=InnoDB AUTO_INCREMENT=2
       DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

And I inserted the following constraint as well:

ALTER TABLE spies add constraint
         death_date_valid check ( spies.death_date = NULL
           || spies.death_date > spies.birth_date );

And The following insert statement inserts a record. In my case, it should not be able to do that:

INSERT INTO spies (name,surname,birth_date,death_date)
      VALUES ('Namae','Myoji','1980-12-01','1970-12-03');

But it inserted the value instead. Do you know why?

What I want to achieve it that death date will have either:

• null value
• any value that is greater than birth_date

Answer 1

= NULL is not what you think. Say IS NULL instead.