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)
)
);
The foreign key constraint alone does not provide the index on Oracle - one must (and should) be created.
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.
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)).
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.
That isn't allowed. Per the documentation a using_index_clause can only be specified for unique or primary constraints.
Best of luck.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With