Architecture
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 want to add this data to the table but cannot add 0 points.
"CS-001", titled "Weekly Seminar", 0 Credits
Insert query
INSERT INTO `course`(`course_id`, `title`, `credits`) VALUES ('CS-001','Weekly Seminar','0');
search result:
Is there any other way to insert the same data without changing the table structure?
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
foreign_key_checksoption 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 do not 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 the row is inserted, the check constraint cannot be re-enabled because the row will be re-checked when the constraint is enabled.
You can subsequently delete or update the row that violates the check constraint and re-enable the constraint.
If you need to be able to insert a zero value into the
creditscolumn, thencheck (credits > 0)doesn't seem to be the right choice for that column. Maybecheck(credits >= 0)required.