How to make a foreign key in sql server

Mark Tabor

10y1.3k

I have a table in which i am making two columns as a combine as primary key i think this is called a composit key , Now in another table i want to make one of these two columns as foreign key when i tried that it gives an error , i think it is saying that i should include both columns from second table as well , check the link for table structure

https://sweet-gmail.tinytake.com/sf/NzgwNjE0XzM1MTM1OTk

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

barathi raja10y ago
Examples with Primary key and Reference Tables for Foreign Key:
/* Primary Key*/
create table prim(id int,name varchar(30),constraint p_id PRIMARY KEY(Id))
select * from prim
insert into prim(id,name) values(12,'hari')
insert into prim(id,name) values(12,'soori')
/* Foreign Key*/
create table foregn(f_id int,mail varchar(20),constraint f_id foreign key(f_id) references prim(Id))
select * from foregn
select * from tbl_student
Karthik Elumalai10y ago
Hi Mark tabor,
Yup as vinay singh said ,we need to refer both the columns for the foreign key
kindly the below link for detailed info:
http://searchoracle.techtarget.com/answer/Foreign-key-referencing-only-part-of-composite-primary-key
kindly let me know your thoughts
thanks
karthik
Balamurali Krishnan R10y ago
use the following script

ALTER TABLE dbo.Childtable
ADD CONSTRAINT FK_Childtable_ParentTable
FOREIGN KEY(OrderId, CompanyId) REFERENCES dbo.ParentTable(OrderId, CompanyId)

Vinay Singh10y ago

This may help you

As we know Table2 has a composite primary key (FileID, FileType), then any reference to it must also include both columns.

ALTER TABLE dbo.Table1   ADD CONSTRAINT FK_Table1_Table2   
FOREIGN KEY(FileID, FileType) REFERENCES Table2(FileID, FileType)

For detail check the link
http://stackoverflow.com/questions/7650969/foreign-key-used-in-composite-primary-key