SQL Server Fundamentals: Understanding Relationships

Understanding Primary Keys

A primary key is a column or set of columns that uniquely identifies each row in a table.

It ensures that no two rows have the same key value, which is crucial for data accuracy.

• Must contain unique values.
• Cannot contain NULL values.
• Each table can have only one primary key.

Understanding Foreign Keys

A foreign key is a column or set of columns in one table that refers to the primary key in another table.

It creates a link between the two tables and enforces referential integrity.

• Foreign keys can contain duplicate values.
• They can accept NULL values unless restricted.
• They ensure that referenced data exists in the parent table.

Types of Relationships in SQL Server

There are three main types of relationships between tables: one-to-one, one-to-many, and many-to-many.

Each type models different real-world scenarios and affects how data is stored and accessed.

• One-to-One: Each row in Table A relates to one row in Table B.
• One-to-Many: One row in Table A relates to multiple rows in Table B.
• Many-to-Many: Rows in Table A relate to multiple rows in Table B and vice versa, usually implemented with a junction table.

Implementing Relationships with SQL Server Constraints

SQL Server uses constraints to enforce relationships between tables.

Primary key and foreign key constraints ensure data integrity and prevent orphaned records.

• PRIMARY KEY constraint defines the unique identifier for a table.
• FOREIGN KEY constraint links a column to a primary key in another table.
• ON DELETE and ON UPDATE actions control behavior when related data changes.

Example: Creating a One-to-Many Relationship

Let's create two tables: Customers and Orders, where each customer can have multiple orders.

We will define a primary key on Customers and a foreign key on Orders referencing Customers.