Primary key

Question

Primary key

sandeep4testing

Mr or Mrs. 500

Points: 534
More actions
March 23, 2012 at 12:50 pm

#253712

Hi guys,
I want to crate primary key for 2 columns in the table, as well as i need to create more that 2 columns. I'm trying to create primary key like following way
create table usern(userid varchar(20),fname varchar(20),lname varchar(20),village varchar(20) references Towns(tname),phone int,relationship varchar(20)),constraint pl_vilage primary key (fname,lname)
-- here i want to create 2 primary key columns those are fname and lname
Any one assist me how i need to over come form this problem.
Thank's
Sandeep.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

sandeep4testing (3/23/2012)
Hi guys,
I want to crate primary key for 2 columns in the table, as well as i need to create more that 2 columns. I'm trying to create primary key like following way
create table usern(userid varchar(20),fname varchar(20),lname varchar(20),village varchar(20) references Towns(tname),phone int,relationship varchar(20)),constraint pl_vilage primary key (fname,lname)
-- here i want to create 2 primary key columns those are fname and lname
Any one assist me how i need to over come form this problem.
Thank's
Sandeep.

Let me get this right, you want two primary keys, one on fname and one on lname.

Or is it that you want one primary key on both fname and lname.

Problem, this will restrict you to having only ONE person with a given name, like John Smith.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

ALTER TABLE usern ADD CONSTRAINT <constraint name> PRIMARY KEY (lname, fname)

Problem is, names are not unique. Unless this is for a small group of people that you know have no overlapping names, you're going to run into all sorts of problems in the future.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sandeep4testing Mr or Mrs. 500 Points: 534 More actions · Answer 3

Hi Lynin,

Thank u very much for ur quick reply,

i want different primary keys for fname and lname,

here my requirement is :

FK- FName should be FK of Users.FName

FK- LName should be FK of Users.LName

Thank's

Sandeep.

GSquared SSC Guru Points: 260824 More actions · Answer 4

Even for a small group, a primary key on that won't necessarily help. What happens if "John Smith" comes in as "Johnny Smith"? The unique constraint on the primary key won't stop that.

Another thing I noticed: You probably don't want to use Int datatype for phone numbers. Will make formatting them in the presentation layer much more complex. Phone numbers are strings.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

sandeep4testing (3/23/2012)
Hi Lynin,
Thank u very much for ur quick reply,
i want different primary keys for fname and lname,
here my requirement is :
FK- FName should be FK of Users.FName
FK- LName should be FK of Users.LName
Thank's
Sandeep.

Here is the other problem, your table can have only ONE primary key.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 6

Lynn Pettis (3/23/2012)Here is the other problem, your table can have only ONE primary key.

Yup... just a basic, tiny, tiny, small like this (shows finger tips real close) detail, init? 😀

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

sandeep4testing (3/23/2012)
FK- FName should be FK of Users.FName
FK- LName should be FK of Users.LName

Names as foreign keys? That's a weird design.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

sandeep4testing (3/23/2012)
Hi Lynin,
Thank u very much for ur quick reply,
i want different primary keys for fname and lname,
here my requirement is :
FK- FName should be FK of Users.FName
FK- LName should be FK of Users.LName
Thank's
Sandeep.

Seems to me you want Foreign Keys and not Primary Keys.

I would think there would be a better key than what you are trying to do.

Can you provide the table create scripts for the tables involved?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

GilaMonster (3/23/2012)
sandeep4testing (3/23/2012)
FK- FName should be FK of Users.FName
FK- LName should be FK of Users.LName
Names as foreign keys? That's a weird design.

Even weirder is that from the looks of this it would result in two many to many relationships between the two tables.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 10

Lynn Pettis (3/23/2012)
GilaMonster (3/23/2012)
sandeep4testing (3/23/2012)
FK- FName should be FK of Users.FName
FK- LName should be FK of Users.LName
Names as foreign keys? That's a weird design.
Even weirder is that from the looks of this it would result in two many to many relationships between the two tables.

:w00t: runs away from the computer mumbling something like "Codd please forgive us..."

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

PaulB-TheOneAndOnly (3/23/2012)
Lynn Pettis (3/23/2012)
GilaMonster (3/23/2012)
sandeep4testing (3/23/2012)
FK- FName should be FK of Users.FName
FK- LName should be FK of Users.LName
Names as foreign keys? That's a weird design.
Even weirder is that from the looks of this it would result in two many to many relationships between the two tables.
:w00t: runs away from the computer mumbling something like "Codd please forgive us..."

There is a party forming at the Tent in the Desert. 😛

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

Lynn Pettis (3/23/2012)
PaulB-TheOneAndOnly (3/23/2012)
Lynn Pettis (3/23/2012)
GilaMonster (3/23/2012)
sandeep4testing (3/23/2012)
FK- FName should be FK of Users.FName
FK- LName should be FK of Users.LName
Names as foreign keys? That's a weird design.
Even weirder is that from the looks of this it would result in two many to many relationships between the two tables.
:w00t: runs away from the computer mumbling something like "Codd please forgive us..."
There is a party forming at the Tent in the Desert. 😛

Hippo will be checking IDs but not FName/LName.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events