-1

In MySql, if no primary key is defined for a table, MySQL associates a default clustered index with it which functions like a primary key. What is the case with Oracle table having no primary key ?

Prashant
  • 21
  • 3
  • your first statement is not exactly correct: https://stackoverflow.com/questions/13779138/mysql-how-do-you-create-a-clustered-index An Oracle database uses heap tables by default; if present, Oracle database always uses the primary key as the clustering key. – Mitch Wheat Feb 07 '21 at 07:21

2 Answers2

2

MySQL (or rather innodb, which is the storage engine) does not store data the way Oracle normally does.

  • in MySQL (innodb), data is always stored in what is called a "clustered index". Even though it is called an index, all the data is stored there, not just the indexed columns. The data is stored in the order of the indexed column(s). If there is no appropriate primary or unique key, a synthetic key is used.
  • in the Oracle database, by default a table is stored as a heap, meaning rows are not stored in any particular order. Heap tables can exist without an index of any kind.
  • Oracle allows us to define index organized tables which work like the MySQL tables, but they must have a primary key.

So bottom line, without a primary (or non-null unique) key, the only way to uniquely identify a row in an Oracle heap table is the ROWID pseudocolumn. However, an index on ROWID is not allowed.

Stew Ashton
  • 1,499
  • 9
  • 6
0

In Oracle nothing happens with a table with no primary key. There's no key or index created automatically.

D. Mika
  • 2,577
  • 1
  • 13
  • 29