The following diagram shows the database schema that you are going to implement for our simple example: Similarly, the Employee_Office lookup table will have two foreign keys that reference the primary keys of the Employee and Office tables.Īdding Multiple Foreign Keys with SQL Server For the Employee table, you need to add foreign keys that reference the primary keys of the Department table and the Insurance table. In scenarios where a table can have relationships with multiple other tables, you will need to add multiple foreign keys to a table. Hence, in a one-to-many relationship between the Department and Employee tables we discussed above, the Employee table will store a foreign key that references the primary key of the Department table.

The foreign key references the primary key or the unique identity key of the table that is on one side of the table. In relational databases, foreign keys implement different types of database relationships.įor example, to implement a one-to-many relationship in SQL Server, you need to add a foreign key in a table that is on the many sides of the one-to-many relationship. What are Foreign Keys and Why you Need Multiple Foreign Keys? Let’s now see how foreign keys fit in this discussion. Then you create a one-to-many relationship between the Employee and Employee_Office tables and the Office and Employee_Office tables. You would create a new look-up table Employee_Office. Let’s consider creating a many-to-many relationship between the Employee and Office tables. The original tables contain a one-to-many relationship with the look-up table. To implement the many-to-many relationship, you need to create a look-up table that connects two tables in a many-to-many relationship. In this case, the Office and Employee tables would have a many-to-many relationship. Besides, an office can have multiple employees.

Similarly, the Insurance and Employee tables would have a one-to-many relationship.Īlso, the organization may have multiple offices, and an employee may work at more than one office. The Department and Employee tables would have a one-to-many relationship. However, departments and insurance companies can have multiple employees. We assume that an employee can belong to a single department only and can subscribe to only one insurance company. One-to-many and many-to-many relations are far more frequent.Ĭonsider a scenario where you store information about an organization’s employees, departments, employees’ insurance companies, and employees’ office addresses.

Conversely, each address in the Address table will belong to only one record in the User table. Each record in the User table will correspond to one record in the Address table. Each record in a particular table relates to exactly one record in another table.įor example, you can store the user’s name, id, and gender in one table ( User table) and their addresses will be stored in the different table ( Address table). A table can have one-to-one, one-to-many, or many-to-many relations with another table, depending upon the type of data you want to store.Ī one-to-one relationship is simple and rarely used. SQL server adds foreign keys to implement relations between tables in a relational database.

Foreign key phpmyadmin