0

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
1

1 Answer 1

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.