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


Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!