2

I need to design a table and am bit confused to choosing the primary key field.Say for excample am creating a table Student with the following columns

  • ID - Unique Key and Auto increament by 1.
  • RegistrationNumber - Numbers are unique across the table
  • Name
  • RegistrationDate

Now my question is: From the above table we can see both ID and RegistrationNumber are unique, and RegistrationNumber servers the business purpose but ID is used for referring this table with others. So out of these two columns which one I can make as a Primary key.

From the business context Registration number should be the primary key for the table, but since am referring the Id in other tables event I can choose the ID as a PK..

Please suggest me which one should be appropriate.

Thanks in advance.

nvogel
  • 24,981
  • 1
  • 44
  • 82
classic_vmk
  • 495
  • 2
  • 15

4 Answers4

1

You should use a surrogate key for your PK. I would suggest you go with your ID column as the PK.

If the RegistrationNumber column has to be unique, you can always put a unique constraint on it.

Read up on surrogate keys here: Surrogate vs. natural/business keys

Also here: https://en.wikipedia.org/wiki/Surrogate_key

Raj More
  • 47,048
  • 33
  • 131
  • 198
  • Thank you for the suggestion, Is there any strong reason why should I choose the ID as PK.? – classic_vmk Jun 05 '14 at 18:30
  • @classic_vmk I added a couple of links for your perusal. – Raj More Jun 05 '14 at 18:33
  • @classic_vmk: usually ID is an Integer, while registrationNumber is Alphanumeric. This makes ID more suitable as a reference, because it should allow for slightly faster look-ups. – wvdz Jun 05 '14 at 18:38
  • @popovitsj, You can join two tables on any combination of columns between the two, and even on generated columns (RowNumber, etc). But we are talking about designing for the best way to use Ids and PKs here. – Raj More Jun 05 '14 at 19:04
  • @popovitsj : Shall I take your comment in this way.If we make Id (as it referred in other tables)as PK, clustered index will be created on ID column which will help making the lookup faster when join happens. – classic_vmk Jun 05 '14 at 19:05
  • @classic_vmk, no, that's not correct. You can cluster on any column(s), the PK doesn't have to be clustered. The choice of where you put the PRIMARY KEY constraint makes no difference to performance; your choice of indexes does. – nvogel Jun 05 '14 at 20:19
1

If RegistrationNumber is unique at the logical level, you have to put the appropriate constraint on it at the database level, regardless of what you do with ID.

You'll need to make the following design decisions:

  • Do you need ID at all? From the logical perspective, you could just as well reference RegistrationNumber from other tables, although it may not necessarily be a good idea from the physical perspective. For more on reasons why you could decide one way another, please see here.
  • If you decide you do need ID, decide whether it should be PRIMARY KEY or UNIQUE (and the opposite for RegistrationNumber). Logically, it doesn't make a difference, but please be aware than at the physical level MS SQL Server automatically clusters the table on PRIMARY KEY, unless NONCLUSTERED keyword is used.1

1 MS SQL Server will allow you to cluster on any column(s), not just PRIMARY KEY, using special syntax. What I described above is just a default.

Community
  • 1
  • 1
Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
0

The question is tagged as SQL Server. As far as SQL Server is concerned it makes no difference which one is designated PRIMARY KEY and which UNIQUE. SQL Server treats them the same. What matters is how you make use of them.

The distinction of making one key "primary" is essentially a matter of convention and aesthetics rather than anything of fundamental or practical significance. Some people choose to designate the "preferred" or most significant business key as the primary key, others always make it the key referenced by other tables (if any).

nvogel
  • 24,981
  • 1
  • 44
  • 82
  • FYI: Unless NONCLUSTERED keyword is used, MS SQL Server automatically uses primary key as a clustered index, so it is not exactly equivalent to the unique constraint at the physical level. That default is unfortunate, IMO, since many people seem to be unaware of it and consequently overuse clustering. – Branko Dimitrijevic Jun 06 '14 at 09:32
  • The matter of specifying NONCLUSTERED/CLUSTERED vs using the defaults is really just syntactical sugar. Semantically there is no difference: the clustered index goes where you choose, whichever alternative syntax you happen to use to define it. – nvogel Jun 06 '14 at 09:50
  • Sure, but when a person not aware of that reads: _"As far as SQL Server is concerned it makes no difference which one is designated PRIMARY KEY and which UNIQUE."_, they may get a different idea. – Branko Dimitrijevic Jun 06 '14 at 09:53
0

Do not use the resitration number as primary key.

Why?

Because in some years someone may decide to change it - add some digits etc. -and then you are in a world of pain.

Registration codes are varchar columns by standard for this reason - and thus unsuitable for primary key. It is not absolutely unique and non-changing because people do all kinds of decisions in business that change stuff.

TomTom
  • 61,059
  • 10
  • 88
  • 148