NOT NULL Check is not working

Question

I am creating a table like below.

create table modifications (
    id bigint(20) AUTO_INCREMENT not null primary key,
    type varchar(100),
    user_id bigint(20) null,
    vulnerability_id bigint(20) null
)
alter table modifications
    add constraint fk_user_id foreign key (user_id) references app_user(id)
alter table modifications
    add constraint fk_vulnerability_id foreign key (vulnerability_id) references vulnerabilities(id)
alter table modifications
    add constraint ck_OneIsNotNull check (user_id is not null or vulnerability_id is not null)
alter table modifications
    add constraint ck_OneIsNull check (user_id is null or vulnerability_id is null)

my aim is one of the column should be null and the other should not be null. But when I insert both null or not null it is accepting without throwing errors. Here is the insert query I used.

insert into modifications (type,user_id,vulnerability_id) values('vulnerability',16,65)
insert into modifications (type) values('vulnerability')

Where I went wrong?

It would help if you paste the error you are getting too. – Apr 29 '16 at 03:37 — , Apr 29 '16 at 03:37
I am not getting any error. It is getting inserted. – Rajeshkumar Apr 29 '16 at 03:38 — Rajeshkumar, Apr 29 '16 at 03:38

spencer7593 · Accepted Answer · 2016-04-29T03:58:40.337

MySQL doesn't enforce CHECK constraints.

This is documented in the MySQL Reference Manual.

You can create a BEFORE INSERT and BEFORE UPDATE trigger to enforce constraints like this.

For example:

 DELIMITER $$

 CREATE TRIGGER modifications_bi
 BEFORE INSERT ON modifications
 FOR EACH ROW
 BEGIN
    IF ( NEW.user_id IS NULL AND NEW.vulnerability_id IS NULL )
    OR ( NEW.user_id IS NOT NULL AND NEW.vulnerability_id IS NOT NULL ) THEN
      SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Error: one must be null and one must be non-null';
    END IF;
END$$

DELIMITER ;

If you are running a version before 5.5, the SIGNAL syntax isn't supported. To throw an exception, and you would need to run a SQL Statement that causes an exception.

NOT NULL Check is not working

1 Answers1