0

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?

alwayshungry
  • 3
  • 3
  • How are you querying that table? If it just gathers log data, for example, it might not need a primary key. – Thilo Nov 16 '12 at 04:41
  • possible duplicate of [Should a database table always have primary keys?](http://stackoverflow.com/questions/831850/should-a-database-table-always-have-primary-keys) – Ben Nov 16 '12 at 08:41
  • Sorry, I copied the wrong question. I meant to vote to close as a duplicate of http://stackoverflow.com/questions/4621450/should-all-database-tables-have-a-primary-key, which answers your question better. The normal duplicate is http://stackoverflow.com/questions/840162/should-each-and-every-table-have-a-primary-key, but it's less relevant in your case. – Ben Nov 16 '12 at 08:43

6 Answers6

2

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).

kangbu
  • 196
  • 3
2

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.

aneroid
  • 12,983
  • 3
  • 36
  • 66
1

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.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
  • That is exactly what they did in my scenario. All integrity constraints are handled in code. Where possible joins are also performed in code. Surely the database does not pose a performance impact if it were to take on these responsibilities which it is equipped to ( and in my mind, should) do? – alwayshungry Nov 18 '12 at 22:18
0

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.

Noufal Ibrahim
  • 71,383
  • 13
  • 135
  • 169
0

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).

Thilo
  • 257,207
  • 101
  • 511
  • 656
  • The thing is that there are unique constraints on these "primary key" columns, it's just not implemented as PK. And many of these tables are not logging tables - they actively participate in business transactions implemented in this application – alwayshungry Nov 20 '12 at 01:30
0

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.

CodeBro
  • 209
  • 1
  • 4
  • Downside does not apply if you never need to find a (single, particular) row. – Thilo Nov 16 '12 at 04:46
  • that "small overhead" is 2x of inserting time. That is, with an index, insert will be 3x slower. – Florin Ghita Nov 16 '12 at 06:16
  • When you put it like that it doesn't sound so small and that is probably the correct answer - unless the database implementer just forgot to add a primary key. – CodeBro Nov 16 '12 at 06:58