mysql约束check无效_MySQL中CHECK约束无效

MySQL中CHECK约束无效

查看表中的约束

方法一：

mysql> SELECT  CONSTRAINT_CATALOG,

CONSTRAINT_SCHEMA,

CONSTRAINT_NAME,

TABLE_SCHEMA,

TABLE_NAME,

CONSTRAINT_TYPE

FROM

information_schema.TABLE_CONSTRAINTS

WHERE

TABLE_NAME='student_course';

+--------------------+-------------------+-----------------+--------------+----------------+-----------------+

| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME     | CONSTRAINT_TYPE |

+--------------------+-------------------+-----------------+--------------+----------------+-----------------+

| def                | test9             | PRIMARY         | test9        | student_course | PRIMARY KEY     |

| def                | test9             | cid_f_key       | test9        | student_course | FOREIGN KEY     |

| def                | test9             | sid_f_key       | test9        | student_course | FOREIGN KEY     |

+--------------------+-------------------+-----------------+--------------+----------------+-----------------+

3 rows in set

方法二：直接查看表的定义语句

mysql> show create table student_course;

+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table          | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                  |

+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| student_course | CREATE TABLE `student_course` (

`sid` varchar(10) NOT NULL COMMENT '学生的id',

`cid` varchar(10) NOT NULL COMMENT '课程的id',

`score` double DEFAULT '0' COMMENT '学生该课程的分数',

PRIMARY KEY (`sid`,`cid`),

KEY `sid_index` (`sid`),

KEY `cid_index` (`cid`),

CONSTRAINT `cid_f_key` FOREIGN KEY (`cid`) REFERENCES `course` (`cid`),

CONSTRAINT `sid_f_key` FOREIGN KEY (`sid`) REFERENCES `student` (`sid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set

mysql>

如下例子：

mysql> create table test_check(

a int not null,

check (a>0)

);

create table test_check_2(

a int check (a>0)

);

Query OK, 0 rows affected

Query OK, 0 rows affected

mysql> insert into test_check(a) values (-98);

insert into test_check_2(a) values (-98);

Query OK, 1 row affected

Query OK, 1 row affected

mysql> select * from test_check;

+-----+

| a   |

+-----+

| -98 |

+-----+

1 row in set

mysql> select * from test_check_2;

+-----+

| a   |

+-----+

| -98 |

+-----+

1 row in set

mysql>

插入负值还是能够正常插入。。。。

再查看表的约束：

mysql> show create table test_check;

+------------+---------------------------------------------------------------------------------------+

| Table      | Create Table                                                                          |

+------------+---------------------------------------------------------------------------------------+

| test_check | CREATE TABLE `test_check` (

`a` int(11) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+------------+---------------------------------------------------------------------------------------+

1 row in set

mysql> show create table test_check_2;

+--------------+---------------------------------------------------------------------------------------------+

| Table        | Create Table                                                                                |

+--------------+---------------------------------------------------------------------------------------------+

| test_check_2 | CREATE TABLE `test_check_2` (

`a` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+--------------+---------------------------------------------------------------------------------------------+

1 row in set

mysql>

也没有发现有CHECK约束。。。

====END====