How to bypass the check constraint in sql

Schema

   create table course
        (course_id  varchar(8), 
         title      varchar(50), 
         dept_name  varchar(20),
         credits    numeric(2,0) check (credits > 0),
         primary key (course_id),
         foreign key (dept_name) references department (dept_name)
            on delete set null
        );

I wanted to add this data to the table, but I unable to add 0 credits.

“CS-001”, titled “Weekly Seminar”, with 0 credits

Insert Query

INSERT INTO `course`(`course_id`, `title`, `credits`) VALUES ('CS-001','Weekly Seminar','0');

Query result:

Is there any other way to insert the same data without altering the table structure?

The foreign_key_checks option affects foreign key enforcement, not check constraint enforcement.

mysql> set foreign_key_checks=0;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `course`(`course_id`, `title`, `credits`) VALUES ('CS-001','Weekly Seminar','0');
ERROR 3819 (HY000): Check constraint 'course_chk_1' is violated.

You must use ALTER TABLE, but you don't have to drop the constraint.

mysql> alter table course alter check course_chk_1 not enforced;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> INSERT INTO `course`(`course_id`, `title`, `credits`) VALUES ('CS-001','Weekly Seminar','0');
Query OK, 1 row affected (0.00 sec)

But once you insert that row, you cannot re-enable the check constraint, because rows will be re-checked as the constraint is enabled.

mysql> alter table course alter check course_chk_1 enforced;
ERROR 3819 (HY000): Check constraint 'course_chk_1' is violated.

You could subsequently delete or update the rows that violate the check constraint, then re-enable the constraint.

If you need to be able to insert zero values to the credits column, then it seems like that check (credits > 0) isn't the right choice for that column. Maybe it needs to be check (credits >= 0).