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

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/36779/viewspace-1004090/