Geek Answers Handbook

Oracle Is there an effect of not having a primary key in a table?

We use sequence numbers for primary keys in tables. There are several tables where we really do not use the primary key for any query purpose. But we have pointers to other columns. These are non-unique indexes. Queries use these non-primary key columns in WHERE clauses.

So, I really do not see the benefits of having a primary key in such tables. My experience with SQL 2000 was that it was used to replicate tables with some primary key. Otherwise, it is not.

I am using Oracle 10gR2. I would like to know if there are such side effects with tables that do not have a primary key.

+4
source share
3 answers

There is no primary key in the table. There is no effect for the database that the table does not have explicit keys, because each row in the database has an implicit unique data point that Oracle uses for storage and some internal references. This is the ROWID pseudo-column. A ROWID is a piece of data that uniquely identifies each row in a database — with some notable exceptions.

The following query in my database returns the specified data:

  select rowid from user $ where rownum <= 5;

 AAAAAKAABAAAAFlAAC
 AAAAAKAABAAAAFlAAD
 AAAAAKAABAAAAFiAAD
 AAAAAKAABAAAAFlAAE
 AAAAAKAABAAAAFlAAF

It is not necessary to have a key on the table. The Oracle10g database I just requested has 569 system tables that do not have primary or unique keys. This solution is for the database administrator and the developer how to create keys in the database tables. The developers of my project always create primary keys, regardless of their usefulness or reasonableness. As a database administrator, I only create keys where they make sense.

Yours faithfully,

Opus

+9
source

There are several tables in which we really do not use the primary key for any query purpose.

Then why do you have a sequence at all if you never use it? Each table must have something that uniquely identifies the record ; it should not be an artificial incremental sequence (aka a natural key ). There are always requests that get access to some unique key (the key element of the candidate), which means that you definitely need an index, and you can make the index unique.

There are other advantages to applying PK restrictions to all permanent (non-temporary) tables:

  • Ensuring that you never have duplicate values ​​(that your application cannot corrupt the database)
  • Helping external tools perform automated ER modeling
  • Last but not least, resolving FK restrictions!
+6
source

At the top of my head, you cannot have a foreign key without a unique key, so there is no way to associate a table with other tables. Changing this after the fact is not trivial, because all the code that accesses this table can be affected, either not working at all due to the displaced links, or executing in different ways. In addition, I believe that we learned about uni (but that was 5 years ago) that unique indexes are better for performance because rows are segmented without regard to their contents.

+3
source

More articles:


All Articles