CODEWITHSUNDEEP
oracle-databasedatabase-design
alwayshungry
alwayshungry

Reputation: 3

Why no primary key

I have inherited a datababase with tables that lack primary keys. It's an OLTP database. One of the tables in question has ~300k records, and has no primary key implemented, even though examining the rest of the schema tells me one column is used AS a primary key, ie being replicated in another table, with identical name, etc. ie. This is not an 'end of line' table

This database also does not implement FKs.

My question is - is there ANY valid reason for a table (in Oracle for that matter) NOT to have a primary key?

Upvotes: 0

Views: 944

Answers (6)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95532

Any valid reason? I'd say "No"--I'm a database guy--but there are places that insist on using the database as a dumb data store. They usually implement all integrity "constraints" in application code.

Putting integrity constraints into application code isn't usually done to improve performance. In fact, if you built one database that enforces all the known constraints, and you built another with functionally identical constraints only in application code, the first one would almost certainly run rings around the second one.

Instead, application-level constraints usually hope to increase flexibility. (And, in the process, some of the known constraints are usually dropped, which appears to improve performance.) If it becomes inconvenient to enforce certain constraints in order to bulk load some scruffy data, an application programmer can just side-step the application-level constraints for a little while, then clean up the data when it's more convenient.

Upvotes: 1

aneroid
aneroid

Reputation: 15962

Not specific to Oracle but I recall reading about one such use-case where was highly customized for a dam (electricity generation) project, I think. The input data from sensors were in the order 100-1000 per second or something. They were using timestamps for each record so didn't need a primary key (like with logs/logging mentioned in another answer here).

So good reasons would be:

  • Overhead, in the case of high frequency transactions
  • Necessity or Un-necessity in that case
  • "Uniqueness" maintained or inferred by application, not by db
  • In a normalized table, if every record needs to be unique and every field is referenced in other tables, then having a PK additionally adds an index overhead and if the PK would never actually be used in any SQL query (imho, I disagree with this but it's possible). But it should still have a unique index encompassing all the fields.

Bad reasons are infinite :-)

The most frequent bad reason which is actually responsible for the lack of a primary key is when DBs are designed by application/code-developers with little or no DB experience, who want to (or think they should) handle all data constraints in the application.

Upvotes: 2

kangbu
kangbu

Reputation: 196

I think PK is mandatory for almost all cases. Lots of reasons will exist but I'll treat some of them.

  • prevent to insert duplicate rows
  • rows will be referenced, so it must have a key for it

I saw very few cases make tables without PK (e.g. table for logs).

Upvotes: 2

CodeBro
CodeBro

Reputation: 209

There is a small overhead when inserting to a table with an index and you need an index if you have a primary key. Downside of course is that finding a row is very costly.

Upvotes: 0

Thilo
Thilo

Reputation: 262474

You need a primary key to enforce uniqueness for a subset of its columns (useful if you need to refer to individual rows). It also speeds up certain queries because of the index associated to it.

If you do not need that index, or that uniqueness constraint, then you may not need a primary key (the index does not come free).

An example that comes to mind are logging tables, that just record some data (that is never updated or queried for individual records).

Upvotes: 0

Noufal Ibrahim
Noufal Ibrahim

Reputation: 72745

I'm not a db expert but I remember a conversation with a friend who worked in the Oracle apps dept. who told me that this was done to handle emergencies. If there was a problem in some report being generated which you could fix by putting in a row, db level constraints often stand in your way. They generally implemented things like unique primary keys in the application rather than the database. It was inefficient but enough and for them and much more manageable in case of a disaster recovery scenario.

Upvotes: 0

Related Questions