Oracle Database Discussions

Oracle 10 g 10.2.0.3

Table with 5 INDEX on 7 columns that have NOT NULL . I Primary key and 1 foreign key constraint.

Steps:

1. Disable constrain types P R U C on the table

2. Disable Triggers

3. Import Data from old schema to new schema

4. Recreate sequence

5. Enable Triggers

6. Enable Constraints R, U, C with NO VALIDATE

7. Enable the only Primary key constraint that was disabled with ENABLE NOVALIDATION
8. Compile Objects

9. Rebuild Index

Then my application cannot see the primary Key error (No Primary Key for Table Table_name:NONE)

When I check index and constrains all are enabled and look ok, on closer lock the 7 columns that are part of constrain and that have NOT NULL are now showing as NULL in PLSQL and Oracle DBA manager studio GUI.

If I try to alter the table to set the columns back to NOT NULL I get :

SQL>ALTER TABLE "schema.Tablename" MODIFY("PARAM_ID" NOT NULL)

ORA-01442: column to be modified to NOT NULL is already NOT NULL


The reason I used ENABLE NOVALIDATE was to reduce the time for the enable of the Primary key constraint and since I knew the data that was been imported was clean.

--1. Drop PK + FK on Table and refrencing table
alter table "schema"."table_name" drop constraint constraint_name;

--2. make all cols Null
ALTER TABLE "schema"."table_name" MODIFY ("PARAM_ID" NULL);

--3. Re run the NOT Null
ALTER TABLE "schema"."table"."table_name" MODIFY ("PARAM_ID" NOT NULL);

--4. Recreate PK

--5. Recreate FK


This Fixed the issue and when I then check DDL for the Table the status of all columns is returned to correct status and application now works fine.

Can someone explian why this happens and do I really need to ENABLE CONSTRAINT VALIDATE after import and NOT use NOVALIDATE?????


Thanks,
Michael