2

What's the best practice for handling primary keys using an ORM over Oracle or SQL Server?

Oracle - Should I use a sequence and a trigger or let the ORM handle this? Or is there some other way ?

SQL Server - Should I use the identifier data type or somehow else ?

John Saunders
  • 160,644
  • 26
  • 247
  • 397
user137348
  • 10,166
  • 18
  • 69
  • 89
  • There is no product named "MsSQL". It's "SQL Server". It will be easier for people to find your question if you use the correct terms. – John Saunders Aug 15 '09 at 21:07

4 Answers4

1

If you are using any kind of ORM, I would suggest you to let it handle your primary keys generation. In SQL Server and Oracle.

John Saunders
  • 160,644
  • 26
  • 247
  • 397
Pablo Santa Cruz
  • 176,835
  • 32
  • 241
  • 292
1

With either database, I would use a client-generated Guid for the primary key (which would map to uniqueidentifier in SQL Server, or RAW(20) in Oracle). Despite the performance penalty on JOINs when using a Guid foreign key, I tend to work with disconnected clients and replicated databases, so being able to generate unique IDs on the client is a must. Guid IDs also have advantages when working with an ORM, as they simplify your life considerably.

MusiGenesis
  • 74,184
  • 40
  • 190
  • 334
  • What if there is a natural key available? – John Saunders Aug 15 '09 at 21:47
  • @John: I would definitely use a natural key if there were one available. I'm just used to there being no natural key available. :) – MusiGenesis Aug 15 '09 at 22:03
  • For SQL Server, using GUIDs a primary key is a REALLY REALLY bad choice, since by default, the priamry key will also be the clustering key for the table, and using GUIDs for htat leads to god-awful index fragmentation and poor performance. – marc_s Aug 16 '09 at 11:14
  • @marc_s: this GUID vs. int PK debate is REALLY REALLY tiresome. Design choices in SQL Server (like in anything) have costs AND benefits (I mentioned some of the BENEFITS in my original answer). I also mentioned the performance penalties (aka costs), which in any event tend to be drastically overstated. A 2X performance penalty goes away after 18 months of Moore's law, and in almost no case is the performance penalty of a GUID PK even a 2X penalty. – MusiGenesis Aug 16 '09 at 11:42
  • @marc_s: ironically, the uniqueidentifier type was added to SQL Server for precisely the reason that I use and advocate it: for distributed/replicated databases. Are you claiming that you know better than the SQL Server designers? – MusiGenesis Aug 16 '09 at 11:50
1

It is a good idea to remember that databases tend to have a life independent from a front end application. Records can be inserted by batch processes, web services, data exchange with other databases, heck, even different applications sharing the same database.

Consequently it is useful if a database table is in charge of its own identify, or at least has that capability. For instance, in Oracle a BEFORE INSERT trigger can check whether a value has been provided for its primary key, and if not generate its own.

Both Oracle and SQL Server can generate GUIDs, so that is not a sufficient reason for delegating identity generation to the client.

APC
  • 144,005
  • 19
  • 170
  • 281
0

Sometimes, there is a natural, unique identifier for a table. For instance, each row in a User table can be uniquely identified by the UserName column. In that case, it may be best to use UserName as the primary key.

Also, consider tables used to form a many to many relationship. A UserGroupMembership table will contain UserId and GroupId columns, which should be the primary key, as the combination uniquely identifies the fact that a particular user is a member of a particular group.

John Saunders
  • 160,644
  • 26
  • 247
  • 397