Create Foreign Key on SQL Table Column using SSMSE 2005
Please consider the previous tutorial: Create SQL Table Foreign Key using SSMSE 2005, as this is the next part of that tutorial. In the previous article we discussed about the role of primary key and foreign key in relational database management system. Here we will learn in steps how to set foreign key relationship between two database tables i.e. tblUsers and tblProfiles that were created in the previous tutorial. Both tables have UserId column as an unqiue and common attribute. Moreover it can act as primary key separately for both tables. But as we discussed earlier there must be a relational key between both tables to reduce the indexing and record fetching cost while executing SQL queries. Following are the steps to create the Foreign Key Relationships using SQL Server Management Studio Express 2005:
1. Open the tblProfiles in Design view. Right click on table name in Object Explorer and click on Design option from the menu. This will open the design view of table as shown in figure below:
Key icon at the left side of "userId" column represents that it is a Primary key column. If you don’t know how to set the table column as Primary key then learn how to create primary key in SSMSE 2005.
2. Second step is to create the foreign key relationship between tblUsers and tblProfiles. Right click on UserId column and then click Relationships option from the menu. It will open the Foreign Key Relationships dialog box having various properties to bind the relationship between the database tables as show in figure below:
3. After opening the Foreign Key Relationships dialog box you can add new foreign key by using the dialog box. Next you need to set the Tables and columns specifications. It will define the base table for primary key and foreign key to maintain the top level indexes and second level indexes according to specifications. Following figure shows the properties dialog box for Foreign Key Relationships:
4. To add the tables and columns specifications select the second associated group of properties. It will show the button to open the Tables and Columns specifications dialog box. Choose the relevant base table and column to create the foreign key relationship between the database tables as shown in the figure below:
If you have no idea about foreign key, then learn why to create it on SQL table column.