How do you model one-to-many and many-to-many relationships in SQL?
How do you model one-to-many and many-to-many relationships in SQL?
Relationships are modelled with foreign keys. The cardinality decides where the key goes.
The foreign key lives on the many side.
CREATE TABLE authors (id BIGINT PRIMARY KEY, name VARCHAR(100) NOT NULL);
CREATE TABLE books (
id BIGINT PRIMARY KEY,
title VARCHAR(200) NOT NULL,
author_id BIGINT NOT NULL REFERENCES authors(id) -- one author, many books
);
CREATE INDEX idx_books_author ON books (author_id);
A foreign key that is also UNIQUE (or the primary key itself): optional extension data, security-sensitive columns split out, or large rarely-read columns.
CREATE TABLE user_profiles (
user_id BIGINT PRIMARY KEY REFERENCES users(id),
bio TEXT
);
Needs a junction (association) table with two foreign keys.
CREATE TABLE students (id BIGINT PRIMARY KEY, name VARCHAR(100) NOT NULL);
CREATE TABLE courses (id BIGINT PRIMARY KEY, title VARCHAR(100) NOT NULL);
Đăng nhập để tham gia thảo luận cùng mọi người.
Chưa có bình luận nào
Hãy là người đầu tiên chia sẻ suy nghĩ của bạn.