Syntax for creating foreign key index inline in create table statement

user_6315242 0
Viewed 599

I want to create a table bar with a named foreign key constraint backed by a named index. I would like to do that with an inline definition in the create table DDL statement. When looking at the Oracle 19 SQL Language Reference it looks like Oracle should support doing this inline.

When executing the following statements...

create table foo (
    id number not null primary key
);

create table bar (
    id     number not null primary key,
    nick   varchar2(16) not null constraint foo_nick_ck unique using index,
    foo_id number not null constraint foo_fk references foo using index
);

Oracle will respond with [42000][907] ORA-00907: missing right parenthesis and point at the position just before the using index on the last line. If I remove using index it works (but without index being created of course). I've kept the column nick as an example of where it works to create the backing index inline, but for a unique constraint instead of a foreign key constraint.

I am aware that a workaround is to create the backing index in a separate DDL statement, but I would very much like to have it neat and tidy inline.

oracle19c ddl oracle
2 Answers

I am aware that a workaround is to create the backing index in a separate DDL statement, but I would very much like to have it neat and tidy inline.

Unfortunately the syntax does not exist to create indexes inline.

The USING INDEX clause is syntactic sugar: Oracle creates indexes to enforce Primary Key and Unique constraints regardless of whether we include that clause (*). This is because an index is necessary to Oracle's implementation of unique constraints. But there is no such need for an index to enforce a foreign key, it's just desirable.

(*) Unless there's an existing index on the constrained column(s) which Oracle can use.

user_1463250

As per Oracle documentation:

You can specify the using_index_clause only when enabling unique or primary key constraints

You cannot specify this clause for a NOT NULL, foreign key, or check constraint.

user_67481080
Related