Relationships

---

Relationships in SQL Server

A relationship, in the context of databases, is a situation that exists between two relational database tables when one table has a foreign key that references the primary key of the other table.

Relationships in SQL are of 3 types:

1. One to one relationships

2. One to many relationships

3. Many to many relationships

1.One-to-one relationships

In a one-to-one relationship, one record in a table is associated with one and only one record in another table.

EAXMPLE SYNTAX

Use a foreign key to the referenced table
student: student_id, first_name, last_name, address_id
address: address_id, address, city, zipcode, student_id # you can have a
                                                        # "link back" if you need
You must also put a unique constraint on the foreign key column (addess.student_id) to prevent multiple rows in the child table (address) from relating to the same row in the referenced table (student).

2. One-to-many relationships

In a one-to-many relationship, one record in a table can be associated with one or more records in another table.

EXAMPLE SYNTAX

 Use a foreign key on the many side of the relationship linking back to the "one" side:

teachers: teacher_id, first_name, last_name # the "one" side
classes:  class_id, class_name, teacher_id  # the "many" side

3. Many-to-many relationships

A many-to-many relationship occurs when multiple records in a table are associated with multiple records in another table.

EXAMPLE SYNTAX

Use a junction table:

student: student_id, first_name, last_name
classes: class_id, name, teacher_id
student_classes: class_id, student_id     # the junction table