What types of relationships between tables exist in relational databases?

In relational databases, there are three main types of relationships between tables:

1. One-to-one (1:1) — each record in the first table corresponds to exactly one record in the second. For example, a users table and a profiles table, where each user has one profile.

2. One-to-many (1:N) — one record in the first table corresponds to many records in the second. For example, an orders table and an order items table.

3. Many-to-many (M:N) — records in both tables can have multiple relationships with each other. To implement such a relationship, an intermediate table (join table) is used, which contains foreign keys from both tables.

Example of a many-to-many relationship:

-- Students table
CREATE TABLE Students (
  StudentID INT PRIMARY KEY,
  Name VARCHAR(100)
);

-- Courses table
CREATE TABLE Courses (
  CourseID INT PRIMARY KEY,
  Title VARCHAR(100)
);

-- Intermediate table for many-to-many relationship
CREATE TABLE StudentCourses (
  StudentID INT,
  CourseID INT,
  PRIMARY KEY (StudentID, CourseID),
  FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
  FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);