How I can create a table with two primary keys in T-sql?

Question

What is the problem in this code?

CREATE TABLE pharmacy_night
(
    [Id_day] INT NOT NULL ,
    [Id_pharmacy] INT,
    constraint pk_pharmacy_night primary key (Id_day, Id_pharmacy),
    constraint fk_pharmacy_night_days foreign key (Id_day) references days(Id_day),
    constraint fk_pharmacy_night_pharmacy foreign key (Id_pharmacy) references pharmacy (Id_pharmacy)


)

You can only have one primary key, but you can have composite primary key. — Hari Prasad, May 15 '15 at 04:40
Can u add more, why do you need two primary key in one table? If both are important then split them into two tables or use composite key. — Amnesh Goel, May 15 '15 at 04:42

Amnesh Goel · Accepted Answer · 2015-05-15T04:47:05.560

1

Try this for composite key

create table abc (
  ID int,
  number int,
  constraint PK_abc primary key (ID, number)
);

And try this for Primary key

create table abc
(
ID int primary key,
number int
)

edited May 15 '15 at 04:47

answered May 15 '15 at 04:44

Amnesh Goel

2,617
3
28
47

it doesn't work, when i try to update i receive errors – May 15 '15 at 04:46
What error? Explain your business case please... – Amnesh Goel May 15 '15 at 04:46
Update cannot proceed due to validation errors. Please correct the following errors and try again. SQL71508 :: The model already has an element that has the same name dbo.abc. SQL71501 :: Primary Key: [dbo].[PK_abc] has an unresolved reference to object [dbo].[abc]. SQL71501 :: Primary Key: [dbo].[PK_abc] has an unresolved reference to object [dbo].[abc]. SQL71508 :: The model already has an element that has the same name dbo.abc.number. SQL71508 :: The model already has an element that has the same name dbo.abc.ID. – May 15 '15 at 04:49
This error is not helping other than getting that error is coming form some server side code. What is your insert or update query with data please? Check this [SQL FIlddle](http://sqlfiddle.com/#!9/1061a/1) – Amnesh Goel May 15 '15 at 04:56
what do you mean in insert query?just i want to create a table with a composite key – May 15 '15 at 04:59
it'is ok.. I solve the problem.. – May 15 '15 at 05:01
Okay, so why don't you drop the existing table if any with the same name and references.. Could you fire a select * from to check if that exist.. or check if any reference exist with that name.. – Amnesh Goel May 15 '15 at 05:01

How I can create a table with two primary keys in T-sql?

1 Answers1