[Recipe of Code] MySQL Table Creation with Foreign Keys

mysql_foreign_keys_example.sql

-- Worked on MySQL 5.7
CREATE DATABASE IF NOT EXISTS rest;

CREATE TABLE IF NOT EXISTS rest.Users(
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(128) NOT NULL,
    email VARCHAR(128) NOT NULL,
    joined_datetime DATETIME DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS rest.Makers(
	id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(128) NOT NULL,
	founded_year INT
);
CREATE TABLE IF NOT EXISTS rest.Cars(
	id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(128) NOT NULL,
	maker INT UNSIGNED,  -- Foreign key should be `UNSIGNED` too.
	released_year INT,
	FOREIGN KEY (maker)
	REFERENCES Makers(id)
	ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS rest.UserHasCars(
	id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
	uid INT UNSIGNED,
	cid INT UNSIGNED,
	FOREIGN KEY (cid)
	REFERENCES Cars(id)
	ON UPDATE CASCADE,  -- For multiple foreign keys, commas are needed.
	FOREIGN KEY (uid)
	REFERENCES Users(id)
	ON UPDATE CASCADE,  -- Another option is `ON DELETE CASCADE`.
	UNIQUE (uid, cid)
);

INSERT INTO rest.Users(username, email)
VALUES("Park", "[email protected]");
INSERT INTO rest.Users(username, email)
VALUES("Kim", "[email protected]");
INSERT INTO rest.Users(username, email)
VALUES("Bae", "[email protected]");
INSERT INTO rest.Users(username, email)
VALUES("Choi", "[email protected]");

INSERT INTO rest.Makers(name, founded_year)
VALUES("Ferrari", 1947);
INSERT INTO rest.Makers(name, founded_year)
VALUES("Ford", 1903);
INSERT INTO rest.Makers(name, founded_year)
VALUES("Hyundai", 1967);
INSERT INTO rest.Makers(name, founded_year)
VALUES("Jaguar", 1922);

-- `INSERT` without table schema.
INSERT INTO rest.Cars VALUES(NULL, "488", 1, 2020);
INSERT INTO rest.Cars VALUES(NULL, "Mustang", 2, 2020);
INSERT INTO rest.Cars VALUES(NULL, "GV80", 3, 2020);
INSERT INTO rest.Cars VALUES(NULL, "F-Type", 4, 2020);

INSERT INTO rest.UserHasCars VALUES(NULL, 1, 1);
INSERT INTO rest.UserHasCars VALUES(NULL, 1, 2);
INSERT INTO rest.UserHasCars VALUES(NULL, 1, 3);
INSERT INTO rest.UserHasCars VALUES(NULL, 1, 4);
INSERT INTO rest.UserHasCars VALUES(NULL, 2, 2);
INSERT INTO rest.UserHasCars VALUES(NULL, 3, 3);
INSERT INTO rest.UserHasCars VALUES(NULL, 4, 4);