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