MySQL中CHECK约束无效和在PG中的对比

今天才知道在MySQL中CHECK约束是无效的，例如下面一段代码，在创建表table1时添加了CHECK约束，要求field1字段的值大于零，随后向field1字段插入-1，这明显违反CHECK约束，但这段代码在MySQL中却可以执行成功。

CREATE TABLE table1
(
    field1 INT,
    CHECK (field1 > 0)
);
INSERT INTO table1 VALUES (-1);
SELECT * FROM table1;

运行结果：

+--------+
| field1 |
+--------+
|     -1 |
+--------+
1 row in set (0.00 sec)

解决这个问题有两种方式。如果需要设置CHECK约束的字段值离散的，并且能很容易列举全部可能值，就可以考虑将该字段的类型设置为枚举类型enum()或集合类型set()。比如性别字段可以这样设置，插入枚举值以外值的操作将不被允许：

CREATE TABLE table1
(
    gender ENUM('男', '女')
);
INSERT INTO table1 VALUES ('秀吉');-- 此次插入操作将失败

不过enum()类型和set()类型之间还有些小区别，官方文档上有说明。

如果需要设置CHECK约束的字段是连续的，或者列举全部值很困难，比如正实数或正整数，那就只能用触发器来代替约束实现数据有效性了。下面这段代 码创建了一个叫做TestField1_BeforeInsert的约束器，它将保证新插入的数据中field1字段的值不小于零。

[sql] view plain copy print?

DELIMITER $$

CREATE TRIGGER TestField1_BeforeInsert BEFORE INSERT ON table1
FOR EACH ROW
BEGIN
    IF NEW.field1 < 0 THEN
        SET NEW.field1 = 0;
    END IF;
END$$

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

同时在postgresql中进行check约束的测试:

CREATE TABLE OrderItems
(
  order_num  int          NOT NULL ,
  order_item int          NOT NULL ,
  prod_id    char(10)     NOT NULL ,
  quantity   int          NOT NULL check(quantity>0),
  item_price decimal(8,2) NOT NULL 
);
ar_test=# \d OrderItems;
       Table "public.orderitems"
   Column   |     Type      | Modifiers 
------------+---------------+-----------
 order_num  | integer       | not null
 order_item | integer       | not null
 prod_id    | character(10) | not null
 quantity   | integer       | not null
 item_price | numeric(8,2)  | not null
Check constraints:
    "orderitems_quantity_check" CHECK (quantity > 0)

#插入一条满足check约束的数据没有问题
ar_test=# insert into  orderitems values(20005,1,'BR01',100,5.49);
INSERT 0 1
#插入一条违反check约束的数据直接报错
ar_test=# insert into  orderitems values(20005,1,'BR01',0,5.49);
ERROR:  new row for relation "orderitems" violates check constraint "orderitems_quantity_check"
DETAIL:  Failing row contains (20005, 1, BR01      , 0, 5.49).
#可以看到刚刚正确插入的数据,而违反check约束的语句的数据没有插入==>
ar_test=# select * from orderitems;
 order_num | order_item |  prod_id   | quantity | item_price 
-----------+------------+------------+----------+------------
     20005 |          1 | BR01       |      100 |       5.49
(1 row)