Database Relationships
A relationship describes how rows in one table are connected to rows in another. Start with the business rule: can each customer have several orders, and must every order belong to a customer?
Types of Relationships
•
One-to-one: one user has at most one profile. A foreign key with a UNIQUE constraint can enforce this.
•
One-to-many: one customer has many orders. Store the customer’s key in the orders table.
•
Many-to-many: a student can take several courses, and a course can have several students. Use a junction table rather than a comma-separated list.
Example: Students and Courses
PostgreSQL example. The composite primary key prevents the same student from enrolling in the same course twice.
CREATE TABLE students (id bigint PRIMARY KEY);
CREATE TABLE courses (id bigint PRIMARY KEY);
CREATE TABLE enrollments (
student_id bigint REFERENCES students(id),
course_id bigint REFERENCES courses(id),
PRIMARY KEY (student_id, course_id)
);
Plain Text
복사
Things to Check
A foreign key checks that the referenced row exists. It does not automatically make a relationship one-to-one. Use NOT NULL when a relationship is required, and choose deletion behavior deliberately: deleting a customer should not accidentally erase records that must be retained.
Foreign keys and indexes solve different problems. In PostgreSQL, a foreign key does not automatically create an index on the referencing columns; add one when the query and update workload justify it.
