Oracle - Is there any effect of not having a primary key on a table?

Question

We use sequence numbers for primary keys on the tables. There are some tables where we dont really use the primary key for any querying purpose. But, we have Indexes on other columns. These are non-unique indexes. The queries use these non-primary key columns in the WHERE conditions.

So, I dont really see any benefit of having a primary key on such tables. My experience with SQL 2000 was that, it used to replicate tables which had some primary key. Otherwise it would not.

I am using Oracle 10gR2. I would like to know if there are any such side-effects of having tables that dont have primary key.

Answer 1

A table need not have a primary key. There is no effect on the database whatsoever for a table to have no explicit keys because every row in the database has an implicit unique data point that Oracle uses for storage and certain internal references. That is the ROWID pseudocolumn. ROWID is a piece of data that uniquely identifies every row in a database--with some notable exceptions.

The following query on my database returns the data shown:

select rowid from user$ where rownum <= 5;

AAAAAKAABAAAAFlAAC
AAAAAKAABAAAAFlAAD
AAAAAKAABAAAAFiAAD
AAAAAKAABAAAAFlAAE
AAAAAKAABAAAAFlAAF

It is not strictly necessary to have a key on a table. The Oracle10g database that I just queried has 569 system tables that have no primary or unique keys. It is a decision for the DBA and developer how keys should be created on database tables. The developers on my project always create primary keys regardless of their usefulness or sanity. As a DBA, I create keys only where they make sense.

Kind regards,

Opus