Name: Constraint fails to prevent the insertion. Do you know why?
Rating: 4.3 (7341 reviews)
Author: pcmagas

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

Dimitrios Desyllas - Jul 12 '23 -

- Dev Community

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:

Death date is null
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?