No, you can safely drop a foreign key referential integrity constraint without affecting any underlying indexes.<\/p>\n
\r\nSQL> create table p(t number primary key);\r\n\r\nTable created.\r\n\r\nSQL> create table c(f number primary key, t number references p(t));\r\n\r\nTable created.\r\n\r\nSQL> create index c_t on c(t);\r\n\r\nIndex created.\r\n\r\nSQL> select index_name from user_indexes where table_name = 'C';\r\n\r\nINDEX_NAME\r\n------------------------------\r\nSYS_C00413196\r\nC_T\r\n\r\nSQL> select constraint_name from user_constraints where r_constraint_name in (select constraint_name from user_constraints where table_name = 'P');\r\n\r\nCONSTRAINT_NAME\r\n------------------------------\r\nSYS_C00413197\r\n\r\nSQL> insert into c values(1,1);\r\ninsert into c values(1,1)\r\n*\r\nERROR at line 1:\r\nORA-02291: integrity constraint (FOO.SYS_C00413197) violated - parent key not found\r\n\r\n\r\nSQL> alter table c drop constraint SYS_C00413197;\r\n\r\nTable altered.\r\n\r\nSQL> select constraint_name from user_constraints where r_constraint_name in (select constraint_name from user_constraints where table_name = 'P');\r\n\r\nno rows selected\r\n\r\nSQL> select index_name from user_indexes where table_name = 'C';\r\n\r\nINDEX_NAME\r\n------------------------------\r\nSYS_C00413196\r\nC_T\r\n\r\nSQL> alter table c add constraint c_t_fk foreign key(t) references p(t);\r\n\r\nTable altered.\r\n\r\nSQL> select constraint_name from user_constraints where r_constraint_name in (select constraint_name from user_constraints where table_name = 'P');\r\n\r\nCONSTRAINT_NAME\r\n------------------------------\r\nC_T_FK\r\n\r\nSQL> select index_name from user_indexes where table_name = 'C';\r\n\r\nINDEX_NAME\r\n------------------------------\r\nSYS_C00413196\r\nC_T\r\n\r\nSQL>\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"No, you can safely drop a foreign key referential integrity constraint without affecting any underlying indexes. SQL> create table p(t number primary key); Table created. SQL> create table c(f number primary key, t number references p(t)); Table created. SQL> create…<\/p>\n