What are relationships between tables?
Relationships between tables are logical connections between data in different tables that let SQL "understand" how to join rows in queries.
They're built through keys, special fields that link tables together.
1. Primary key (PRIMARY KEY)
This is a unique identifier for a row in a table. Each record has its own non-repeating value. Example:
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50)
);The id field is the user's unique identifier.
2. Foreign key (FOREIGN KEY)
This is a field that references the primary key of another table. It's what actually creates the relationship between tables. Example:
CREATE TABLE orders (
id INT PRIMARY KEY,
user_id INT,
FOREIGN KEY (user_id) REFERENCES users(id)
);Here, user_id in orders is linked to id in users.
Every order "belongs to" a specific user.
3. Types of relationships between tables
| Relationship type | Description | Example |
|---|---|---|
| One-to-one (1:1) | Each record in table A corresponds to exactly one record in table B. | A user, a passport |
| One-to-many (1:N) | One record in table A corresponds to many records in table B. | A user, orders |
| Many-to-many (M:N) | Each record in A can relate to several in B and vice versa. | Students, courses |
4. Why relationships matter
- To avoid duplicating data;
- To enforce integrity (e.g. you can't create an order without an existing user);
- To combine data correctly in
JOINqueries.
Summary: A relationship between tables is a logical link through keys that shows which data relates to which. A primary key uniquely identifies a record, a foreign key points to it from another table, and the relationships themselves make the database structured, consistent, and safe.
Short Answer
Interview readyA concise answer to help you respond confidently on this topic during an interview.