Rajesh Rajesh - 1 year ago 59
SQL Question

NOT NULL Check is not working

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?

Answer Source

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:


 CREATE TRIGGER modifications_bi
 BEFORE INSERT ON modifications
    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;


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.