Database designs are closely related to database relationships, the association between two columns in one or more tables. Relationships are defined on the basis of matching key columns. In SQL server, these relationships are defined using Primary Key-Foreign Key constraints. A link is created between two tables where the primary key of one table is associated with the foreign key of another table using database relationships.
Consider the following example. Book table (pk_book_id, title, ISBN) is associated with Author (pk_author_id, author_name, phone_no, fk_book_id). One book can have many authors. This relationship can be implemented by using (PK_Author_Id as Primary Key) and (Fk_Author_Id as Foreign Key).
Types of Relationships
a. One-One Relationship (1-1 Relationship)
b. One-Many Relationship (1-M Relationship)
c. Many-Many Relationship (M-M Relationship)
This tech-recipe covers only 1-1 and 1-M relationship.
1. One-One Relationship (1-1 Relationship)
One-to-One (1-1) relationship is defined as the relationship between two tables where both the tables should be associated with each other based on only one matching row. This relationship can be created using Primary key-Unique foreign key constraints.
With One-to-One Relationship in SQL Server, for example, a person can have only one passport. Let’s implement this in SQL Server.
One-to-One Relationship is implemented using dbo.Person(Pk_Person_Id) as the Primary key and dbo.PassportDetails(fk_person_id) as (Unique Key Constraint-Foreign Key).
Therefore, it will always have only one matching row between the Person-PassportDetails table based on the dbo.Person(Pk_Person_Id)-dbo.PassportDetails(Fk_Person_Id) relationship.
1. Create two Tables (Table A & Table B) with the Primary Key on Both the tables.
2. Create Foreign key in Table B which references the Primary key of Table A.
3. Add a Unique Constraint on the Foreign Key column of Table B.
What happens if we try to insert passport details for the same fk_person_id which already exists in the passportDetails table?
We get an error of Unique key violation.
Msg 2627, Level 14, State 1, Line 1 Violation of UNIQUE KEY constraint 'UQ__Passport__04554C334F12BBB9'. Cannot insert duplicate key in object 'dbo.PassportDetails'. The duplicate key value is (3). The statement has been terminated.
2. One-Many Relationship (1-M Relationship)
The One-to-Many relationship is defined as a relationship between two tables where a row from one table can have multiple matching rows in another table. This relationship can be created using Primary key-Foreign key relationship.
In the One-to-Many Relationship in SQL Server, for example, a book can have multiple authors. Let’s implement this in SQL Server.
One-to-Many Relationship is implemented using dbo.Book(Pk_Book_Id) as the Primary Key and dbo.Author (Fk_Book_Id) as (Foreign Key). Thus, it will always have only One-to-Many (One Book-Multiple Authors) matching rows between the Book-Author table based on the dbo.Book (Pk_Book_Id)-dbo.Author(Fk_Book_Id) relationship.
1. Create two Tables (Table A & Table B) with the Primary Key on both the tables.
2. Create a Foreign key in Table B which references the Primary key of Table A.