January 30, 2014 at 7:07 am
As per the requirement, it is asked to create a table with two primary keys. How can we implement the same?
January 30, 2014 at 7:08 am
A table cannot have two primary keys. Sure the requirement isn't for a single primary key consisting of two columns?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 30, 2014 at 7:20 am
it is really interesting to hear. In a table, two prmary keys.
but this is a question which i got for an interview
i had options too. :doze:
as per the requirement, it is asked to create a table with two primary keys. how can we implement the same
(composite Primary key /
One primary key and one Unique key /
one primary key and unique key with NOT NULL constraint /
None of the above)
Thanks for the immediate response.
January 30, 2014 at 7:29 am
Ryan George (1/30/2014)
it is really interesting to hear. In a table, two prmary keys.but this is a question which i got for an interview
i had options too. :doze:
as per the requirement, it is asked to create a table with two primary keys. how can we implement the same
(composite Primary key /
One primary key and one Unique key /
one primary key and unique key with NOT NULL constraint /
None of the above)
Thanks for the immediate response.
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.
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 30, 2014 at 9:20 am
I'd say it depends on what you need because a table can't have 2 primary keys by definition. It's not about SQL Server, it's about relational theory.
January 30, 2014 at 10:40 am
Ryan George (1/30/2014)
as per the requirement, it is asked to create a table with two primary keys. how can we implement the same
At the risk of sounding like an echo...
A table cannot have two primary keys.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 30, 2014 at 5:12 pm
Ryan George (1/30/2014)
it is really interesting to hear. In a table, two prmary keys.but this is a question which i got for an interview
i had options too. :doze:
as per the requirement, it is asked to create a table with two primary keys. how can we implement the same
(composite Primary key /
One primary key and one Unique key /
one primary key and unique key with NOT NULL constraint /
None of the above)
Thanks for the immediate response.
I feel your pain and I hate written questions like that and that's why I'd never be able to pass a test. I do, however, love such questions in an interview because they give me the chance to shine.
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 (as the others have already stated).
That, not withstanding, I frequently do have the need to have a Primary Key and another key that could be used as a primary key on many of my tables and that answer is one of the 3 other answers on the list of possibilities you provided. So, now we have to guess... did the person that wrote the question understand that there truly can be one and only one primary key and he just wrote a question trying to trick you or is he someone that thinks that other kind of key is considered to be a Primary Key? Or is it a test of your interpretive capabilites to see if you understand that a lot of people don't know you can only have one real Primary Key on a table and what you would do about it if asked such a thing in real life?
If it's a written test that a human would review, I'd give the obvious answer that you can only have one and only one Primary Key for a table but I'd also write a small paragraph that you can also have a ALTERNATE KEY that could be used as a Primary Key if there wasn't one already and then I would identify which answer that one was.
If it's a test to be graded by machine, I'd go with the technically correct answer and argue it to the max if I got it wrong.
If it's a question during an interview, then it's "discussion time" where you'd explain all about PKs, AKs, and their relationship with FKs. That's what interviewers look for when they ask questions. Do you know the subject well enough to spontaneously add facts, uses, and methods or are you just "book learned".
Remember, requirements in real life are rarely technically correct and THAT might be what they were actually looking for. A lot of people don't understand that "Before you can think outside the box, you must first realize... YOU'RE IN A BOX!" 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
January 31, 2014 at 12:23 am
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.
January 31, 2014 at 12:36 am
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.
Wow, just...
I can tell you Sean knows perfectly well the answer to the question. We however don't tend to like interview questions here, especially when they don't appear to be accompanied by any research (google, books online, etc). I've interviewed enough people who didn't have a clue and appeared to have just memorised answers.
Be a Professional, Man..!!
Good idea. Maybe take your own advice? Would you tell someone, someone that 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?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 31, 2014 at 2:09 am
GilaMonster (1/31/2014)
Ryan George (1/31/2014)
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
January 31, 2014 at 5:23 am
Lempster (1/31/2014)
GilaMonster (1/31/2014)
Ryan George (1/31/2014)
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.
January 31, 2014 at 7:28 am
Ryan George (1/31/2014)
Lempster (1/31/2014)
GilaMonster (1/31/2014)
Ryan George (1/31/2014)
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 31, 2014 at 8:19 am
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
Change is inevitable... Change for the better is not.
January 31, 2014 at 2:34 pm
I say we resurrect John Wayne from the dead and have him monitor these forums....Pilgrim:-)
February 11, 2014 at 2:58 pm
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?
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply