| Author |
Topic |
|
aoriju
Posting Yak Master
156 Posts |
 Posted - 2010-07-29 : 08:16:57
|
| Dear allI have a table with Item_Id as Identity(1,1)Is any advantage while i am setting this Item_Id as Primary keyBoth of them not allow duplicate values....then what's the diff ? |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-07-29 : 08:28:39
|
http://www.sqlteam.com/article/identity-and-primary-keys
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
aoriju
Posting Yak Master
156 Posts |
Posted - 2010-07-29 : 08:37:25
|
| This article summarise the Difference between Identity key and Primary Key.My Question is simple.What i am trying to ask isAny relevance while setting identity key as Primary Key ? |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-07-29 : 08:48:11
|
One idea is:You can't replicate a table if it has no primary key.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2010-07-29 : 09:15:37
|
| It's called a surrogate key, and only has real value for updating rows...Would you for example, have an IDENTITY Column on a stae code table, and store the identity value in all you other tables?Or would you make the stated Code the Primary key.A state Code is what is considered a Natural Key.So the debate has always raged on about Surogate vs. Natural Keys.And I think the camps are split 50/50M$ for example uses surrogate keys (ad nauseum) for it's catalog for SQL Server...and they have found a mirard of problems in doing that...ever dump a database and have to "fix" users?Ever look at the stored procedure sp_help?(I like the comment in the code where it says..."give up"...a personal favorite)http://weblogs.sqlteam.com/brettk/archive/2004/06/09/1530.aspxBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-07-29 : 09:36:46
|
quote:
Originally posted by webfred
One idea is:You can't replicate a table if it has no primary key.
No, you're never too old to Yak'n'Roll if you're too young to die.
Only true for transactional replication.But for merge & snapshot its not necessary.Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless. PBUH |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-07-29 : 09:42:55
|
quote:
Originally posted by Idera
quote:
Originally posted by webfred
One idea is:You can't replicate a table if it has no primary key.
No, you're never too old to Yak'n'Roll if you're too young to die.
Only true for transactional replication.But for merge & snapshot its not necessary.Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless. PBUH
right.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-07-29 : 11:32:08
|
| Just to be clear, there is no such thing as an Identity Key. So, is your real question about placing a Unique or Primary Key constraint on an Identity column? Possibly if it should be clustered or not?If so, then one difference is that Primary Key constraints do not allow NULLs. Where as a Unique Constraint will allow a NULL value. But, if you are going to surrogate, as Brett mentioned, then I think only makes sense to create the Identity column as a Primary Key. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-07-29 : 12:06:28
|
quote:
Originally posted by X002548
quote:
Originally posted by Lampreyso, then one difference is that Primary Key constraints do not allow NULLs.
I don't think that's true anymore
It certainly is true, it's the definition of primary key. Try and put one on a nullable column and see. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-07-29 : 13:24:45
|
quote:
Originally posted by X002548
maybe it's a unique that allows nulls?<snip>
Correct. That's why I said:quote:
Originally posted by Lamprey
If so, then one difference is that Primary Key constraints do not allow NULLs. Where as a Unique Constraint will allow a NULL value.
 |
|
|
|
Celko
Starting Member
23 Posts |
Posted - 2010-07-29 : 13:34:50
|
quote:
Originally posted by aoriju
This article summarise the Difference between Identity key and Primary Key. My Question is simple.What i am trying to ask is Any relevance while setting identity key as Primary Key ?
Since an IDENTITY is a non-relational, proprietary piece of physical metadata, it can never be a key in a properly designed schema. It tells the experienced SQL programmer he has to clean up a bad design and find the real key.  Author ofSQL for Smarties Thinking in SetsSQL Puzzles and AnswersAnalytics and OLAP in SQLSQL Programming Style Standards in SQLData & DatabasesTrees & Hierarchies in SQL |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2010-07-29 : 13:42:05
|
quote:
Originally posted by Celko
quote:
Originally posted by aoriju
This article summarise the Difference between Identity key and Primary Key. My Question is simple.What i am trying to ask is Any relevance while setting identity key as Primary Key ?
Since an IDENTITY is a non-relational, proprietary piece of physical metadata, it can never be a key in a properly designed schema. It tells the experienced SQL programmer he has to clean up a bad design and find the real key. Author ofSQL for Smarties Thinking in SetsSQL Puzzles and AnswersAnalytics and OLAP in SQLSQL Programming Style Standards in SQLData & DatabasesTrees & Hierarchies in SQL
ding...ding...ding...."And in this Corner, weighing in at (SELECT Weight FROM Person WHERE Name = 'Celko'), Joe Celko"Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-08-03 : 04:34:34
|
quote:
Originally posted by aoriju
Dear allI have a table with Item_Id as Identity(1,1)Is any advantage while i am setting this Item_Id as Primary keyBoth of them not allow duplicate values....then what's the diff ?
Identity column without primary or unique key will allow duplicatesMadhivananFailing to plan is Planning to fail |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2010-08-03 : 09:42:20
|
quote:
Originally posted by CelkoSince an IDENTITY is a non-relational, proprietary piece of physical metadata, it can never be a key in a properly designed schema. It tells the experienced SQL programmer he has to clean up a bad design and find the real key.
I'd clarify that it indicates a bad design if your IDENTITY is the ONLY unique key on your table. Each table should have a natural key inherent in the data (and enforced by constraint), but may also include a surrogate key for programmatic or design purposes.Having an IDENTITY defined on a table does not in itself indicate poor design.________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-08-03 : 10:09:58
|
quote:
Anyone got a good natural key PK for an address table
Well, don't know if it's a good one, but if you parse the street number, apt./unit, street name, city, state/province and ZIP or postal code into separate columns, you should do OK with that. Of course if you have to join it to something else it begs for a surrogate key. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-08-03 : 10:23:48
|
quote:
Originally posted by X002548
Anyone got a good natural key PK for an address table?
in the UK : postcode + either house number, or house name, is unique - I think! not 100% sure though.UK Postcode is a "postman's walk" or somesuch - so typically one side of a street in town, or one lane of houses in the country. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
Identity Key vs Primary Key