Using INDEX DISABLED For Foreign Key

Hi, Using INDEX DISABLED feature For Foreign Key Currently our database is created in following sequence. 1)Create Index for Primary Key 2)Create Primary Key 3)Create Index for Foreign Key 4)Create Foreign Key (Using Alter table) Currently it working fine. ------------------------------------------------------------------------ Now to disable index on foreign key i have done following changes 1)Create Index for Primary Key 2)Create Primary Key 3)Create Index for Foreign Key 4)Create Foreign Key (Using Alter table and passed INDEX DISABLE) Example : ALTER TABLE tbl_TransferOption ADD CONSTRAINT FOREIGN KEY ( MediaSwitchObjectId ) REFERENCES tbl_MediaSwitch ( ObjectId ) CONSTRAINT FK_tbl_TransferOption_MediaSwitchObjectId_tbl_MediaSwitch_Ob INDEX DISABLED ; ------------------------------------------------------------------------------ There are two types of foreign keys in database 1)Foreign keys on which cascade delete 2)Foreign keys on which NO cascade delete ------------------------------Problem---------------------------------------- When i pass INDEX DISABLED parameter for CASCADE DELETE foreign keys it is working fine ,but when i pass INDEX DISABLED parameter for NON CASCADE DELETE foreign keys it gives ERROR Database error: Integrity constraint violation. while some deletion csp call. So my question is there any limitation while using INDEX DISABLED or am i missing some step?

Original post: Hi, Using INDEX DISABLED feature For Foreign Key Currently our database is created in following sequence. 1)Create Index for Primary Key 2)Create Primary Key 3)Create Index for Foreign Key 4)Create Foreign Key (Using Alter table) Currently it working fine. ------------------------------------------------------------------------ Now to disable index on foreign key i have done following changes 1)Create Index for Primary Key 2)Create Primary Key 3)Create Index for Foreign Key 4)Create Foreign Key (Using Alter table and passed INDEX DISABLE) Example : ALTER TABLE tbl_TransferOption ADD CONSTRAINT FOREIGN KEY ( MediaSwitchObjectId ) REFERENCES tbl_MediaSwitch ( ObjectId ) CONSTRAINT FK_tbl_TransferOption_MediaSwitchObjectId_tbl_MediaSwitch_Ob INDEX DISABLED ; ------------------------------------------------------------------------------ There are two types of foreign keys in database 1)Foreign keys on which cascade delete 2)Foreign keys on which NO cascade delete ------------------------------Problem---------------------------------------- When i pass INDEX DISABLED parameter for CASCADE DELETE foreign keys it is working fine ,but when i pass INDEX DISABLED parameter for NON CASCADE DELETE foreign keys it gives ERROR Database error: Integrity constraint violation. while some deletion csp call. So my question is there any limitation while using INDEX DISABLED or am i missing some step? Response: Well, I'm not exactly certain of your issue, but what I think you are saying is when you delete from a parent and have cascade turned off (and also have index disable turned on) you are seeing some of your deletes fail with a constraint violation error. Which is what is supposed to happen. All the index disabled does when you add a constraint is to not force the requirement of the index to be present and enabled. The constraint still needs to be enforced, so you can not have rows in the child table that do not have a corresponding row in the parent, even using index disabled. Here's a blur from the doc on index disabled: "The INDEX DISABLED keywords have no effect on foreign key constraint that you define. The database server enforces that constraint, and issues an error if any subsequent operation on the child table or on the parent table violates the specified foreign key constraint." Which sounds like the issue you are describing, unless I'm not understanding your post. Jacques Renaut IBM Informix Advanced Support APD Team