relationships between tables in a MySQL database

In this tutorial, we will discuss the different types of relationships between tables in a MySQL database and the queries used to create and manage these relationships.

1. One-to-One Relationship: A one-to-one relationship exists when a single record in one table is related to a single record in another table. This relationship is created using a foreign key constraint.

Example:

CREATE TABLE employee (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  address VARCHAR(100)
);

CREATE TABLE personal_info (
  id INT PRIMARY KEY,
  phone_number VARCHAR(15),
  email VARCHAR(50)
);

ALTER TABLE employee
ADD CONSTRAINT fk_employee_personal_info
FOREIGN KEY (id) REFERENCES personal_info(id);

2. One-to-Many Relationship: A one-to-many relationship exists when a single record in one table can be related to multiple records in another table. This relationship is created using a foreign key constraint.

Example:

CREATE TABLE department (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);

CREATE TABLE employee (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  address VARCHAR(100),
  department_id INT,
  FOREIGN KEY (department_id) REFERENCES department(id)
);

3. Many-to-Many Relationship: A many-to-many relationship exists when multiple records in one table can be related to multiple records in another table. This relationship is created using a junction table.

Example:

CREATE TABLE course (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);

CREATE TABLE student (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);

CREATE TABLE course_student (
  course_id INT,
  student_id INT,
  PRIMARY KEY (course_id, student_id),
  FOREIGN KEY (course_id) REFERENCES course(id),
  FOREIGN KEY (student_id) REFERENCES student(id)
);

4. Creating Relationships: To create a relationship between two tables, you can use the FOREIGN KEY constraint. This constraint is used to link two tables together based on a common column.

Example:

CREATE TABLE order_details (
  order_id INT,
  product_id INT,
  quantity INT,
  price DECIMAL(10, 2),
  FOREIGN KEY (order_id) REFERENCES orders(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

5. Managing Relationships: To manage relationships between tables, you can use the following SQL commands:

• ALTER TABLE: To add or remove foreign key constraints.
• DELETE: To delete records from a table while maintaining referential integrity.
• UPDATE: To update records in a table while maintaining referential integrity.

Example:

-- Add a foreign key constraint
ALTER TABLE employee
ADD CONSTRAINT fk_employee_department
FOREIGN KEY (department_id) REFERENCES department(id);

-- Remove a foreign key constraint
ALTER TABLE employee
DROP FOREIGN KEY fk_employee_department;

-- Delete records with a CASCADE option
DELETE FROM employee WHERE id = 1;

-- Update records with a CASCADE option
UPDATE department SET name = 'New Department Name' WHERE id = 1;

In this tutorial, we have discussed the different types of relationships between tables in a MySQL database and the queries used to create and manage these relationships.