FOREIGN KEY - THINGS TO KNOW (MS SQL SERVER)

 create table testmercando1

(

ID int not null primary key ,

--rollno not null primary key, -- can not define two primary keys this way --Should define composite primary keys

name varchar(50),

section varchar(1))

select * from testmercando1

create table result

(

Id int not null primary key,-- must have to connect with foregn keys

subject varchar(20),

MarksObtained int,

Status VARCHAR(7),

ID int,

foreign key ID) references testmercando1 (ID) )

Alert : know about composite key first

alter table result drop constraint FK__result__Id__7755B73D -- since constraint name is important to drop a foreign key why not give constraint key while creating so that it will be easy for us to drop .. since creating and deleting are major components in a software development field.

create table result

(

Id int not null ,-- must have to connect with foregn keys

subject varchar(20),

MarksObtained int,

Status VARCHAR(7),

ID int,

primary key (Id), constraint FK_result foreign key(ID) references testmercando1(ID) ) 

--incase you dont want to give constraint name and have default constraint name just do ID int foreign key refrences testmercando1(ID)....  like this .. declare your primary key in the begining(top) like Id int not null primary key.

Now altering table for foreign key addition after table is created. 

alter table result add constraint FK_result foreign key(Id) references testmercando1(ID) -- just like primary key addition