0

How do I index a foreign key(column cnt004_id) for a table?

There is a table ChildTable:

CREATE TABLE ChildTable (
    id NUMBER GENERATED as IDENTITY,
    cnt004_id int not null,
    tariff_dist NUMBER not null,
    foreign key (cnt004_id) references ParentTable(id)
);

I don't have any foreign key indexes created.

user19296905
  • 21
  • 1
  • Why do you expect that a `FOREIGN KEY` column needs an index? The column it references on the parent table must have either a `PRIMARY KEY` or `UNIQUE` constraint on it which will be backed by a `UNIQUE` index but there is no requirement for the referencing column on the child table to have an index. – MT0 Jul 21 '23 at 19:56
  • In parent table - PRIMARY KEY – user19296905 Jul 21 '23 at 20:33
  • It's important to index the child column or deletes on the parent table will be terribly slow, requiring a full table scan of the child for every row deleted as it does its internal check on child rows to ensure it isn't about to orphan them. In a multi-user situation this will likely cause locking issues for the app for the duration. – Paul W Jul 21 '23 at 22:19
  • Paul W is correct. MT0 is apparently not aware of the issue at hand. Refer to " Foreign Keys, Indexes and Locking (Doc ID 1945574.1)" Refer also to this question https://stackoverflow.com/questions/4127206/do-i-need-to-create-indexes-on-foreign-keys-on-oracle – Brian Fitzgerald Jul 23 '23 at 03:38

2 Answers2

1

With a create index command.

Sample parent table:

SQL> create table parenttable
  2    (id int primary key);

Table created.

Your child table (this code isn't changed in any way):

SQL> CREATE TABLE ChildTable (
  2      id NUMBER GENERATED as IDENTITY,
  3      cnt004_id int not null,
  4      tariff_dist NUMBER not null,
  5      foreign key (cnt004_id) references ParentTable(id)
  6  );

Table created.

Create index:

SQL> create index i1_ct_cnt004 on childtable (cnt004_id);

Index created.

SQL>

Because, unlike primary key constraints - for which Oracle automatically creates unique index that supports it (if it doesn't exist):

SQL> select index_name, uniqueness, constraint_index
  2  from user_indexes where table_name = 'PARENTTABLE';

INDEX_NAME                     UNIQUENES CON
------------------------------ --------- ---
SYS_C0010080                   UNIQUE    YES

SQL>

Oracle doesn't do the same for foreign key constraints, so - you have to do it yourself (if you want to have that column indexed).

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
0

As already mentioned, Oracle does not automatically index foreign keys. If you want to index a foreign key, you need to create an index explicitly using the CREATE INDEX statement. In Oracle Database, if you do not index the child column of a foreign key, deleting rows from the parent table will be very slow.

You can use Devart's dbForge Studio for Oracle to create an index which is a powerful database tool that optimizes query performance tuning. Using indexes in queries is an effective way to improve query performance, accelerate data retrieval operations, and reduce query response time. With the help of indexes, you can access and sort rows in the query much easier and faster.

Jessica Williams
  • 59
  • 4