db2 informational constraints

db2 informational constraints 笔记

不强制检查的约束：create table applicants(ap_no int not null,

gender char(1) not null,

constraint genderok 

check (gender in ('M','F'))

not enforced 

enable query optimization,

age int not null,

constraint ageok

check (age between 1 and 80)

not enforced 

enable query optimization

);

[db2inst@www ~]$ db2 "insert into applicants values ('5','C','10')"

DB20000I  The SQL command completed successfully.

强制检查的约束：

create table applicants(ap_no int not null,

gender char(1) not null,

constraint genderok 

check (gender in ('M','F'))

not enforced not trusted

enable query optimization,

age int not null,

constraint ageok

check (age between 1 and 80)

not enforced 

enable query optimization

);

[db2inst@www ~]$ db2 "insert into applicants values ('5','C','10')"

DB21034E  The command was processed as an SQL statement because it was not a 

valid Command Line Processor command.  During SQL processing it returned:

SQL0545N  The requested operation is not allowed because a row does not 

satisfy the check constraint "DB2INST.APPLICANTS.GENDEROK".  SQLSTATE=23513

enable query optimization影响查询结果，不符合约束的记录被过滤掉：

[db2inst@www ~]$ db2 "SELECT * FROM applicants WHERE GENDER='C'"

AP_NO       GENDER AGE        

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

  0 record(s) selected.

禁用查询优化功能，查出实际结果：

[db2inst@www ~]$ db2 "alter table applicants alter check genderok disable query optimization"

DB20000I  The SQL command completed successfully.

[db2inst@www ~]$ db2 "SELECT * FROM applicants WHERE GENDER='C'"

AP_NO       GENDER AGE        

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

          5 C               10

  1 record(s) selected.

结论：当应用程序能够保证插入表中的数据是对的，那么建议使用not enforced ,如果不能建议使用 enfored关键字

现有 表 及 约束定义如下： CREATE TABLE EMPLOYEE(     EMPID INTEGER NOT NULL,     LNAME VARCHAR(20),     GENDER CHAR(1) NOT NULL,     CONSTRAINT GENDER_OK          CHECK(GENDER IN ('M', 'F')) NOT ENFORCED         ENABLE QUERY OPTIMIZATION ); 希望以下 SQL 可以成功执行： INSERT INTO EMPLOYEE VALUES (1, 'SMITH', 'M'), (2, 'DOE', 'F'),(3, 'JONES', 'U'); 但是，希望 SQL 结果如下： SELECT * FROM EMPLOYEE; EMPID     LNAME     GENDER     --------  --------  ---------  1         Smith     M          2         Doe       F                 2 record(s) selected 而不是 三条 记录。 EMPID     LNAME     GENDER     --------  --------  ---------  1         SMITH     M          2         DOE       F          3         JONES     U          3  record(s) selected 并且，确保 一下SQL 结果为 0条记录： SELECT * FROM EMPLOYEE WHERE GENDER = 'U'; EMPID     LNAME     GENDER     --------  --------  ---------  0 record(s) selected 如何修改表定义，才能使 SELECT * FROM EMPLOYEE;得到预期的结果？  EMPID     LNAME     GENDER     --------  --------  ---------  1         Smith     M          2         Doe       F                 2 record(s) selected

因为你建表时用了‘ENABLE QUERY OPTIMIZATION’，所以optimizor总是假设constraint为真。

用alter table disable query optimization结果就会不一样了。