In this tutorial, I will show you how can we create Foreign Key in a table without writing any query in SQL Server 2008. Foreign Key makes your column to contain data which is present in any cell of the referencing column of the other table, you can’t insert different values, but if you allow the column to have NULL values, then it can have NULL values. It has to be noted that the column of referenced table must be Primary Key column of that table.
For example, I have 1 column already created in my SQL Server named [tbemp] which contains two columns [eid] INT and [ename] VARCHAR(50). Now I am creating one more table where I will create a Foreign Key Referencing [eid] column of this [tbemp] table.
- In the new table design, create all the columns you want, and then right click anywhere in the design, and select Relationships from the Right Click Context Menu.
- In the Relationships dialogue, click on Add button which will create a new relationship of this table.
- Now in the right pane, expand the General category and then select Tables and Column Specification. Click on the ellipsis button for this column which will open a new dialogue for Tables and Columns.
- Select the Primary Key table from dropdown.
- Now click on the first row beneath this drop down, which will let you to select the Primary Key column of the table which you want to reference to.
- Now select the column on which you want to create Foreign Key constraint in the first row of Foreign Key Table.
- If you want to create more relations between these two tables, then you can also specify them by clicking in the next rows and then selecting required columns.
- After creating all the Relations, click on OK and then close the Foreign Key Relationships dialogue.
- Now save your table, which will give you a message that on saving this table, the other table (Referenced table) will also be saved with the changes regarding Foreign Key constraint. Just click Yes which will save both the tables.
- Here is snapshot of the data I have in [tbemp] table
- Now if you try to insert a row in [tbinfo] table (which we have created) with any value for [eid] column which is not present in the [eid] column of [tbemp] table, you will get following error.
- But if you try to give any value to [eid] column of [tbinfo] table which is present in the [eid] column of [tbemp], or a NULL value, you will not get any error.
Incoming search terms:
- how to set foreign key in sql server 2008 (2)
- add new column as foreign key in sql server 2008 (1)
- create foreign key sql server 2008 (1)
- how to create foreign keys in sql server 2008 (1)
- SQL Server Foreign key tutorial designer (1)