Is there a reason to have an integer primary key column not also an Identity column?

Question

I've run into a problem where I need to regularly insert records into a table, but the integer primary key column is not an identity column. If it was, inserting records and having them auto-increment to maintain uniqueness would be easy. However, I can't make the primary key column an identity column without causing errors in an application that is still used to sometimes accomplish what I'm doing. Is there a reason why you would want an integer primary key and not have that column as an identity column also? I'm a little new to this and just wondering why someone would structure a table this way.

Edit to add: I've done some Googling and research, and I understand their differences and purposes, but I can't find anything on why you would not want to use them together in this particular instance and even create your table/application in such a way that you couldn't.

I mean, there **are** reasons, but we can't answer the reason it's made like that in your system. For instance, if the int value has actual meaning and is unique in the table, you can perfectly use it as a primary key and not make it an identity — Lamak, Jul 12 '16 at 20:19
The int value has no meaning. It's purely an identifier and artificial key. I'm just wondering what those reasons would be, even if they're not the specific reasons for our system. — asp8811, Jul 12 '16 at 20:21
Again, I can't know why the decision was made in your system, but a simple google search about "sql server why not use identity" gives several links with good information about it. [Here's one of them](http://sqlmag.com/t-sql/should-i-use-identity-or-not) — Lamak, Jul 12 '16 at 20:25
As @Lamak said there are reasons and it depends on your system but I think you are asking for examples: if you are recording data from an external system including identifiers, if the identifier is also a foreign key, if speed is of great concern with high volume inserts then identity can slow down a bulk insert in which case there are ways to generate batches of sequential identifiers to speed up the insert, and I am sure there are probably more. — Igor, Jul 12 '16 at 20:25
It seems academic anyway as you can't change it without breaking the other application so looks like you are stuck with it irrespective of whether there was some good reason. — Martin Smith, Jul 12 '16 at 20:40
Out of curiosity, in what way(s) does the application break if the column is made to be an identity? — Ben Thul, Jul 12 '16 at 21:45
@Ben Thul I'm not sure exactly what doesn't work. It doesn't give any detail other than it could not insert a record into the particular table. We asked the application vendor to look into the error, and they came back with the answer that the column was set to Identity spec (autoincrement). — asp8811, Jul 13 '16 at 21:05
@philipxy Yes, I mean auto-increment. Each row in the table is a billing period tied to a job. The required columns are the PeriodID (PK), Name (formed from parts of the FromDate), FromDate, ToDate, and JobNumber. There can be duplication in any column except PeriodID (different jobs w/ same period dates, different periods tied to same job). Other tables refer to periods based on their PeriodID. Are you saying that you would not want an auto-incrementing PK because it's a surrogate key w/out business meaning? If the column has no meaning anyway, why would auto-incrementing be undesirable? — asp8811, Jul 13 '16 at 21:48
@asp8811: I wonder if it's the sort of thing where they're specifying a value for the column which, when you try to do that for an identity column, doesn't work so well. Either way, it sounds like you're working with a vendor's application, so changes to the schema are… discouraged. If you want to keep your support anyways. :) — Ben Thul, Jul 14 '16 at 00:48

score 2 · Answer 1 · answered Sep 12 '17 at 07:05

2

IT seems like a bad database design to me , tables should have a primary key that you can use for searching and sorting for example a username as primary key, a integer primary key without auto increment is bad design

answered Sep 12 '17 at 07:05

Nikki

409
1
5
15

score 1 · Accepted Answer · edited May 23 '17 at 12:22

Is there a reason to have an integer primary key column not also an Identity column?

If by "identity" you mean (auto-increment as) surrogate, ie made up the DBMS, then yes:

-- ticket #N is held by person P
lotto(N, P) -- PK(N)

A surrogate is just a name/identifier ("identifying" in the everyday sense) for something that got picked arbitrarily by the DBMS, eg user "3508218", rather than not, eg "asp8811" or "eighty-eight" or "Texas". Note that they are surrogates ("meaningless") in the system that exists outside the DBMS. (Although some people don't call such a name/identifier generated by a system a surrogate if it's visible outside the system.)

PK/UNIQUE just says that the subrow values for a column set are unique in a table. Here N does have a "meaning" ie it is a thing's name/identifier that the DBMS does not have control over picking. In fact if a ticket can be held by only one person then P is also a candidate key (PK/UNIQUE) whether or not the value (of whatever kind) that names/identifies people is a surrogate.

Every PK/UNIQUE or superset in every base table & query result names/identifies things of some kind. Ie any column set on any types can name/identify things (be 1:1 or M:1 with them) whether or not it is a candidate key (PK/UNIQUE). So integer (and any other type or type set) primary key (and UNIQUE) columns (and column sets) (and supersets) are all over the place naming/identifying without being surrogates and whether or not they are candidate keys.

Is there a reason to have an integer primary key column not also an Identity column?

2 Answers2