We use sequence amounts for primary secrets around the tables. You will find some tables where we dont really make use of the primary key for just about any querying purpose. But, we've Indexes on other posts. They are non-unique indexes. The queries begin using these non-primary key posts within the WHERE conditions.

So, I dont really use whatever advantage of getting a principal key on such tables. My knowledge about SQL 2000 was that, previously replicate tables which in fact had some primary key. Otherwise it wouldn't.

I'm using Oracle 10gR2. I must determine if you will find such side-results of getting tables that do not have primary key.

You will find some tables where we dont really make use of the primary key for just about any querying purpose.

Then why have you got a sequence whatsoever, should you never utilize it? Every table should have something which distinctively identifies an archive, it do not need to be a man-made incremental sequence (also known as a surrogate key), maybe it's a mixture of natural key). You will find always queries which access by some type of a distinctive key (candidate key), meaning you'll certainly need a catalog and you'll too result in the index unique.

You will find other advantages of enforcing PK constraints on all persistent (non-temporary) tables:

  1. Making certain that you'll never have duplicate values (that the application will not have the ability to corrupt the DB)
  2. Helping exterior tools perform automatic ER modeling
  3. Last although not minimal - permitting FK constraints!

Off the top my mind, you cannot possess a foreign key with no unique key, so there is no method to link the table with other tables. Altering this afterwards isn't trivial since all code which accesses this table may be affected, either by no longer working whatsoever due to moved references, or by carrying out in a different way. Also, In my opinion we learned at uni (but it was 5+ years back) that unique indexes be more effective for performance, because rows are segmented regardless of their contents.

A table do not need to possess a primary key. There's no impact on the database whatsoever for any table to possess no explicit secrets because every row within the database comes with an implicit unique data point that Oracle ways to use storage and certain internal references. That's the ROWID pseudocolumn. ROWID is a bit of data that distinctively identifies every row inside a database--with a few notable exceptions.

The next query on my small database returns the information proven:


choose rowid from user$ where rownum <= 5

AAAAAKAABAAAAFlAAC

AAAAAKAABAAAAFlAAD

AAAAAKAABAAAAFiAAD

AAAAAKAABAAAAFlAAE

AAAAAKAABAAAAFlAAF

It's not strictly necessary to possess a key on the table. The Oracle10g database which i just queried has 569 system tables which have no primary or unique secrets. It's a decision for that DBA and developer how secrets ought to be produced on database tables. The designers on my small project always create primary secrets no matter their effectiveness or sanity. Like a DBA, I create secrets only where they create sense.

Xox,

Opus