Monday, September 19, 2011

Foreign Key constraints using Visual Studio with SQL Server Database


Step 1:
File ->New->Website
Step 2:
In Solution Explorer Right click over App_data folder and click on add item and add Sql Server Database.

Step 3:
In Server Explorer Expand Database by clicking on + sign before database.mdf then right click over Tables and click on Add New Table.
Step 4:
Enter Column Name(eg. Id) and Data Type.(eg. int).
Then right click over Id and select Primary Key(So that this table will become primary key table)

Save this table by pressing ctrl+s


After Saving this table Sever Explorer will looks like as below given Image when you expand Table Folder by clicking + sign
Step 5:
Right click over table1 and click on Show data and enter data as shown below:

Step 6:
Create table2 by same above Procedure.(Note: column in table2 you are going to set as foreign key must have same data type(eg. int) as in primay key table1)
press OK to save table2. and close it.

After Saving this table Sever Explorer will looks like as below given Image when you expand Table Folder by clicking + sign

Step 7:
Before entering data in table2 double clicck on Table2 in Server Explorer.
Then click on Relationships button 
you can click on it by different ways shown below
This button also appears over Server Explorer.
This option you can also get by right click over Id column in Table2.

Then after click Foreign Key Relationships window will appear. and click on Add button.

Select Tables And Columns Specifications


(click on this)

Tables and Columns window will appear. Set all values as shown.

press OK.
after that screen will looks like...
close this window by clicking close button.

IMP: press ctrl+s and save dbo.Table2 first then do the data Entry in Table 2.
If you do entry in Table2 without saving it. then it will not follow Foreign Key constraints.
and when you try to save dbo.Table2 after entry in Table2 it will show ERROR given below:
{
'Table1' table saved successfully
'Table2' table
- Unable to create relationship 'FK_Table2_Table1'.  
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Table2_Table1". The conflict occurred in database "~\FOREIGNKEY\APP_DATA\DATABASE.MDF", table "dbo.Table1", column 'Id'.
}
save dbo.Table2

Step 8: 
After saving dbo.Table2 make entry in Table2.


This is Foreign Key constraints due to Id in 2nd row not matched with the entry in primary key Table1.

2 comments:

Faysal Ahamed said...

Many many thanks for this tutorial. Yous step by step procedure with picture is so helpful. Many many thanks again.

Tay Nguyen said...

Many thanks to you. It's what i'm looking for.

Popular Posts