Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can I create a foreign key with Index in one single create table statement? (Oracle)

Tags:

sql

oracle

I tried to create a new table (tableB) with a foreign key constraint to another table (tableA) and just wondering if I can create along with this all constraints and indexes needed. My goal would be to have a single create table statement with no need of an alter table… statement afterwards and no other create index… statement. Is this possible? Thanks for any hint in advance :)

create table tableA
(
   id number
 , constraint tableApk primary key (id)
);

create table tableB
(
   id number
 , constraint tableBfk foreign key (id) references tableA (id)
                       on delete cascade
                       using index (
                         create index tableBfkidx on tableB (id)
                       )
);
like image 256
Ben Avatar asked Feb 12 '16 11:02

Ben


People also ask

Can index be create on foreign key column in Oracle?

The foreign key constraint alone does not provide the index on Oracle - one must (and should) be created.

How many foreign keys we can create in a single table?

A table with a foreign key reference to itself is still limited to 253 foreign key references. Greater than 253 foreign key references are not currently available for columnstore indexes, memory-optimized tables, Stretch Database, or partitioned foreign key tables. Stretch Database is deprecated in SQL Server 2022 (16.

Can a primary key be a foreign key in the same table in Oracle?

It is not possible even in self join case. Another column in a table can refer to primary key of the same table. E.g. create table employee(e_id int primary key, e_name varchar(30), e_mgr int, foreign key (e_mgr) references employee(e_id)).

Can we CREATE INDEX on foreign key?

MySQL requires that foreign key columns be indexed; if you create a table with a foreign key constraint but no index on a given column, an index is created. Information about foreign keys on InnoDB tables can also be found in the INNODB_FOREIGN and INNODB_FOREIGN_COLS tables, in the INFORMATION_SCHEMA database.


1 Answers

That isn't allowed. Per the documentation a using_index_clause can only be specified for unique or primary constraints.

Best of luck.

like image 89