Is it correct command to create foreign key with index creation also? I got ORA-01735: invalid ALTER TABLE option

Question

Is it correct or not?

alter table test
add constraint test_fk foreign key (prim_id)
references prim (prim_id)
using index 
(create index i_test_prim_fk 
on test(prim_id) tablespace index01)
on delete cascade
/

maybe using index not allowed here?

using index * ERROR at line 4: ORA-01735: invalid ALTER TABLE option

tried create fk expected error

Answer 1

The best way to check is to read the documentation.

ALTER TABLE has the syntax:

constraint_clauses::=

out_of_line_ref_constraint::=

references_clause::=

constraint_state::=

using_index::=

and then the CREATE INDEX syntax is here.

The using_index clause specifies the following restrictions:

using_index_clause

You can specify the using_index_clause only when enabling unique or primary key constraints. You can specify the clauses of the using_index_clause in any order, but you can specify each clause only once.

If you specify schema.index, then Oracle attempts to enforce the constraint using the specified index. If Oracle cannot find the index or cannot use the index to enforce the constraint, then Oracle returns an error.

If you specify the create_index_statement, then Oracle attempts to create the index and use it to enforce the constraint. If Oracle cannot create the index or cannot use the index to enforce the constraint, then Oracle returns an error.

If you neither specify an existing index nor create a new index, then Oracle creates the index. In this case:

• The index receives the same name as the constraint.
• If table is partitioned, then you can specify a locally or globally partitioned index for the unique or primary key constraint.

Restrictions on the using_index_clause

The following restrictions apply to the using_index_clause:

• You cannot specify this clause for a view constraint.
• You cannot specify this clause for a NOT NULL, foreign key, or check constraint.
• You cannot specify an index (schema.index) or create an index (create_index_statement) when enabling the primary key of an index-organized table.
• You cannot specify the parallel_clause of index_attributes.
• The INDEXTYPE IS ... clause of index_properties is not valid in the definition of a constraint.

No, your statement is not valid as:

1. The ON DELETE CASCADE should be before USING INDEX.
2. USING INDEX should create a unique or primary key index.
3. The unique index should be on prim and not test.

You can use two statements:

ALTER TABLE prim
  ADD CONSTRAINT pk__prim__prim_id
    PRIMARY KEY (prim_id)
    USING INDEX (CREATE UNIQUE INDEX i__prim__prim_id__u ON prim(prim_id))
/

alter table test
  add constraint test_fk
      foreign key (prim_id)
      references prim (prim_id)
      on delete cascade
/