Rajesh Rajesh - 7 months ago 18
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

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.