how to use multiple primary key in a database

Question

0.00/5 (No votes)

See more:

.NET3.5

I am making desktop appication using c#.Net and Sql Server.I have taken a form in which there are 4 labels prospect id ,customer id,customer name and services and same are the fields of database.Nw i want that prospect id and customer id both should not have duplicate values.so please tell me the solution for this

I have created two tables one table having one primary key with two columns

create table PSM.dbo.conversion(Prospect_ID int,Customer_ID int ,Customer_Name varchar(50),Customer_Products_Services varchar(50),constraint pk1 primary key(Prospect_ID ,Customer_ID  ));

it run successfully

2nd table as foreign key of one column only

create table PSM.dbo.Customer_follow_up(Customer_ID int references conversion(Customer_ID),Customer_Name varchar(50),Customer_Products_Services varchar(50),Feedback nvarchar(1000));

here,getting an error

Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'PSM.dbo.conversion' that match the referencing column list in the foreign key 'FK__Customer___Custo__5AEE82B9'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

regards

Posted 5-May-11 2:37am

shivani 2013

Updated 5-May-11 20:37pm

v2

Add a Solution

Comments

yesotaso 6-May-11 9:36am

The reference table "conversion" does not have "Customer_ID" as Primary Key simple as that. If you want to add such constraint, "references conversion(Prospect_ID, Customer_ID)" but of course you do not have Prospect_ID column "Customer_follow_up" table and Customer_ID itself doesnot guarantee that you get right "conversion" record. It is design fault. 1st solution I can think of is make only "Customer_ID" as primary key and put a unique constraint on "Prospect_ID ,Customer_ID". that way it will work BUT I do not know the intention of making a PK with 2 fileds in the 1st place this may hurt your design.

5 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Toniyo Jackson · Answer 1 · 2011-05-05T02:44:00

Solution 2

You can have only one Primary Key[^] in a table.

But you can have multiple Unique Key[^].

Posted 5-May-11 2:44am

Toniyo Jackson

Comments

Peter_in_2780 5-May-11 8:48am

Didn't we go round this circus a day or two ago? I'm getting bored with this question...

nit_singh · Answer 2 · 2011-05-05T02:49:00

Solution 3

First of all you can not create multiple primary key, you can create single primary key on more than one column.

CREATE TABLE dbo.test
(
test1 int,
test2 int,
test3 int,
CONSTRAINT somename PRIMARY KEY (test1 , test2 )

)

Posted 5-May-11 2:49am

nit_singh

Comments

yesotaso 5-May-11 10:12am

Can Mr/Ms downrater? Can you elaborate? The statement above is perfecly fine though seems repetition but code was not given before to clear confusion.

shivani 2013 6-May-11 2:37am

i hve updated question see it nw

shivani 2013 6-May-11 2:37am

i hve updated question see it

Mahendra.p25 · Answer 3 · 2011-05-05T02:41:00

Solution 1

try this Link

Composite Key[^]

Posted 5-May-11 2:41am

Mahendra.p25

Updated 5-May-11 18:44pm

v2

Comments

Peter_in_2780 5-May-11 8:46am

As Toniyo says, there can only be one PRIMARY key in a table.

SKOTAJI · Answer 4 · 2011-05-05T19:55:00

Solution 4

Its better keep a primary key as separate and use Unique Key combination on those two DB fields. Refer the concept called as Composite primari key !

Thanks !.

Posted 5-May-11 19:55pm

SKOTAJI

Comments

shivani 2013 6-May-11 2:27am

can u pls explain it little bit more with eg

Ramalinga Koushik · Answer 5 · 2011-05-05T20:00:00

Solution 5

You can only have one primary key, but you can have multiple columns in your primary key.
You can also have Unique Indexes on your table, which will work a bit like a primary key in that they will enforce unique values, and will speed up querying of those values.

Posted 5-May-11 20:00pm

Ramalinga Koushik

Comments

shivani 2013 6-May-11 2:27am

can u pls explain it little bit more with eg