ITPub博客

首页 > 应用开发 > IT综合 > 一个关于非空约束enable novalidate的测试

一个关于非空约束enable novalidate的测试

原创 IT综合 作者:cyr1974 时间:2008-05-15 14:33:17 0 删除 编辑


SQL> drop table locations_demo;

Table dropped.

SQL> CREATE TABLE locations_demo
2 ( location_id NUMBER(4)
3 , street_address VARCHAR2(40)
4 , postal_code VARCHAR2(12)
5 , city VARCHAR2(30)
6 , state_province VARCHAR2(25)
7 , country_id CHAR(2)
8 , CONSTRAINT loc_id_pk PRIMARY KEY (location_id));

Table created.

SQL> insert into locations_demo values (1,'east load','100000','beijing','beijing','86');

1 row created.

SQL> insert into locations_demo values (1,'east load','100000','beijing','beijing',null);
insert into locations_demo values (1,'east load','100000','beijing','beijing',null)
*
ERROR at line 1:
ORA-00001: unique constraint (LOC_ID_PK) violated


SQL> insert into locations_demo values (2,'east load','100000','beijing','beijing',null);

1 row created.


SQL> commit;

Commit complete.

SQL> set autotrace off;
SQL> select * from locations_demo;

LOCATION_ID STREET_ADDRESS POSTAL_CODE
----------- ---------------------------------------- ------------
CITY STATE_PROVINCE CO
------------------------------ ------------------------- --
1 east load 100000
beijing beijing 86

2 east load 100000
beijing beijing


SQL> set linesize 300;
SQL> /

LOCATION_ID STREET_ADDRESS POSTAL_CODE CITY STATE_PROVINCE CO
----------- ---------------------------------------- ------------ ------------------------------ ------------------------- --
1 east load 100000 beijing beijing 86
2 east load 100000 beijing beijing

SQL> ALTER TABLE locations_demo
2 MODIFY (country_id CONSTRAINT country_nn NOT NULL enable novalidate);

Table altered.

SQL> ALTER TABLE locations_demo
2 MODIFY (country_id CONSTRAINT country_nn NOT NULL enable validate);
MODIFY (country_id CONSTRAINT country_nn NOT NULL enable validate)
*
ERROR at line 2:
ORA-01442: column to be modified to NOT NULL is already NOT NULL


SQL> alter table locations_demo drop constraint country_nn;

Table altered.

SQL> ALTER TABLE locations_demo
2 MODIFY (country_id CONSTRAINT country_nn NOT NULL enable validate);
MODIFY (country_id CONSTRAINT country_nn NOT NULL enable validate)
*
ERROR at line 2:
ORA-02296: cannot enable (COUNTRY_NN) - null values found


SQL>
[@more@]

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/36779/viewspace-1004090/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-03-16

  • 博文量
    37
  • 访问量
    256945