Primary Key vs IDENTITY Property ????
-
Hello,
To guarantee record uniqueness, is there a need to set a column with a IDENTITY property (ie: created using CREATE/ALTER TABLE..) with a Primary Key constraint as well? What is the difference?
Many thanks. Jeff
-
Identity is a PROPERTY. Primary Key is a CONSTRAINT.
Therefore only a Primary key or a unique constraint guarantee uniqueness.
Primary Key also prevents nulls
* Noel -
Here is just one example how IDENTITY does not guarantee uniqueness:
CREATE TABLE #t(a int IDENTITY, b varchar(5))
INSERT INTO #t VALUES ('ss')
INSERT INTO #t VALUES ('ab')
SET IDENTITY_INSERT #t ON
INSERT INTO #t (a,b) VALUES (1, 'QR')
SELECT * FROM #t
DROP TABLE #t
HTH, Vladan
-
The purpose of an IDENTITY property is to create a uniqness to a record that does not have a naturally occurring key or to act as a surrogate in replacing a natural key that represents the data in a fashion that is too long to make pratical use of or that the natural key has a protected requirement that would not be met thru it's use.
A persons SSN makes a great natural key but because of the fact you don't want to expose this to improper parties it would make a terrible key to represent the person in a client environment where multiple people can query the individual. So in this case to protect the person you make the SSN the Primary Key but create an INDENTITY based column to generate a substitute value to represent that person.
Also, if no natural key exists within the bounds of your data then the creation of an artificial key of your own becomes neccessary, so you rely on IDENTITY to provide you one. This in itself will not prevent duplication of data it just provides assistance in mitigating the trobules of not being able to work with otherwise similar records.
-
Please not again an IDENTITY discussion.
This is a never ending story. I really, really like SQL Server MVP Steve Kass' answer here:How about putting a UNIQUE NOT NULL CLUSTERED constraint on the identity
column and putting the PRIMARY KEY NONCLUSTERED constraint on the
multi-column primary key?
That might confuse the anti-identity fanatics enough so they'll stop
complaining. You will have a natural primary key, so they won't think
the world is coming to an end, but you will go on as you always have,
using the identity column for its convenience in queries, FK
constraints, etc.
You can find it (and 111 other replies to a similar question) here:
http://groups.google.de/groups?q=g:thl2487473974d&dq=&hl=de&lr=&selm=edb90340.0311301240.11f2d584%40posting.google.com
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
A persons SSN makes a great natural key
Opps. Gotta question that.
SSNs can't be great natural keys. They are not mandatory (even for Americans), they are not unique, and they are one of the most often mis-reported piece of information by their owners.
Finally, there are restrictons on their use, not to mention public backlash when they are abused.
The only thing an SSN is good for identifying is a Social Security account - and that is not a person; it's an account. And a person may have zero, one, or many SSNs. An SSN may be the account for zero, one, or many people. SSNs are not good identifiers.
-
IDENTITY columns are nothing more than a specific implementation of what are generally known as surrogate keys. There are several ways to generate surrogate keys, but for SQL Server the IDENTITY function is undoubtedly the most efficient.
Your question is really about surrogate keys and whether they are required for uniqueness. The answer is that it depends... There are several concepts that you must keep in mind in order to use surrogates intelligently. First off you need to realize that surrogate keys DO NOT necessarily ensure logical uniqueness!!! They do--by definition--ensure physical uniqueness, but that isn't the same thing at all.
Most data has a natural key that can uniquely identify each row. Sometimes the natrual key is a single column, or it might be the combination of all the columns in a table. No matter which it is, you should always enforce the uniqueness of the natrual key (assuming one exists) with either a PK or a unique constraint. In the case of composite keys, I will usually create a surrogate to use as the PK, but will still declare a unique constraint on the natural key.
There are a lot of folks who claim that surrogate keys should never be exposed to the users, or who claim that they should never be used at all. They are wrong. In many cases the only difference between a natrual key and a surrogate key is when it is created. Take the example of invoice numbers. If the invoice numbers are assigned before the data hits the database, it is said to be a natural key. If the invoice numbers are assigned by an IDENTITY column then they are said to be a surrogate key. What is the difference? Nothing really.
I know this is probably more than you asked for. But it is still far from a complete treatment of the subject, and I am tired of the know-nothings out there that think that it can be treated with slogans. I'm working on a series of articles on database design and this will be a subject that I plan to cover. Keep an eye out in the comming months...
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Hi,
many thanks to all.
dcpeterson - this is exactly what I was looking for. I look forward to your future articles. When the time comes, I'd be appreciative if you could post or email to myself, the link to them.
Many thanks. Jeff
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply