Cardinality
- SQL course
- Expert
- Lesson 2/45
- 5 min
Learn one-to-one, one-to-many and many-to-many.
Cardinality
Cardinality states how many entities take part in a relationship: one-to-one (1:1), one-to-many (1:N) or many-to-many (M:N). The right cardinality drives the right table layout.
-- 1:1 users.profile_id -> profiles.id
-- 1:N orders.customer_id -> customers.id
-- M:N students <-> courses (junction table)Three basic cardinality types exist.
Cardinality — details
An M:N relationship needs a junction table between the two entities. It holds the pair of foreign keys.
Practice Cardinality
Create the M:N enrollments junction table for students and courses. Add two foreign keys.
Starter code
-- junction table
CREATE TABLE enrollmentsSample solution
CREATE TABLE enrollments (
student_id INT REFERENCES students(id),
course_id INT REFERENCES courses(id),
PRIMARY KEY (student_id, course_id)
);Expected output
CREATE TABLE
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 Add a foreign key
Question
FOREIGN KEY (customer_id) REFERENCES ____
Answer
FOREIGN KEY (customer_id) REFERENCES orders(id)Explanation: orders(id) is the right answer.
2 What does an M:N relationship need?
- An extra column
- A new index
- A trigger
- A junction table Correct answer
Explanation: An M:N relationship needs a junction table.
3 Cardinality: match the terms
| Term | Meaning |
|---|---|
1:1 |
one to one |
1:N |
one to many |
M:N |
many to many |
junction |
bridge |