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.

Solution 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

Solution 2

There are some tables where we dont really use the primary key for any querying purpose.

Then why do you have a sequence at all, if you never ever use it? Every table must have something that uniquely identifies a record, it need not be an artificial incremental sequence (aka a surrogate key), it could be a combination of natural key). There are always queries which access by some kind of a unique key (candidate key), which means that you'll definitely need an index and you may as well make the index unique.

There are other benefits of enforcing PK constraints on all persistent (non-temporary) tables:

1. Ensuring that you will never ever have duplicate values (that your application won't be able to corrupt the DB)
2. Helping external tools perform automatic ER modeling
3. Last but not the least - allowing FK constraints!

Solution 3

Off the top of my head, you can't have a foreign key without a unique key, so there's no way to link the table to other tables. Changing this after the fact is not trivial since all code which accesses this table might be affected, either by not working at all because of shifted references, or by performing differently. Also, I believe we learned at uni (but this was 5+ years ago) that unique indexes are better for performance, because rows are segmented without regard for their contents.