Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

create a table with two primary keys. Expand / Collapse
Author
Message
Posted Friday, January 31, 2014 5:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 11:32 PM
Points: 8, Visits: 57
Lempster (1/31/2014)
GilaMonster (1/31/2014)
Ryan George (1/31/2014)

[quote]Be a Professional, Man..!!


Good idea. Maybe take your own advice? Would you tell someone that you've never met and that you asked for free advice from to their face to shut up because you didn't like their advice?

+1

@Ryan George: you are fairly new to the Forums and perhaps Sean could have been a little kinder, but there's no excuse for your response.

Some of the most knowledgeable and respected people in the SQL Server community (I'm not including myself in that category!) post on these forums and if you want their help in future, I suggest adjusting the tone of your posts.

Hopefully just a one-off eh?

Lempster


Hello Lempster/GilaMonster,

This is not an insolence from my side. I just expressed my feelings towards the quote. I know it was impoliteness, and I know Mr. Lange is one of the best techies and a respected person, but this is not the way you treat the newbies into the forum.

And, I just said it. I hope he could understand that. and I hope, you too.

Thanks for the guidance and advises. And I guess We should stop this here.
Post #1536729
Posted Friday, January 31, 2014 7:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:21 PM
Points: 13,083, Visits: 11,918
Ryan George (1/31/2014)
Lempster (1/31/2014)
GilaMonster (1/31/2014)
Ryan George (1/31/2014)

[quote]Be a Professional, Man..!!


Good idea. Maybe take your own advice? Would you tell someone that you've never met and that you asked for free advice from to their face to shut up because you didn't like their advice?

+1

@Ryan George: you are fairly new to the Forums and perhaps Sean could have been a little kinder, but there's no excuse for your response.

Some of the most knowledgeable and respected people in the SQL Server community (I'm not including myself in that category!) post on these forums and if you want their help in future, I suggest adjusting the tone of your posts.

Hopefully just a one-off eh?

Lempster


Hello Lempster/GilaMonster,

This is not an insolence from my side. I just expressed my feelings towards the quote. I know it was impoliteness, and I know Mr. Lange is one of the best techies and a respected person, but this is not the way you treat the newbies into the forum.

And, I just said it. I hope he could understand that. and I hope, you too.

Thanks for the guidance and advises. And I guess We should stop this here.


Let me just say that I probably could have been less confrontational but it seemed to me that you were in an interview and looking for an answer. We have seen that around here many times.

My comment is because so often we see people come around here asking for other to provide answers to interview questions so they can get a job. Often they don't want to learn the answer, they just want the job. When somebody comes here after the interview and wants to discuss the questions that caused them grief in the interview, myself and many others are happy to help the learning process. Nobody wants to just spoon feed answers, we like to help others learn.

Let's put this misunderstanding behind us. I look forward to hopefully providing some insight on future questions.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1536777
Posted Friday, January 31, 2014 8:19 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:18 AM
Points: 36,751, Visits: 31,202
Ryan George (1/31/2014)

So what do you think? We are not here to provide answers for interview questions so you can get a job that you are not qualified for.



Dear Mr. Lange,

if you dont know the answer, just shut up and search some other threads. dont poke your nose into this. I will ask questions which I ve doubt. Please dont reply/make any quotes if you have no clarity in the answer. And this is not the right way to handle this.

Be a Professional, Man..!!

Cheers.


The others have already explained why that's so out of line but I'll also tell you that this is the world of IT and you should be able to handle such comments that question your intentions, especially when the question is something that every candidate for any job pertaining to SQL Server should actually know. To wit, many people who conduct interviews will frequently say something insulting or constantly interrupt the candidate just to see how they'd react at crunch time.

How well do you think you did there?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1536802
Posted Friday, January 31, 2014 2:34 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 1:24 PM
Points: 1,432, Visits: 460
I say we resurrect John Wayne from the dead and have him monitor these forums....Pilgrim
Post #1536983
Posted Tuesday, February 11, 2014 2:58 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 12:46 AM
Points: 440, Visits: 3,270
Jeff Moden (1/30/2014)

By definition and by design and as the others have stated, you cannot have two Primary Keys on a table. If you try to create a second real Primary Key on a table, it will give you an error

By definition? Well perhaps yes but not necessarily. Let's not confuse the concept of a primary key with the misnamed construction called a PRIMARY KEY constraint in SQL. The set of columns subject to a PRIMARY KEY constraint is not necessarily the actual "primary key" of a table. So the fact that SQL Server has a limitation that only allows the PRIMARY KEY syntax to be used once per table isn't necessarily the definite answer to the question. Anyone who thought to point that out in an interview would certainly get extra points from me.

In principle any candidate key of a table can be called "primary", meaning it is designated as the "preferred" identifier for information in that table. Since the primary key is not fundamentally different to any other key it isn't any great matter of principle that there must only be one such key. Historically (before SQL was commonplace), the term "primary key" was used for any and all keys of a table and not just one. For ease of comprehension and to simplify some aspects of design and implementation it frequently makes sense to designate exactly one primary key but there's no absolute need to be bound by that rule if you find an exceptional reason to do otherwise.

My favourite example, a table of Marriages with exactly two attributes: Husband and Wife. Both attributes are candidate keys because clearly we don't want to allow the same person to have more than one marriage simultaneously. So which key is "primary", husband or wife? Does it make any real difference if I pick either or even both of those as primary keys?


David
Post #1540463
Posted Tuesday, February 11, 2014 4:03 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:18 AM
Points: 36,751, Visits: 31,202
sqlvogel (2/11/2014)
Jeff Moden (1/30/2014)

By definition and by design and as the others have stated, you cannot have two Primary Keys on a table. If you try to create a second real Primary Key on a table, it will give you an error

By definition? Well perhaps yes but not necessarily. Let's not confuse the concept of a primary key with the misnamed construction called a PRIMARY KEY constraint in SQL. The set of columns subject to a PRIMARY KEY constraint is not necessarily the actual "primary key" of a table. So the fact that SQL Server has a limitation that only allows the PRIMARY KEY syntax to be used once per table isn't necessarily the definite answer to the question. Anyone who thought to point that out in an interview would certainly get extra points from me.

In principle any candidate key of a table can be called "primary", meaning it is designated as the "preferred" identifier for information in that table. Since the primary key is not fundamentally different to any other key it isn't any great matter of principle that there must only be one such key. Historically (before SQL was commonplace), the term "primary key" was used for any and all keys of a table and not just one. For ease of comprehension and to simplify some aspects of design and implementation it frequently makes sense to designate exactly one primary key but there's no absolute need to be bound by that rule if you find an exceptional reason to do otherwise.

My favourite example, a table of Marriages with exactly two attributes: Husband and Wife. Both attributes are candidate keys because clearly we don't want to allow the same person to have more than one marriage simultaneously. So which key is "primary", husband or wife? Does it make any real difference if I pick either or even both of those as primary keys?


All true but, as you have pointed out yourself, there is only one Primary Key in T-SQL. You can have a dozen different alternate keys, if you'd like, but, by definition in T_SQL, there can only be one "Primary Key" on any given table.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1540480
Posted Tuesday, February 11, 2014 4:25 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 9:24 PM
Points: 3,342, Visits: 7,216
sqlvogel (2/11/2014)

In principle any candidate key of a table can be called "primary", meaning it is designated as the "preferred" identifier for information in that table.

That's the point. A table can have multiple candidate keys but just one primary key.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1540489
Posted Wednesday, February 12, 2014 9:11 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 2:49 PM
Points: 804, Visits: 1,989
My favourite example, a table of Marriages with exactly two attributes: Husband and Wife. Both attributes are candidate keys because clearly we don't want to allow the same person to have more than one marriage simultaneously. So which key is "primary", husband or wife? Does it make any real difference if I pick either or even both of those as primary keys?


It would certainly seem to make a real difference. If the husband is the primary key, wives could still have multiple husbands, and vice versa. This would be a tricky one and subject to obvious questions like is the record deleted upon end of the marriage. If not, people remarry. As I don't ever have to expect to maintain such a table, that's as far as I will take these thoughts.

I would chime in, however, that I agree that the original post could have included some thoughts on the matter, newness not withstanding.



Post #1540833
Posted Wednesday, February 12, 2014 10:16 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 12:46 AM
Points: 440, Visits: 3,270
RonKyle (2/12/2014)
My favourite example, a table of Marriages with exactly two attributes: Husband and Wife. Both attributes are candidate keys because clearly we don't want to allow the same person to have more than one marriage simultaneously. So which key is "primary", husband or wife? Does it make any real difference if I pick either or even both of those as primary keys?


It would certainly seem to make a real difference. If the husband is the primary key, wives could still have multiple husbands, and vice versa.


No because I already stated that both Husband and Wife are keys (i.e. candidate keys). Enforcing all the desired keys of a table is of course extremely important - much more important than choosing one of them to be "primary". The point of the example is that it doesn't make any difference which key you call primary. It shouldn't even make any difference if you call both of them primary. Whatever distinction you choose to ascribe to the primary key could in principle apply equally well to any or all other keys so the form, function and intended meaning of the table isn't altered by that essentially arbitrary choice.


David
Post #1540893
Posted Wednesday, February 12, 2014 10:43 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 9:24 PM
Points: 3,342, Visits: 7,216
That's the difference of unique keys and primary keys. Primary keys will idenitfy a row within a table and they're essentially a preferred unique key. Unique keys will enforce uniqueness and a table can have as many as needed.
For me, the primary key on the marriages table should include both columns (husband & wife) or I might choose a surrogate key if the situation is correct for that. Husband and wife might be both foreign keys to a table People (and the PK for that table is a great discussion on its own).



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1540907
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse