Before moving forward to the subject “Add foreign key relationship from table designer” I want to shed some light on the following stuff about database structure. In database creation and management, we know that it is very important to create tables in such a way which are easy to understand. Table structure should be simple to understand. So how would you create tables which are simple to manage and understand.
Obviously by keeping the number of columns less. So how can you show more information about the object by limiting the number of columns ? For this Primary key and Foreign comes into play. Using Primary key and Foreign key we can show the relationship between tables limiting the size of the table. Using this approach it is easy to query tables and managing them.
Suppose there are two tables named teacher table and subject table as given below:
teacher table :
subject table :
If you notice we have added Primary key to Id in both table. Now here we are going to add foreign key on Subject_Id in teacher table referencing Id (Primary key) in Subject table. We are going to add foreign key using table designer.
For this right click the table click on “Relationships”
Expand “tables and Columns Specification” and click on edit icon as shown below :
Edit icon is given at the right most side of the title “tables and Columns Specification”
You will see two settings
- Primary key table
- Foreign key table
Forign key table will be “teacher” as we are adding foreign key in “teacher” table.
Now select Subect_ID as foreign key in “teacher” table.
And select “subject” as Primary key table and add Id as Primary key and press OK.
Now you can see the relationship between both the table in “Table and Column Specification” as shown below :
In this way we have added foreign key relationship between tables.
Difference between Primary key and foreign key:
- Primary key uniquely identifies the row in table.
- The value in the column is the combination of “Not Null” and “Unique” constraints.
- “Not Null” constraint means that there should be no null value inside Primary key column.
- “Unique” constraint means that each row of the column should contain the unique value as Primary key uniquely identifies each row in table.
- The Primary key column is used by the Foreign Key column for referencing. It means that the Primary key column will be the Foreign key Column in Foreign key table when adding table relationships.
- As we have mentioned above the Foreign key column references Primary key Column in another table.
- We can have one or more Foreign key columns in a table.
- The value in the Foreign key need not to be unique. It means it can contain duplicate values.
Latest posts by Amit Kulat (see all)
- SQL Constraint Interview Questions in Sql – Explained with Examples. - November 12, 2017
- Add Foreign Key Relationship using Sql Queries in Sql Server - October 29, 2017
- Add Foreign Key Relationship from Table Designer in Sql Server - October 17, 2017