Although an index is not mandatory on your foreign keys, if they are not indexed you will cause additional locking when certain Data Manipulation Language statements are executed against the parent table.
Without the index, checking for existing child records when attempting a delete of a master record (cascade or restrict, makes no difference) incurs a full table scan on the child table. Thus, the transaction on the parent table acquires a “share” lock on the child table, thereby, blocking all other transactions from performing DML on the child table for the duration of the transaction.
Unless your parent table is static, you should maintain an index on the child table’s foreign key column(s) if you wish to avoid performance problems that can occur when the parent table is being modified concurrently with other transactions that modify the parent or child table.
