If Oracle Data is not deleted, change the field type.

-- Modify the info_cus_complex_tax sequence number type
Alter table info_cus_complex_tax add AA number;
-- Add/Modify columns
Alter table info_cus_complex_tax modify no null;
-- Disable Constraints
Alter table info_cus_complex_tax disable constraint pk_info_complex_tax1;
Update info_cus_complex_tax set AA = NO, NO = NULL;
Commit;
Alter table info_cus_complex_tax modify no number (4 );
Update info_cus_complex_tax set no = AA, AA = NULL;
Commit;
Alter table info_cus_complex_tax drop column AA;
Alter table info_cus_complex_tax modify no not null;
-- Enable constraints

Alter table info_cus_complex_tax enable constraint pk_info_complex_tax1;

Problem: When a constraint is disabled or deleted in the database, the corresponding index is not deleted. As a result, the unique constraint is still reported when no column value is assigned to column AA.

Normal condition: When the constraint is disabled/deleted, the corresponding index will be deleted. When the constraint is enabled/created, it will be re-created.

Possible causes:

When you create a constraint, Oracle automatically creates the corresponding index. The two are associated. When the constraint is disabled or deleted, the corresponding index is deleted, when you enable/create an index, the corresponding index will be created. However, if you create an index first and then create a constraint, even if the two names are consistent with the associated fields, they do not have a relationship. It looks the same as above, the above problem occurs.

Solution 1: Determine whether the index exists

Script:

Declare
V_count varchar2 (2 );
Begin
-- Modify the info_cus_complex_tax sequence number type
Execute immediate 'alter table info_cus_complex_tax add AA number ';
-- Add/Modify columns
Execute immediate 'alter table info_cus_complex_tax modify no null ';
-- Disable Constraints
Execute immediate 'alter table info_cus_complex_tax disable constraint pk_info_complex_tax1 ';

Select count (T. Status) into v_count from user_indexes t where T. index_name = 'pk _ info_complex_tax1 ';
If (v_count> 0) then
Execute immediate 'drop index pk_info_complex_tax1 ';
End if;

Execute immediate 'Update info_cus_complex_tax set AA = No, no = null ';
Execute immediate 'alter table info_cus_complex_tax modify no number (4 )';

Execute immediate 'Update info_cus_complex_tax set no = AA, AA = null ';
Commit;
Execute immediate 'alter table info_cus_complex_tax drop column ';
Execute immediate 'alter table info_cus_complex_tax modify no not null ';
-- Enable constraints
Execute immediate 'alter table info_cus_complex_tax enable constraint pk_info_complex_tax1 ';

End;

Solution 2: cascade Deletion
Alter table tvehicle drop constraint check_only cascade drop index;
Re-create Index