Record non-empty constraints for deleting Oracle table fields at one time

Record the non-null constraint for deleting Oracle table fields at a time. A table structure specifies that a field (such as cno) is not null, and the check not null constraint is added. Now we need to remove the not null constraint so that the cno field can be inserted with a null value. The following statement is executed:

SQL code 1. alter table tableName drop constraints not_null_cons_cno; 2. alter table tableName modify cno null;

 

After the statement is executed, the null value cannot be inserted. The following error is reported: ORA-01451: column to be modified to NULL cannot be modified to NULL queries the Oracle documentation, which is described as follows: Cause: the column may already allow NULL values, the not null constraint is part of a primary key or check constraint. action: if a primary key or check constraint is enforcing the not null constraint, then drop that constraint. then I found that this field and other fields constitute a unique composite index. So I modified this index and deleted the cno field from it.

1. delete the primary key. SQL code alter table tableName drop constraints pk_tableName; 2. delete a unique index; SQL code drop index un_idx_table_name; 3. create a primary key (excluding the cno field); SQL code alter table tableName add constraint PK_tableName primary key (DNO, TNO, YEAR, PNO );

 

4. Create a unique index (excluding the cno field ).

SQL code create unique index ix_tableName on tableName (DNO, TNO, YEAR, PNO );

 

Step 1 reported the error: ORA-02437: cannot validate (TBOSDATA. PK_VOU_NUM)-primary key violated which indicates that the primary key or unique key has duplicate records. (My uniqueness constraints apply to dno, tno, cno, year, And pno fields. Find duplicate records:

SQL code select * from test vnt where (vnt. dno, vnt. tno, vnt. year, vnt. pno) in (select t. dno, t. tno, t. year, t. pno from test t group by t. dno, t. tno, t. year, t. pno having count (*)> 1) order by dno, tno, year, pno;

 

Delete or modify the repeat record, and execute the above 3, 4 again.