I am presently creating a completely new database. In class, we always learned to place a principal type in each table.

I just read lots of articles/discussions/newsgroups posts stating that it's easier to use unique constraint (also known as unique index for many db) rather than PK.

What your perspective?

Are you able to provide references to those articles?

I see pointless to alter the tried and tested techniques. In the end, Primary Secrets really are a fundamental design feature of relational databases.

Using UNIQUE for everyone exactly the same purpose sounds really hackish in my experience. What's their rationale?

Edit: My attention got attracted to this old answer. Possibly the discussion that you simply read regarding PK versus. UNIQUE worked with individuals making something a PK for that sole reason for enforcing originality onto it. The response to this really is, If it's a vital, make it key, otherwise allow it to be UNIQUE.

A Principal Secret is really only a candidate key that doesn't permit NULL. As a result, in SQL terms - it's the same as every other unique key.

However, for the non-theoretical RDBMS's, you ought to have a principal Key - I have never heard it contended otherwise. In the event that Primary Secret is a surrogate key, then you definitely should also have unique constraints around the natural key(s).

The key bit just to walk away with is you must have unique constraints on all the candidate (whether natural or surrogate) secrets. Next select the one that's simplest to reference inside a Foreign Key to become most of your Key*.

It's also wise to possess a clustered index*. this could become your Primary Key, or perhaps a natural key - but it is not needed to become either. You need to pick your clustered index according to query usage on the table. Much more doubt, the main Key isn't a bad first choice.

  • Climax technically only needed to consult a distinctive type in an overseas key relationship, it's recognized standard practice to greatly favor the main key. Actually, I would not be surprised if some RDBMS only allow primary key references.

  • Edit: It has been stated that Oracle's term of "clustered table" and "clustered index" will vary than Sql Server. The same as what I am talking about in Oracle-ose is definitely an Index Purchased Table which is suggested for OLTP tables - which, I believe, will be the primary focus of SO questions. I suppose if you are accountable for a sizable OLAP data warehouse, you need to curently have your personal opinions on database design and optimisation.

It might be unusual denormalization that will make you need to possess a table with no primary key. Primary secrets have unique constraints instantly simply by their character because the PK.

A distinctive constraint could be used when you wish to ensure originality inside a column additionally towards the primary key.

The rule of also have a PK is a great one.


A principal key is simply a candidate key (unique constraint) designated for special therapy (automatic development of indexes, etc).

I expect the people that argue against them see pointless to deal with one key in a different way than another. This is where I stand.

[Edit] Apparently I can not comment even by myself answer without 50 points.

@chris: I do not think there's any harm. "Primary Key" is actually just syntactic sugar. I personally use them constantly, however i certainly don't believe they are needed. A distinctive key is needed, yes, although not always a principal Key.