SQL Server > Table

Set Primary key and Foreign key relationship in SQL Server

How to add primary key and foreign key relationship in a Sql Server database table?


In order to demonstrate this, we have created two tables as shown below

Personal Details table

Accounts table

Here the primary personal data is being stored into PersonalDetails table and finance related details of the person is being stored into Accounts table.

Accounts table is linked with the PersonalDetails table with PersonalDetailsId column in the Accounts table. That means the Accounts.PersonalDetailsId column will hold the value of PersonalDetails.PersonalDetailsId for each record in the PersonalDetails table.

Here Primary key table is PersonalDetails and Foreign key table is Accounts as Accounts table is holding the primary key value into its PersonalDetailsId column.

To make this relationship, the PersonalDetailsId column must be primary key in the PersonalDetails table (learn how to set the column as primary key in the previous How to points).

To create the primary key and foreign key relationship, right click the foreign key table columns (Accounts table) and select Relationships…

In the Foreign Key Relationships dialog box, click Add button.

That will by default add a relationship in the left panel. Now click on the … button highlighted above.

Now click on the Primary key table dropdown and select PersonaDetails and then click on the rows below to select PersonalDetailsId from the columns link and then in the Foreign key table below the table name and click the rows and select PersonalDetialsId, now click OK as shown below.

Then again click on Close button of the Foreign Key Relationships dialog box. At last click on the Save icon that will show below dialog box on which click yes to save the database table relationship just now created.

To confirm whether the foreign key has been created, explore the columns of the Accounts table in the Object Explorer and see the gray key icon against PersonalDetialsId column.


 Views: 1780 | Post Order: 26



Write for us