One-to-Many: Customer and Orders

basic

One customer places many orders

CUSTOMERS:

customer_id | name | email

1 | John Smith | john@example.com

2 | Jane Doe | jane@example.com

ORDERS:

order_id | customer_id (FK) | order_date | total

101 | 1 | 2024-01-15 | 150.00

102 | 1 | 2024-01-20 | 200.00

103 | 2 | 2024-01-18 | 75.00

CREATE TABLE orders (

order_id INTEGER PRIMARY KEY,

customer_id INTEGER NOT NULL,

order_date DATE DEFAULT CURRENT_DATE,

total DECIMAL(10,2) NOT NULL,

FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE RESTRICT

);

One-to-Many: Customer 1 has 2 orders (101, 102).

FK in "many" side (orders).

RESTRICT prevents orphaned orders.

FK in orders table references customers. RESTRICT prevents deleting customer with existing orders.

All

Many-to-Many: Students and Courses

intermediate

Students enroll in many courses, courses have many students

STUDENTS:

student_id | name

1 | Alice

2 | Bob

COURSES:

course_id | course_name

101 | Math

102 | Physics

ENROLLMENTS (junction table):

enrollment_id | student_id (FK) | course_id (FK) | grade

1 | 1 | 101 | A

2 | 1 | 102 | B

3 | 2 | 101 | B