Click here to Skip to main content
15,895,746 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I want to create Composite Primary Key in a table, but Ms Sql server gives me the error that you cant create composite primary keys. why? or any alternative.
I want to create One-to-One relationship between Table Subscription against Table Payment then then One to One relationship against Table Payment to PaymnetCash,PaymentWesternUnion,PaymentCreditCard.

SQL
Create Table TblSubscription
(
SubscriptionId int identity(1,1) Primary key,
UserId INT NOT NULL Foreign key References TblAppUser(UserId),
PackageId INT NOT NULL Foreign key References TblPackages(PackageID),
StartDate datetime,
FinishDate datetime,
IsActive bit,
IsPaid bit
);


Create Table TblPayment
(
PaymentId int identity(1,1) Primary Key,
SubscriptionId int Primary key,
PaymentAmount float,
PaymentDate datetime,
PaymentMethod varchar(50),
PaymentStatus bit
);
Posted
Comments
CHill60 23-Apr-14 13:03pm    
So which is it? Create a composite primary key or create the foreign key relationships? What exactly did the error message say?
Muhammad Taqi Hassan Bukhari 23-Apr-14 14:04pm    
Msg 8110, Level 16, State 0, Line 1
Cannot add multiple PRIMARY KEY constraints to table 'TblPayment'.

1 solution

Well, SQL is not saying that you cannot create a composite key, it is saying that you can't have multiple primary keys.

Have a look at this link[^] for an example of how to create a composite primary key

I suspect your schema design is exactly up to scratch though and may need some rethinking
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900