master-detail relationship in MySQL

In this tutorial, we will learn how to create a master-detail relationship in MySQL and perform various operations using SQL queries.

A master-detail relationship is a one-to-many relationship between two tables. In this relationship, one table (the master) contains the primary key, and the other table (the detail) contains the foreign key that references the primary key of the master table.

Let's create two tables to demonstrate the master-detail relationship:

1. Create a master table named "cities" to store information about cities:

CREATE TABLE cities (
    city_id INT AUTO_INCREMENT PRIMARY KEY,
    city_name VARCHAR(255) NOT NULL,
    country VARCHAR(255) NOT NULL
);

2. Create a detail table named "employees" to store information about employees, including a foreign key that references the "city_id" in the "cities" table:

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone_number VARCHAR(20),
    city_id INT,
    FOREIGN KEY (city_id) REFERENCES cities(city_id)
);

Now that we have our tables set up, let's perform some operations:

1. Insert data into the "cities" table:

INSERT INTO cities (city_name, country) VALUES ('New York', 'USA');
INSERT INTO cities (city_name, country) VALUES ('London', 'UK');
INSERT INTO cities (city_name, country) VALUES ('Tokyo', 'Japan');

2. Insert data into the "employees" table:

INSERT INTO employees (first_name, last_name, email, phone_number, city_id) VALUES ('John', 'Doe', 'john.doe@example.com', '123-456-7890', 1);
INSERT INTO employees (first_name, last_name, email, phone_number, city_id) VALUES ('Jane', 'Smith', 'jane.smith@example.com', '098-765-4321', 2);
INSERT INTO employees (first_name, last_name, email, phone_number, city_id) VALUES ('Mike', 'Johnson', 'mike.johnson@example.com', '555-555-5555', 3);

3. Retrieve data from both tables using a JOIN query:

SELECT c.city_name, c.country, e.first_name, e.last_name, e.email, e.phone_number
FROM employees e
JOIN cities c ON e.city_id = c.city_id;

4. Update data in the "employees" table:

UPDATE employees
SET email = 'john.doe@newemail.com'
WHERE employee_id = 1;

5. Delete data from the "employees" table:

DELETE FROM employees
WHERE employee_id = 3;

That's it! You have now learned how to create a master-detail relationship in MySQL and perform various operations using SQL queries.