Constraint fails to prevent the insertion. Do you know why?

Can you help me with that?

https://dba.stackexchange.com/q/329226/118215

What I want to do is to check that insertion must happen if either:

1. Death date is null
2. Death date is greater than birth date

But is seems that constraint does not prevent the insertion of:

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

At this table:

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;

Do you know why?

Comments

[Vasyl Pominchuk]

Maybe it's too late, but :)

try CHECK constraint
mariadb.com/kb/en/constraint/#chec...

CONSTRAINT `death_date_validation` `death_date` IS NULL OR `death_date` > `birth_date`