Composite primary key in sql server

Question

I am trying to create a composite primary key in Sql server. The syntax I have tried is:

create table Installment_details
(
 constraint P_key primary key(Account_No,month),
 Account_No int not null,
     foreign key (Account_No) references Account_details(Account_no) on delete cascade,
 Month char(15) not null,
 D@te date,
 Receipt_no varchar(15),
 Amount_received int,
 Amount_left int,
 Amount_receiver char(50),
)

As far as I know it should create column with column name P_key for primary key but whenever I make a entry in table it doesn't show this column.

No, P_Key is not a column, it's the name of the primary key constraint. — Zohar Peled
– Zohar Peled, Commented Jan 17, 2019 at 15:30
You created the Composite key using those two fields (AccountNo, month). — N.S
– N.S, Commented Jan 17, 2019 at 15:31
No, that is only the name of the Key you created! Fields are only the ones which shows up on table. — N.S
– N.S, Commented Jan 17, 2019 at 15:33
Column names != constraint names. You never see constraint names unless you dig into system views or use Management Studio. You'll find P_key under Keys, but nowhere else. To refer to the primary key in foreign key constraints, you will also always need to name the columns involved ((Account_No, month)), never the constraint name itself. — Jeroen Mostert
– Jeroen Mostert, Commented Jan 17, 2019 at 15:34

Nicolas Fleitas · Accepted Answer · 2019-01-17 15:42:16Z

1

You are confused about the terms you're using. It's not the same a Primary Key and a Column. For example, you're creating a Primary Key based on two existing columns, and the name P_Key it's the name of the Primary Key, which is the way SQL SERVER (in this case) can identify a row in the Table (it cannot be two rows with the same values on those two columns). I hope this clarifies a little bit the issue.

answered Jan 17, 2019 at 15:42

Nicolas Fleitas

1188 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sachin Sah · Accepted Answer · 2019-01-17 15:42:44Z

1

I think you are getting it wrong P_key in your code is constraint's name not a column name. Also composite key is not a column, it is used when you don't have a column with unique values. So you take combination of two or more column as primary key so that we can uniquely identify a row.

answered Jan 17, 2019 at 15:42

Sachin Sah

362 bronze badges

Collectives™ on Stack Overflow

Composite primary key in sql server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related