SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


create a table with two primary keys.


create a table with two primary keys.

Author
Message
Ryan George
Ryan George
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 59
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.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63426 Visits: 17966
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 Modens 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)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217749 Visits: 41995
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Chris Hurlbut
Chris Hurlbut
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2066 Visits: 540
I say we resurrect John Wayne from the dead and have him monitor these forums....Pilgrim:-)
sqlvogel
sqlvogel
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2130 Visits: 3708
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?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217749 Visits: 41995
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42450 Visits: 19838
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.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
RonKyle
RonKyle
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7080 Visits: 3622
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.



sqlvogel
sqlvogel
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2130 Visits: 3708
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.
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42450 Visits: 19838
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.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search