MySQL Table Relationships
Kennedy Waweru
2/24/2022
Course Outline
Organising related tables into a schema
Learn Foreign Keys
Learn SQL Joins and use JOIN to retrieve data from multiple tables
Learn
ON DELETE CASCADEandON DELETE SET NULL
Lets create our first database
CREATE DATABASE IF NOT EXISTS company_db; We have successfully created database/schema using SQL command.
Show databases
SHOW DATABASES;| Database |
|---|
| company_db |
| day3 |
| demo_db |
| denaco_training |
| employee |
| first_database |
| information_schema |
| intro |
| manu_training |
| mysql |
Choose which database to use when evaluating commands
-- USE company_db;
USE employee;After selecting the database now we can query what tables are in the db
Show Tables
SHOW TABLES;| Tables_in_employee |
|---|
| blog_posts |
| departments |
| devices |
| employees |
| employees_has_departments |
| titles |
| users |
The output of the query above should be 0 rows because it’s a clean database and we have not created any tables.
Foreign Key
A foreign key is a column or group of columns in a table that links to a column or group of columns in another table. The foreign key places constraints on data in the related tables, which allows MySQL to maintain referential integrity.
A foreign key is a primary key of one table stored as a column in another table to establish a relationship between the two tables.
Employee Schema
Employee Schema
To replicate this table run the following queries:
-- -----------------------------------------------------
-- Schema employee
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema employee
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `employee` DEFAULT CHARACTER SET utf8 ;
USE `employee` ;
-- -----------------------------------------------------
-- Table `employees`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `employees` (
`emp_id` INT NOT NULL AUTO_INCREMENT,
`dob` DATE NOT NULL,
`first_name` VARCHAR(45) NULL,
`last_name` VARCHAR(45) NULL,
`phone` VARCHAR(45) NULL,
`join_date` DATE NULL,
`email` VARCHAR(255) NULL,
PRIMARY KEY (`emp_id`),
UNIQUE INDEX `emp_id_UNIQUE` (`emp_id` ASC) VISIBLE,
UNIQUE INDEX `email_UNIQUE` (`email` ASC) VISIBLE)
ENGINE = InnoDB
COMMENT = 'Employee personal details';
-- -----------------------------------------------------
-- Table `departments`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `departments` (
`dep_id` INT NOT NULL AUTO_INCREMENT,
`dept_name` VARCHAR(45) NULL,
PRIMARY KEY (`dep_id`),
UNIQUE INDEX `dept_name_UNIQUE` (`dept_name` ASC) VISIBLE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `devices`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `devices` (
`dev_id` INT NOT NULL AUTO_INCREMENT,
`model` VARCHAR(45) NULL,
`make` VARCHAR(45) NULL,
`owndate` DATE NULL,
`emp_id` INT NOT NULL,
PRIMARY KEY (`dev_id`),
INDEX `fk_devices_employees1_idx` (`emp_id` ASC) VISIBLE,
CONSTRAINT `fk_devices_employees1`
FOREIGN KEY (`emp_id`)
REFERENCES `employees` (`emp_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `employees_has_departments`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `employees_has_departments` (
`emp_id` INT NOT NULL,
`dep_id` INT NOT NULL,
PRIMARY KEY (`emp_id`, `dep_id`),
INDEX `fk_employees_has_departments_departments1_idx` (`dep_id` ASC) VISIBLE,
INDEX `fk_employees_has_departments_employees_idx` (`emp_id` ASC) VISIBLE,
CONSTRAINT `fk_employees_has_departments_employees`
FOREIGN KEY (`emp_id`)
REFERENCES `employees` (`emp_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_employees_has_departments_departments1`
FOREIGN KEY (`dep_id`)
REFERENCES `departments` (`dep_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `titles`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `titles` (
`emp_id` INT NOT NULL,
`title` VARCHAR(45) NULL,
`from_date` DATE NULL,
`to_date` DATE NULL,
UNIQUE INDEX `title_UNIQUE` (`title` ASC) VISIBLE,
PRIMARY KEY (`emp_id`),
CONSTRAINT `fk_titles_employees1`
FOREIGN KEY (`emp_id`)
REFERENCES `employees` (`emp_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
SHOW TABLES
To see if our tables have been created
SHOW TABLES;| Tables_in_employee |
|---|
| blog_posts |
| departments |
| devices |
| employees |
| employees_has_departments |
| titles |
| users |
To see the underlying structure of a table use the DESCRIBE keyword.
DESCRIBE employees;| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| emp_id | int | NO | PRI | NA | auto_increment |
| dob | date | NO | NA | ||
| first_name | varchar(45) | YES | MUL | NA | |
| last_name | varchar(45) | YES | MUL | NA | |
| phone | varchar(45) | YES | NA | ||
| join_date | date | YES | NA | ||
| varchar(255) | YES | UNI | NA |
Insert one record per table
DESCRIBE employees;| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| emp_id | int | NO | PRI | NA | auto_increment |
| dob | date | NO | NA | ||
| first_name | varchar(45) | YES | MUL | NA | |
| last_name | varchar(45) | YES | MUL | NA | |
| phone | varchar(45) | YES | NA | ||
| join_date | date | YES | NA | ||
| varchar(255) | YES | UNI | NA |
insert into employees
(dob, first_name, last_name, phone, join_date, email)
INSERT INTO employees
(dob, first_name, last_name, phone, join_date, email)
VALUES
('1998-05-28','Kennedy','Waweru','0724442515','2016-09-25','kennedywaweru17@gmail.com');
INSERT INTO titles
(emp_id, title, from_date, to_date)
VALUES (1, 'Manager', '2018-05-21','2021-12-31');
INSERT INTO departments
(dept_name)
VALUES ("Operations");
INSERT INTO devices
(model, make, owndate, emp_id)
VALUES ("Dell","Inspiron Mini Laptop","2019-09-03",1);
INSERT INTO employees_has_departments
(emp_id, dep_id)
VALUES(1,1);Now that we have seen how to insert one record, lets insert dummy data to allow us to work with tables in details:
insert into employees (dob, first_name, last_name, phone, join_date, email) values ('1966-04-14', 'Purcell', 'Madine', '8346212810', '2007-03-28', 'pmadine0@spotify.com');
insert into employees (dob, first_name, last_name, phone, join_date, email) values ('1993-01-22', 'Izzy', 'Gribble', '3301072422', '2005-01-15', 'igribble1@mtv.com');
insert into employees (dob, first_name, last_name, phone, join_date, email) values ('1970-01-20', 'Fey', 'Cuffley', '4436832691', '1999-04-04', 'fcuffley2@chron.com');
insert into employees (dob, first_name, last_name, phone, join_date, email) values ('1970-03-09', 'Hillie', 'Othen', '3811418681', '2019-03-08', 'hothen3@nyu.edu');
insert into employees (dob, first_name, last_name, phone, join_date, email) values ('1980-05-21', 'Randy', 'Lammertz', '3799689105', '2001-02-13', 'rlammertz4@imdb.com');
insert into employees (dob, first_name, last_name, phone, join_date, email) values ('1982-02-05', 'Therine', 'Aubery', '8881054012', '2002-07-13', 'taubery5@woothemes.com');
insert into employees (dob, first_name, last_name, phone, join_date, email) values ('1988-11-13', 'Gwenore', 'Blenkinship', '9669448540', '1998-09-03', 'gblenkinship6@businesswire.com');
insert into employees (dob, first_name, last_name, phone, join_date, email) values ('1965-10-18', 'Dov', 'Anmore', '9616126958', '2015-08-15', 'danmore7@mediafire.com');
insert into employees (dob, first_name, last_name, phone, join_date, email) values ('1986-04-15', 'Stephine', 'Mayzes', '7343609349', '2017-07-20', 'smayzes8@home.pl');
insert into employees (dob, first_name, last_name, phone, join_date, email) values ('1979-04-03', 'Aron', 'Telfer', '5992416826', '2010-05-05', 'atelfer9@indiatimes.com');
-- Add job titles to the titles table. An employee can only have one title
insert into titles (emp_id, title, from_date, to_date) values (3, 'Social Worker', '1998-02-05', '2019-04-23');
insert into titles (emp_id, title, from_date, to_date) values (5, 'Civil Engineer', '1998-06-04', '2020-06-21');
insert into titles (emp_id, title, from_date, to_date) values (10, 'Staff Scientist', '1997-03-06', '2021-01-07');
insert into titles (emp_id, title, from_date, to_date) values (4, 'VP Quality Control', '1997-03-10', '2020-04-25');
insert into titles (emp_id, title, from_date, to_date) values (2, 'Human Resources Assistant II', '1997-08-17', '2020-02-28');
insert into titles (emp_id, title, from_date, to_date) values (9, 'Human Resources Manager', '1997-07-05', '2020-01-18');
insert into titles (emp_id, title, from_date, to_date) values (1, 'Senior Cost Accountant', '1998-10-19', '2018-08-09');
insert into titles (emp_id, title, from_date, to_date) values (7, 'Database Administrator IV', '1997-07-17', '2019-11-17');
insert into titles (emp_id, title, from_date, to_date) values (6, 'Professor', '1998-01-14', '2021-10-12');
insert into titles (emp_id, title, from_date, to_date) values (8, 'Financial Analyst', '1999-09-21', '2019-09-06');
-- Add departments
insert into departments (dept_name) values ('Engineering');
insert into departments (dept_name) values ('Human Resources');
insert into departments (dept_name) values ('Human Resources');
insert into departments (dept_name) values ('Accounting');
insert into departments (dept_name) values ('Business Development');
insert into departments (dept_name) values ('Human Resources');
-- Insert the device details, and the emp_id of the owner
insert into devices (model, make, owndate, emp_id) values ('Aspen', 'Chrysler', '2011-01-21', 5);
insert into devices (model, make, owndate, emp_id) values ('Accent', 'Hyundai', '2007-01-17', 9);
insert into devices (model, make, owndate, emp_id) values ('90', 'Audi', '2009-02-10', 3);
insert into devices (model, make, owndate, emp_id) values ('Windstar', 'Ford', '2013-08-19', 6);
insert into devices (model, make, owndate, emp_id) values ('Pilot', 'Honda', '2009-01-01', 11);
insert into devices (model, make, owndate, emp_id) values ('Crown Victoria', 'Ford', '2014-02-23', 5);
insert into devices (model, make, owndate, emp_id) values ('Bonneville', 'Pontiac', '2017-01-10', 11);
insert into devices (model, make, owndate, emp_id) values ('CX-9', 'Mazda', '2008-11-23', 2);
insert into devices (model, make, owndate, emp_id) values ('Corvair', 'Chevrolet', '2009-10-10', 1);
insert into devices (model, make, owndate, emp_id) values ('S4', 'Audi', '2019-09-15', 8);
-- Create the many-to-many relationships
insert into employees_has_departments (emp_id, dep_id) values (11, 6);
insert into employees_has_departments (emp_id, dep_id) values (5, 2);
insert into employees_has_departments (emp_id, dep_id) values (11, 5);
insert into employees_has_departments (emp_id, dep_id) values (11, 5);
insert into employees_has_departments (emp_id, dep_id) values (9, 2);
insert into employees_has_departments (emp_id, dep_id) values (3, 1);
insert into employees_has_departments (emp_id, dep_id) values (4, 5);
insert into employees_has_departments (emp_id, dep_id) values (8, 1);
insert into employees_has_departments (emp_id, dep_id) values (6, 1);
Retrieving Data
Suppose we want to retrieve all employees and the devices they own. If they do not own a device, exclude them from results.
Solution: Use an INNER JOIN or JOIN
SELECT * FROM employees
JOIN devices ON employees.emp_id = devices.emp_id;| emp_id | dob | first_name | last_name | phone | join_date | dev_id | model | make | owndate | emp_id | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 1998-05-28 | Kennedy | Waweru | 0724442515 | 2016-09-25 | kenwaweru@gmail.com | 1 | Dell | Inspiron Mini Laptop | 2019-09-03 | 1 |
| 5 | 1970-03-09 | Hillie | Othen | 3811418681 | 2019-03-08 | hothen3@nyu.edu | 2 | Aspen | Chrysler | 2011-01-21 | 5 |
| 9 | 1965-10-18 | Dov | Anmore | 9616126958 | 2015-08-15 | danmore7@mediafire.com | 3 | Accent | Hyundai | 2007-01-17 | 9 |
| 3 | 1993-01-22 | Izzy | Gribble | 3301072422 | 2005-01-15 | igribble1@mtv.com | 4 | 90 | Audi | 2009-02-10 | 3 |
| 6 | 1980-05-21 | Randy | Lammertz | 3799689105 | 2001-02-13 | rlammertz4@imdb.com | 5 | Windstar | Ford | 2013-08-19 | 6 |
| 11 | 1979-04-03 | Aron | Telfer | 5992416826 | 2010-05-05 | atelfer9@indiatimes.com | 6 | Pilot | Honda | 2009-01-01 | 11 |
| 5 | 1970-03-09 | Hillie | Othen | 3811418681 | 2019-03-08 | hothen3@nyu.edu | 7 | Crown Victoria | Ford | 2014-02-23 | 5 |
| 11 | 1979-04-03 | Aron | Telfer | 5992416826 | 2010-05-05 | atelfer9@indiatimes.com | 8 | Bonneville | Pontiac | 2017-01-10 | 11 |
| 2 | 1966-04-14 | Purcell | Madine | 8346212810 | 2007-03-28 | pmadine0@spotify.com | 9 | CX-9 | Mazda | 2008-11-23 | 2 |
| 1 | 1998-05-28 | Kennedy | Waweru | 0724442515 | 2016-09-25 | kenwaweru@gmail.com | 10 | Corvair | Chevrolet | 2009-10-10 | 1 |
Suppose we want to retrieve all employees and the devices they own. If they do not own a device, include them and show null for devices
Solution: Use a LEFT JOIN
SELECT * FROM employees
LEFT JOIN devices ON employees.emp_id = devices.emp_id;| emp_id | dob | first_name | last_name | phone | join_date | dev_id | model | make | owndate | emp_id | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 1998-05-28 | Kennedy | Waweru | 0724442515 | 2016-09-25 | kenwaweru@gmail.com | 1 | Dell | Inspiron Mini Laptop | 2019-09-03 | 1 |
| 1 | 1998-05-28 | Kennedy | Waweru | 0724442515 | 2016-09-25 | kenwaweru@gmail.com | 10 | Corvair | Chevrolet | 2009-10-10 | 1 |
| 2 | 1966-04-14 | Purcell | Madine | 8346212810 | 2007-03-28 | pmadine0@spotify.com | 9 | CX-9 | Mazda | 2008-11-23 | 2 |
| 3 | 1993-01-22 | Izzy | Gribble | 3301072422 | 2005-01-15 | igribble1@mtv.com | 4 | 90 | Audi | 2009-02-10 | 3 |
| 4 | 1970-01-20 | Fey | Cuffley | 4436832691 | 1999-04-04 | fcuffley2@chron.com | NA | NA | NA | NA | NA |
| 5 | 1970-03-09 | Hillie | Othen | 3811418681 | 2019-03-08 | hothen3@nyu.edu | 2 | Aspen | Chrysler | 2011-01-21 | 5 |
| 5 | 1970-03-09 | Hillie | Othen | 3811418681 | 2019-03-08 | hothen3@nyu.edu | 7 | Crown Victoria | Ford | 2014-02-23 | 5 |
| 6 | 1980-05-21 | Randy | Lammertz | 3799689105 | 2001-02-13 | rlammertz4@imdb.com | 5 | Windstar | Ford | 2013-08-19 | 6 |
| 7 | 1982-02-05 | Therine | Aubery | 8881054012 | 2002-07-13 | taubery5@woothemes.com | NA | NA | NA | NA | NA |
| 8 | 1988-11-13 | Gwenore | Blenkinship | 9669448540 | 1998-09-03 | gblenkinship6@businesswire.com | 11 | S4 | Audi | 2019-09-15 | 8 |
FUN Exercise
Write a query that retrieves all employees and their job title.
SELECT * FROM employees e
LEFT JOIN titles t
ON e.emp_id = t.emp_id;| emp_id | dob | first_name | last_name | phone | join_date | emp_id | title | from_date | to_date | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 1998-05-28 | Kennedy | Waweru | 0724442515 | 2016-09-25 | kenwaweru@gmail.com | 1 | Manager | 2018-05-21 | 2021-12-31 |
| 2 | 1966-04-14 | Purcell | Madine | 8346212810 | 2007-03-28 | pmadine0@spotify.com | NA | NA | NA | NA |
| 3 | 1993-01-22 | Izzy | Gribble | 3301072422 | 2005-01-15 | igribble1@mtv.com | 3 | Social Worker | 1998-02-05 | 2019-04-23 |
| 4 | 1970-01-20 | Fey | Cuffley | 4436832691 | 1999-04-04 | fcuffley2@chron.com | 4 | VP Quality Control | 1997-03-10 | 2020-04-25 |
| 5 | 1970-03-09 | Hillie | Othen | 3811418681 | 2019-03-08 | hothen3@nyu.edu | 5 | Civil Engineer | 1998-06-04 | 2020-06-21 |
| 6 | 1980-05-21 | Randy | Lammertz | 3799689105 | 2001-02-13 | rlammertz4@imdb.com | NA | NA | NA | NA |
| 7 | 1982-02-05 | Therine | Aubery | 8881054012 | 2002-07-13 | taubery5@woothemes.com | NA | NA | NA | NA |
| 8 | 1988-11-13 | Gwenore | Blenkinship | 9669448540 | 1998-09-03 | gblenkinship6@businesswire.com | 8 | Financial Analyst | 1999-09-21 | 2019-09-06 |
| 9 | 1965-10-18 | Dov | Anmore | 9616126958 | 2015-08-15 | danmore7@mediafire.com | 9 | Human Resources Manager | 1997-07-05 | 2020-01-18 |
| 10 | 1986-04-15 | Stephine | Mayzes | 7343609349 | 2017-07-20 | smayzes8@home.pl | 10 | Staff Scientist | 1997-03-06 | 2021-01-07 |
Suppose we want to show only the ‘Ghost workers’ i.e Workers without a job title
Solution: Use a LEFT JOIN and NULL
SELECT * FROM employees e
LEFT JOIN titles t
ON e.emp_id = t.emp_id
WHERE t.emp_id IS NULL;| emp_id | dob | first_name | last_name | phone | join_date | emp_id | title | from_date | to_date | |
|---|---|---|---|---|---|---|---|---|---|---|
| 2 | 1966-04-14 | Purcell | Madine | 8346212810 | 2007-03-28 | pmadine0@spotify.com | NA | NA | NA | NA |
| 6 | 1980-05-21 | Randy | Lammertz | 3799689105 | 2001-02-13 | rlammertz4@imdb.com | NA | NA | NA | NA |
| 7 | 1982-02-05 | Therine | Aubery | 8881054012 | 2002-07-13 | taubery5@woothemes.com | NA | NA | NA | NA |
SQL JOINS
Joins help retrieving data from two or more database tables. The tables are mutually related using primary and foreign keys.
Different types of SQL JOINS
(INNER) JOIN: Returns records that have matching values in both tables
LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table
Joining Multiple Tables in SQL
SELECT * FROM employees e
JOIN employees_has_departments ed
ON e.emp_id = ed.emp_id
JOIN departments d
ON ed.dep_id = d.dep_id;
| emp_id | dob | first_name | last_name | phone | join_date | emp_id | dep_id | dep_id | dept_name | |
|---|---|---|---|---|---|---|---|---|---|---|
| 4 | 1970-01-20 | Fey | Cuffley | 4436832691 | 1999-04-04 | fcuffley2@chron.com | 4 | 5 | 5 | Accounting |
| 11 | 1979-04-03 | Aron | Telfer | 5992416826 | 2010-05-05 | atelfer9@indiatimes.com | 11 | 5 | 5 | Accounting |
| 11 | 1979-04-03 | Aron | Telfer | 5992416826 | 2010-05-05 | atelfer9@indiatimes.com | 11 | 6 | 6 | Business Development |
| 5 | 1970-03-09 | Hillie | Othen | 3811418681 | 2019-03-08 | hothen3@nyu.edu | 5 | 2 | 2 | Engineering |
| 9 | 1965-10-18 | Dov | Anmore | 9616126958 | 2015-08-15 | danmore7@mediafire.com | 9 | 2 | 2 | Engineering |
| 1 | 1998-05-28 | Kennedy | Waweru | 0724442515 | 2016-09-25 | kenwaweru@gmail.com | 1 | 1 | 1 | HR |
| 3 | 1993-01-22 | Izzy | Gribble | 3301072422 | 2005-01-15 | igribble1@mtv.com | 3 | 1 | 1 | HR |
| 6 | 1980-05-21 | Randy | Lammertz | 3799689105 | 2001-02-13 | rlammertz4@imdb.com | 6 | 1 | 1 | HR |
| 8 | 1988-11-13 | Gwenore | Blenkinship | 9669448540 | 1998-09-03 | gblenkinship6@businesswire.com | 8 | 1 | 1 | HR |
ON DELETE CASCADE
ON DELETE CASCADE constraint is used in MySQL to delete the rows from the child table automatically, when the rows from the parent table are deleted.
Assuming we have a small website where users can sign in and write blog posts. A user can have many blog posts. A blog post can only belong to one user.
Blog DB
To identify the owner of the blog post, we store the user_id primary key in the blog_posts table as a foreign key.
Suppose that a user deletes their account.
What happens to the blog posts?
Let’s see how we can create these tables
- Create the users table
-- -----------------------------------------------------
-- Table `users`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
`user_id` INT NOT NULL AUTO_INCREMENT,
`user_name` VARCHAR(45) NULL,
`user_email` VARCHAR(244) NULL,
`join_date` DATE NULL,
PRIMARY KEY (`user_id`),
UNIQUE INDEX `user_name_UNIQUE` (`user_name` ASC) VISIBLE,
UNIQUE INDEX `user_email_UNIQUE` (`user_email` ASC) VISIBLE)
ENGINE = InnoDB;
- Create the blog_posts table
-- -----------------------------------------------------
-- Table `blog_posts`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `blog_posts` (
`post_id` INT NOT NULL AUTO_INCREMENT,
`post_title` VARCHAR(200) NOT NULL,
`post_body` VARCHAR(1000) NOT NULL,
`post_image` VARCHAR(45) NULL DEFAULT 'placeholder.jpg',
`user_id` INT NOT NULL,
PRIMARY KEY (`post_id`, `user_id`),
INDEX `fk_blog_posts_users1_idx` (`user_id` ASC) VISIBLE,
CONSTRAINT `fk_blog_posts_users1`
FOREIGN KEY (`user_id`)
REFERENCES `users` (`user_id`)
ON DELETE CASCADE
ON UPDATE NO ACTION)
Note
Note the ON DELETE CASCADE constraint on the above statement
Insert some users
insert into users (user_name, user_email, join_date) values ('Nikos', 'nbuckham0@statcounter.com', '2021-10-05');
insert into users (user_name, user_email, join_date) values ('Orazio', 'oduffyn1@army.mil', '2021-11-03');
insert into users (user_name, user_email, join_date) values ('Daile', 'ddewdney2@virginia.edu', '2021-05-25');
insert into users (user_name, user_email, join_date) values ('Sacha', 'sbakey3@gravatar.com', '2022-01-11');
insert into users (user_name, user_email, join_date) values ('Marcos', 'msenter4@mashable.com', '2021-03-14');
insert into users (user_name, user_email, join_date) values ('Rabi', 'rslayny5@jugem.jp', '2021-05-19');
insert into users (user_name, user_email, join_date) values ('Elroy', 'etomankiewicz6@zdnet.com', '2021-11-22');
insert into users (user_name, user_email, join_date) values ('Padriac', 'pjurasek7@histats.com', '2021-07-12');
insert into users (user_name, user_email, join_date) values ('Loria', 'lbastock8@cafepress.com', '2021-04-29');
insert into users (user_name, user_email, join_date) values ('Vance', 'vcoghlin9@tinypic.com', '2021-10-08');
Insert some blog_posts
insert into blog_posts (post_title, post_body, user_id) values ('Cross-group system-worthy neural-net', 'vnassau0@dell.com', 2);
insert into blog_posts (post_title, post_body, user_id) values ('De-engineered actuating algorithm', 'ggemson1@surveymonkey.com', 8);
insert into blog_posts (post_title, post_body, user_id) values ('Polarised optimal structure', 'bsture2@ibm.com', 7);
insert into blog_posts (post_title, post_body, user_id) values ('Devolved zero defect orchestration', 'lclute3@hibu.com', 7);
insert into blog_posts (post_title, post_body, user_id) values ('Digitized multimedia infrastructure', 'lpaddington4@nba.com', 10);
insert into blog_posts (post_title, post_body, user_id) values ('Business-focused context-sensitive circuit', 'fstihl5@paypal.com', 6);
insert into blog_posts (post_title, post_body, user_id) values ('Inverse 3rd generation pricing structure', 'dketch6@themeforest.net', 6);
insert into blog_posts (post_title, post_body, user_id) values ('Stand-alone next generation flexibility', 'ldcosta7@paginegialle.it', 4);
insert into blog_posts (post_title, post_body, user_id) values ('Robust maximized productivity', 'gbirrane8@springer.com', 2);
insert into blog_posts (post_title, post_body, user_id) values ('Polarised cohesive functionalities', 'lbewshea9@mediafire.com', 3);Show all blog posts in our blog_posts table
SELECT * FROM blog_posts;
If we delete User with user_id = 2, then the blog post written by user_id = 2 will automatically be deleted.
DELETE FROM users WHERE user_id = 2;
Show all blog posts in our blog_posts table
SELECT * FROM blog_posts;
| post_id | post_title | post_body | post_image | user_id |
|---|---|---|---|---|
| 2 | De-engineered actuating algorithm | ggemson1@surveymonkey.com | placeholder.jpg | 8 |
| 3 | Polarised optimal structure | bsture2@ibm.com | placeholder.jpg | 7 |
| 4 | Devolved zero defect orchestration | lclute3@hibu.com | placeholder.jpg | 7 |
| 5 | Digitized multimedia infrastructure | lpaddington4@nba.com | placeholder.jpg | 10 |
| 6 | Business-focused context-sensitive circuit | fstihl5@paypal.com | placeholder.jpg | 6 |
| 7 | Inverse 3rd generation pricing structure | dketch6@themeforest.net | placeholder.jpg | 6 |
| 8 | Stand-alone next generation flexibility | ldcosta7@paginegialle.it | placeholder.jpg | 4 |
| 10 | Polarised cohesive functionalities | lbewshea9@mediafire.com | placeholder.jpg | 3 |
As you delete the contents of user_id=2 in the parent table it automatically deletes the details of user_id=2 from the child table also.