Creating Tables

13.1. Creating Tables#

13.1.1. Basic#

A database is made up of tables. Tables are made up of columns and rows. A database can be considered like an Excel file and the sheets in your Excel file are like tables. The power of database comes from joining different tables based on shared column information/name between tables and filtering and aggregating rows. SQL is the language that allows you to create, update, delete, filter, and aggregate tables. Often the first step in data wrangling is to extract the data you need from an SQL table. Sometimes you might even have to parse data and populate into a database before you can start using it.

13.1.2. SQLite3#

SQLite3 supports three primitive data types: INTEGER, REAL, TEXT, and BLOB.

Warning

SQLite3 is dynamically typed. This means that even if you specify a column to be data type real or integer, you can insert a string type into it. If the string type can be cast into a numeric type, it will be, otherwise it will be left as is. If you apply a sum or other math function to a column with mixed data types, only the numerical values will be used.

13.1.3. Creating a table#

Tables are created using the CREATE TABLE <name_of_table> directive followed by a pair of parenthesis inside which is a definition of each of the columns, meaning column names, their respective data type, and if they allow NULL values or not, default is to allow NULL values, and if they are a PRIMARY KEY. In addition, create statements can link a table column to another column using the REFERENCES keyword.

13.1.3.1. A simple table#

The following SQL command creates a students table with six columns

CREATE TABLE Students (
    last_name TEXT, 
    first_name TEXT, 
    username TEXT, 
    exam1 REAL, 
    exam2 REAL, 
    exam3 REAL
);

13.1.3.2. Inserting some values into a table#

You can insert values into a table using the INSERT INTO <name_of_table> (<name_of_column_1>, <name_of_column_2>, ....) VALUES (<value_of_column_1>, <value_of_column_1>, ...); The following SQL statements insert some values into a table with some variations.

13.1.3.2.1. Valid Insert statements#

INSERT INTO Students (last_name, first_name, username, exam1, exam2, exam3) VALUES ('Doe', 'John', 'johndoe', 98, 76, 89);
INSERT INTO Students (first_name, last_name, username, exam1, exam2, exam3) VALUES ('Emily', 'Shepard', 'eshepard', 88, 96, 90);
INSERT INTO Students VALUES ('Doe', 'Jane', 'janedoe', 89, 64, 39);
INSERT INTO Students (last_name, first_name, username) VALUES ('Smith', 'John', 'johnsmith');
INSERT INTO Students (exam1, exam2, exam3) VALUES (78, 99, 83);
INSERT INTO Students (exam1, exam2, exam3) VALUES ('25', '33', '45');
INSERT INTO Students  VALUES (56, 32, 33, 'Eaglestone', 'Ken', 'keagle');

In the first insert statement all columns names and their corresponding values are supplied in the order of create table statement.
In the second insert statement, the order of the columns names are changed (first and last name), but SQLite3 will put them in the correct column.
In the third insert statement, if all the number of values supplied correspond to the number of rows, then SQLite3 will insert them into the table in the order of the create statement.
In the fourth insert statement, it is possible to only supply some values since by default all columns allow null values.
In the fifth insert statement, only the names exams values a provided. This is allowed because by default all columns allow null values, but his is poor database design.
In the sixth insert statement, only exams values are provided, but they are type string. SQLite3 will convert them to REAL before inserting them into the table.
In the seventh insert statement, all the values are inserted into the wrong column, which is allowed by SQLite3 since it is dynamically types, so be very careful!

13.1.3.2.2. Invalid Insert Statement#

INSERT INTO Students  VALUES ('Smith', 'John', 'johnsmith');

Execution finished with errors.
Result: table Students has 6 columns but 3 values were supplied
At line 1:
INSERT INTO Students  VALUES ('Smith', 'Jake', 'johnsmith');

If fewer values are provided without explicitly mentioning the column names, SQLite3 will raise an error.

13.1.3.3. Adding `NOT NULL` constraint#

In the previous table we highlighted a poor database design where you could insert exams scores without a student. We fix this by making the student fields NOT NULL, meaning they have to be specified.

Note you can drop a table using DROP TABLE <name_of_table>.

DROP TABLE Students;
CREATE TABLE Students (
    last_name TEXT NOT NULL, 
    first_name TEXT NOT NULL, 
    username TEXT NOT NULL, 
    exam1 REAL, 
    exam2 REAL, 
    exam3 REAL
);

Note that the two separate SQL statements are separated by ;.

If we know attempt to enter a student exam score without the name fields, SQLite3 will raise an error.

INSERT INTO Students('exam1') VALUES ('52');

Execution finished with errors.
Result: NOT NULL constraint failed: Students.last_name
At line 10:
INSERT INTO Students('exam1') VALUES ('52');

13.1.4. Primary Keys#

When you create any table, SQLite3 adds a hidden column called rowid.

DROP TABLE IF EXISTS Teachers;
CREATE TABLE Teachers(
  TeacherName  TEXT NOT NULL
);

INSERT INTO Teachers ('TeacherName') VALUES ('John Smith');
INSERT INTO Teachers ('TeacherName') VALUES ('John Smith');
SELECT rowid, * FROM Teachers;

	TeacherName
0	John Smith
1	John Smith

	rowid	TeacherName
0	1	John Smith
1	2	John Smith

Note: DROP TABLE IF EXISTS Teachers; is used to drop the table if it exists. This is useful if you want to run the create statement over and over again with changes.

When you create a table that has an INTEGER NOT NULL PRIMARY KEY column, this column is the alias of the rowid column.

Note

It uniquely identifies a row in a table. Primary keys have the following properties:

Primary keys can consist of one or more columns, meaning multiple columns combined can uniquely define a row.
Primary keys cannot be duplicated since they uniquely identify a row in a table.
Primary keys cannot be NULL since a blank value cannot identify a row in a table.
There can only be one primary key per table. Note that this is different from a primary key being made up of multiple tables.
Primary keys are indexed automatically.
INTEGER primary keys are auto-increment fields starting at 1.

DROP TABLE Teachers;
CREATE TABLE Teachers (
   TeacherId INTEGER NOT NULL PRIMARY KEY,
   TeacherName  TEXT NOT NULL
);
INSERT INTO Teachers ('TeacherName') VALUES ('John Smith');
INSERT INTO Teachers ('TeacherName') VALUES ('John Smith');
SELECT * FROM Teachers;

	TeacherId	TeacherName
0	1	John Smith
1	2	John Smith

13.1.5. Adding a unique constraint#

In the previous examples teachers table, it is possible to add multiple John Smiths. How can you identify one John Smith from another John Smith. One way would be to assign teachers unique IDs that cannot be reused. A column can be made unique by using the UNIQUE keyword.

DROP TABLE Teachers;
CREATE TABLE Teachers (
   TeacherId INTEGER NOT NULL PRIMARY KEY,
   TeacherName  TEXT NOT NULL,
   TeacherEmployeeID INTEGER NOT NULL,
   UNIQUE (TeacherEmployeeID)
);
INSERT INTO Teachers ('TeacherName', 'TeacherEmployeeID') VALUES ('John Smith', 100001);
INSERT INTO Teachers ('TeacherName', 'TeacherEmployeeID') VALUES ('John Smith', 100002);
SELECT * FROM Teachers;

	TeacherId	TeacherName	TeacherEmployeeID
0	1	John Smith	100001
1	2	John Smith	100002

13.1.6. Foreign Keys#

What is a foreign key? In a relational database, you can relate one table to another table. The two tables can be related if and only if both tables have one column in common. This column has to be declared as a INTEGER/TEXT data type that cannot be NULL and has the PRIMARY KEY constraint, e.g., <name_of_column> INTEGER NOT NULL PRIMARY KEY.It seems INTEGER foreign keys are preferred over TEXT ones. See the following references:

Warning

Foreign key constraint are NOT enabled by default in SQLite. Execute PRAGMA foreign_keys = ON; to enable foreign key constraint.

13.1.6.1. Creating a relationship#

PRAGMA foreign_keys = ON;
DROP TABLE IF EXISTS  Courses;
DROP TABLE IF EXISTS Teachers;
CREATE TABLE Teachers (
   TeacherId INTEGER NOT NULL PRIMARY KEY,
   TeacherName  TEXT NOT NULL,
   TeacherEmployeeID INTEGER NOT NULL,
   UNIQUE (TeacherEmployeeID)
);

CREATE TABLE Courses(
  CourseId     INTEGER NOT NULL PRIMARY KEY, 
  CourseName   TEXT NOT NULL,
  CourseShortID   TEXT NOT NULL,
  TeacherId INTEGER NOT NULL,
  FOREIGN KEY(TeacherId) REFERENCES Teachers(TeacherId),
  UNIQUE (CourseName, CourseShortID)
);

INSERT INTO Teachers (TeacherName, TeacherEmployeeID)
VALUES ('Melissa Larson', 10001);

INSERT INTO Teachers (TeacherName,TeacherEmployeeID)
VALUES ('Christopher Smith', 10002);

INSERT INTO Courses (CourseName, CourseShortID, TeacherId)
VALUES ('Introduction to Python', 'EAS503', 1);

INSERT INTO Courses (CourseName, CourseShortID, TeacherId)
VALUES ('Introduction to Probability', 'EAS506', 2);

13.1.6.2. Using the relationship#

Method 1 – explicitly link two tables

Note this method will result in duplicated TeacherID fields if you select all the columns using ‘*’.

SELECT 
    *
FROM 
    Teachers t
INNER JOIN 
    Courses c ON t.TeacherId = c.TeacherId;

	TeacherId	TeacherName	TeacherEmployeeID	CourseId	CourseName	CourseShortID	TeacherId
0	1	Melissa Larson	10001	1	Introduction to Python	EAS503	1
1	2	Christopher Smith	10002	2	Introduction to Probability	EAS506	2

SELECT 
    t.TeacherName, 
    c.CourseName, 
    c.CourseShortID
FROM 
    Teachers t
INNER JOIN 
    Courses c ON t.TeacherId = c.TeacherId;

	TeacherName	CourseName	CourseShortID
0	Melissa Larson	Introduction to Python	EAS503
1	Christopher Smith	Introduction to Probability	EAS506

Method 2 – let SQLite infers join condition – but you specify the shared column name

Note this method will NOT result in duplicated TeacherID fields if you select all the columns using ‘*’.

SELECT 
    *
FROM 
    Teachers t
INNER JOIN Courses c USING(TeacherId);

	TeacherId	TeacherName	TeacherEmployeeID	CourseId	CourseName	CourseShortID
0	1	Melissa Larson	10001	1	Introduction to Python	EAS503
1	2	Christopher Smith	10002	2	Introduction to Probability	EAS506

SELECT 
    t.TeacherName, 
    c.CourseName, 
    c.CourseShortID
FROM 
    Teachers t
INNER JOIN Courses c USING(TeacherId);

	TeacherName	CourseName	CourseShortID
0	Melissa Larson	Introduction to Python	EAS503
1	Christopher Smith	Introduction to Probability	EAS506

13.1.7. Joining Tables#

There are two types of joins

Inner Join
Outer Join
- Left Outer Join (or Left Join)
- Right Outer Join (or Right Join)
- Full Outer Join (or Full Join) Example database: join_example_database.db
- Ref: https://www.diffen.com/difference/Inner_Join_vs_Outer_Join

	PK	PRODUCT	PRICE
0	1	Potatoes	3
1	2	Avacados	4
2	3	Kiwis	2
3	4	Onions	1
4	5	Melons	5
5	6	Oranges	5
6	7	Tomatoes	6

	PK	PRODUCT	QUANTITY
0	1	Potatoes	45
1	2	Avacados	63
2	3	Kiwis	19
3	4	Onions	20
4	5	Melons	66
5	6	Broccoli	27
6	7	Squash	92

13.1.7.1. Inner Join#

SELECT 
    Prices.Product, 
    Prices.Price, 
    Quantities.Quantity
FROM Prices 
INNER JOIN Quantities ON Prices.Product = Quantities.Product;

	PRODUCT	PRICE	QUANTITY
0	Potatoes	3	45
1	Avacados	4	63
2	Kiwis	2	19
3	Onions	1	20
4	Melons	5	66

13.1.7.2. Left Join#

SELECT 
    Prices.Product, 
    Prices.Price, 
    Quantities.Quantity
FROM 
    Prices
LEFT OUTER JOIN Quantities ON Prices.Product = Quantities.Product;

	PRODUCT	PRICE	QUANTITY
0	Potatoes	3	45.000000
1	Avacados	4	63.000000
2	Kiwis	2	19.000000
3	Onions	1	20.000000
4	Melons	5	66.000000
5	Oranges	5	nan
6	Tomatoes	6	nan

13.1.7.3. Right Join – Doesn’t work in SQLITE3#

SELECT 
    Prices.Product, 
    Prices.Price, 
    Quantities.Quantity
FROM 
    Prices 
RIGHT OUTER JOIN Quantities ON Prices.Product = Quantities.Product;

Execution finished with errors.
Result: RIGHT and FULL OUTER JOINs are not currently supported
At line 1:
SELECT 
    Prices.Product, 
    Prices.Price, 
    Quantities.Quantity
FROM 
    Prices 
RIGHT OUTER JOIN Quantities

13.1.7.4. Right Join by doing Left Join#

Change the order of the tables to emulate right join!

SELECT 
    Quantities.Product, 
    Prices.Price, 
    Quantities.Quantity
FROM 
    Quantities 
LEFT OUTER JOIN Prices ON Quantities.Product = Prices.Product;

	PRODUCT	PRICE	QUANTITY
0	Potatoes	3.000000	45
1	Avacados	4.000000	63
2	Kiwis	2.000000	19
3	Onions	1.000000	20
4	Melons	5.000000	66
5	Broccoli	nan	27
6	Squash	nan	92

13.1.7.5. Full Outer join – does not work in SQLite3#

SELECT Prices.Product, Prices.Price, Quantities.Quantity
FROM Prices FULL OUTER JOIN Quantities
ON Prices.Product = Quantities.Product;

Execution finished with errors.
Result: RIGHT and FULL OUTER JOINs are not currently supported
At line 1:
SELECT Prices.Product, Prices.Price, Quantities.Quantity
FROM Prices FULL OUTER JOIN Quantities

13.1.7.6. Emulate Full outer join#

You can use unions to emulate full outer join Ref: https://www.sqlitetutorial.net/sqlite-full-outer-join/

SELECT Prices.Product, Prices.Price,  Quantities.Product, Quantities.Quantity
FROM Prices 
LEFT OUTER JOIN Quantities
ON Prices.Product = Quantities.Product
UNION ALL
SELECT Prices.Product, Prices.Price, Quantities.Product, Quantities.Quantity
FROM Quantities 
LEFT OUTER JOIN Prices
ON Quantities.Product = Prices.Product;

	PRODUCT	PRICE	PRODUCT	QUANTITY
0	Potatoes	3.000000	Potatoes	45.000000
1	Avacados	4.000000	Avacados	63.000000
2	Kiwis	2.000000	Kiwis	19.000000
3	Onions	1.000000	Onions	20.000000
4	Melons	5.000000	Melons	66.000000
5	Oranges	5.000000	None	nan
6	Tomatoes	6.000000	None	nan
7	Potatoes	3.000000	Potatoes	45.000000
8	Avacados	4.000000	Avacados	63.000000
9	Kiwis	2.000000	Kiwis	19.000000
10	Onions	1.000000	Onions	20.000000
11	Melons	5.000000	Melons	66.000000
12	None	nan	Broccoli	27.000000
13	None	nan	Squash	92.000000

13.1.8. Joining Multiple Tables#

PRAGMA foreign_keys = ON;
DROP TABLE IF EXISTS Cars;
DROP TABLE IF EXISTS MakeModels;
DROP TABLE IF EXISTS Colors;


CREATE TABLE Colors (
   color_id INTEGER NOT NULL PRIMARY KEY,
   color  TEXT NOT NULL,
   UNIQUE (color)
);

CREATE TABLE MakeModels (
  make_model_id     INTEGER NOT NULL PRIMARY KEY, 
  Make   TEXT NOT NULL,
  Model   TEXT NOT NULL,
  Year INTEGER NOT NULL,
  UNIQUE (Make, Model, Year)
);

CREATE TABLE Cars (
   car_id INTEGER NOT NULL PRIMARY KEY,
   make_model_id INTEGER NOT NULL,
   color_id INTEGER NOT NULL,
   available INTEGER NOT NULL,
   FOREIGN KEY(make_model_id) REFERENCES MakeModels(make_model_id),
   FOREIGN KEY(color_id) REFERENCES Colors(color_id)
);

INSERT INTO Colors (color) VALUES ('Red');
INSERT INTO Colors (color) VALUES ('Blue');
INSERT INTO Colors (color) VALUES ('Green');

INSERT INTO MakeModels (Make, Model, Year) VALUES ('Ford', 'Explorer', 2019);
INSERT INTO MakeModels (Make, Model, Year) VALUES ('Toyota', 'Camry', 2010);
INSERT INTO MakeModels (Make, Model, Year) VALUES ('Honda', 'Accord', 2015);

INSERT INTO Cars (make_model_id, color_id, available) VALUES (1, 1, 1);
INSERT INTO Cars (make_model_id, color_id, available) VALUES (2, 1, 1);
INSERT INTO Cars (make_model_id, color_id, available) VALUES (3, 1, 1);
INSERT INTO Cars (make_model_id, color_id, available) VALUES (1, 2, 0);
INSERT INTO Cars (make_model_id, color_id, available) VALUES (1, 2, 0);
INSERT INTO Cars (make_model_id, color_id, available) VALUES (3, 3, 0);

SELECT * FROM Colors;

	color_id	color
0	1	Red
1	2	Blue
2	3	Green

SELECT * FROM MakeModels;

	make_model_id	Make	Model	Year
0	1	Ford	Explorer	2019
1	2	Toyota	Camry	2010
2	3	Honda	Accord	2015

SELECT * FROM Cars;

	car_id	make_model_id	color_id	available
0	1	1	1	1
1	2	2	1	1
2	3	3	1	1
3	4	1	2	0
4	5	1	2	0
5	6	3	3	0

SELECT Cars.car_id, MakeModels.Make, MakeModels.model, MakeModels.year, Colors.color, Cars.available
FROM Cars
    INNER JOIN MakeModels ON MakeModels.make_model_id = Cars.make_model_id
    INNER JOIN Colors ON Colors.color_id = Cars.color_id;

	car_id	Make	Model	Year	color	available
0	1	Ford	Explorer	2019	Red	1
1	2	Toyota	Camry	2010	Red	1
2	3	Honda	Accord	2015	Red	1
3	4	Ford	Explorer	2019	Blue	0
4	5	Ford	Explorer	2019	Blue	0
5	6	Honda	Accord	2015	Green	0

13.1.9. Updating Tables#

The following tables have typos in them!

PRAGMA foreign_keys = ON;
DROP TABLE IF EXISTS Cars;
DROP TABLE IF EXISTS MakeModels;
DROP TABLE IF EXISTS Colors;


CREATE TABLE Colors (
   color_id INTEGER NOT NULL PRIMARY KEY,
   color  TEXT NOT NULL,
   UNIQUE (color)
);

CREATE TABLE MakeModels (
  make_model_id     INTEGER NOT NULL PRIMARY KEY, 
  Make   TEXT NOT NULL,
  Model   TEXT NOT NULL,
  Year INTEGER NOT NULL,
  UNIQUE (Make, Model, Year)
);

CREATE TABLE Cars (
   car_id INTEGER NOT NULL PRIMARY KEY,
   make_model_id INTEGER NOT NULL,
   color_id INTEGER NOT NULL,
   available INTEGER NOT NULL,
   FOREIGN KEY(make_model_id) REFERENCES MakeModels(make_model_id),
   FOREIGN KEY(color_id) REFERENCES Colors(color_id)
);

INSERT INTO Colors (color) VALUES ('Red');
INSERT INTO Colors (color) VALUES ('Blue');
INSERT INTO Colors (color) VALUES ('Greene'); -- TYPO!!!

INSERT INTO MakeModels (Make, Model, Year) VALUES ('Frd', 'Explorer', 2019); -- TYPO!!!
INSERT INTO MakeModels (Make, Model, Year) VALUES ('Toyota', 'Camrey', 2010); -- TYPO!!!
INSERT INTO MakeModels (Make, Model, Year) VALUES ('Honda', 'Accord', 2015);

INSERT INTO Cars (make_model_id, color_id, available) VALUES (1, 1, 1);
INSERT INTO Cars (make_model_id, color_id, available) VALUES (2, 1, 1);
INSERT INTO Cars (make_model_id, color_id, available) VALUES (3, 1, 1);
INSERT INTO Cars (make_model_id, color_id, available) VALUES (1, 2, 0);
INSERT INTO Cars (make_model_id, color_id, available) VALUES (1, 2, 0);
INSERT INTO Cars (make_model_id, color_id, available) VALUES (3, 3, 0);

Show code cell source Hide code cell source

import sqlite3
import pandas as pd

conn = sqlite3.connect('cars.db')

sql_statements = """
PRAGMA foreign_keys = ON;
DROP TABLE IF EXISTS Cars;
DROP TABLE IF EXISTS MakeModels;
DROP TABLE IF EXISTS Colors;

CREATE TABLE Colors (
   color_id INTEGER NOT NULL PRIMARY KEY,
   color  TEXT NOT NULL,
   UNIQUE (color)
);

CREATE TABLE MakeModels (
  make_model_id     INTEGER NOT NULL PRIMARY KEY, 
  Make   TEXT NOT NULL,
  Model   TEXT NOT NULL,
  Year INTEGER NOT NULL,
  UNIQUE (Make, Model, Year)
);

CREATE TABLE Cars (
   car_id INTEGER NOT NULL PRIMARY KEY,
   make_model_id INTEGER NOT NULL,
   color_id INTEGER NOT NULL,
   available INTEGER NOT NULL,
   FOREIGN KEY(make_model_id) REFERENCES MakeModels(make_model_id),
   FOREIGN KEY(color_id) REFERENCES Colors(color_id)
);

INSERT INTO Colors (color) VALUES ('Red');
INSERT INTO Colors (color) VALUES ('Blue');
INSERT INTO Colors (color) VALUES ('Greene'); -- TYPO!!!

INSERT INTO MakeModels (Make, Model, Year) VALUES ('Frd', 'Explorer', 2019); -- TYPO!!!
INSERT INTO MakeModels (Make, Model, Year) VALUES ('Toyota', 'Camrey', 2010); -- TYPO!!!
INSERT INTO MakeModels (Make, Model, Year) VALUES ('Honda', 'Accord', 2015);

INSERT INTO Cars (make_model_id, color_id, available) VALUES (1, 1, 1);
INSERT INTO Cars (make_model_id, color_id, available) VALUES (2, 1, 1);
INSERT INTO Cars (make_model_id, color_id, available) VALUES (3, 1, 1);
INSERT INTO Cars (make_model_id, color_id, available) VALUES (1, 2, 0);
INSERT INTO Cars (make_model_id, color_id, available) VALUES (1, 2, 0);
INSERT INTO Cars (make_model_id, color_id, available) VALUES (3, 3, 0);
"""

with conn:
    cur = conn.cursor()
    cur.executescript(sql_statements)

sql_statement = """
SELECT Cars.car_id, MakeModels.Make, MakeModels.Model, MakeModels.year, Colors.color, Cars.available
FROM Cars
    INNER JOIN MakeModels ON MakeModels.make_model_id = Cars.make_model_id
    INNER JOIN Colors ON Colors.color_id = Cars.color_id;
"""
df = pd.read_sql_query(sql_statement, conn)
df.style.set_table_attributes('style="font-size: 12px"')

	car_id	Make	Model	Year	color	available
0	1	Frd	Explorer	2019	Red	1
1	2	Toyota	Camrey	2010	Red	1
2	3	Honda	Accord	2015	Red	1
3	4	Frd	Explorer	2019	Blue	0
4	5	Frd	Explorer	2019	Blue	0
5	6	Honda	Accord	2015	Greene	0

13.1.9.1. Update Color#

UPDATE Colors
SET color = 'Green'
WHERE color = "Greene";

SELECT Cars.car_id, MakeModels.Make, MakeModels.Model, MakeModels.year, Colors.color, Cars.available
FROM Cars
    INNER JOIN MakeModels ON MakeModels.make_model_id = Cars.make_model_id
    INNER JOIN Colors ON Colors.color_id = Cars.color_id;

	car_id	Make	Model	Year	color	available
0	1	Frd	Explorer	2019	Red	1
1	2	Toyota	Camrey	2010	Red	1
2	3	Honda	Accord	2015	Red	1
3	4	Frd	Explorer	2019	Blue	0
4	5	Frd	Explorer	2019	Blue	0
5	6	Honda	Accord	2015	Green	0

13.1.9.2. Update MakeModel#

UPDATE MakeModels
SET Make = 'Ford'
WHERE Make = "Frd";

UPDATE MakeModels
SET Model = 'Camry'
WHERE Model = "Camrey";

SELECT Cars.car_id, MakeModels.Make, MakeModels.Model, MakeModels.year, Colors.color, Cars.available
FROM Cars
    INNER JOIN MakeModels ON MakeModels.make_model_id = Cars.make_model_id
    INNER JOIN Colors ON Colors.color_id = Cars.color_id;

	car_id	Make	Model	Year	color	available
0	1	Ford	Explorer	2019	Red	1
1	2	Toyota	Camry	2010	Red	1
2	3	Honda	Accord	2015	Red	1
3	4	Ford	Explorer	2019	Blue	0
4	5	Ford	Explorer	2019	Blue	0
5	6	Honda	Accord	2015	Green	0

13.1.9.3. Update Car#

UPDATE Cars
SET available = 1
WHERE car_id = 6;

SELECT Cars.car_id, MakeModels.Make, MakeModels.Model, MakeModels.year, Colors.color, Cars.available
FROM Cars
    INNER JOIN MakeModels ON MakeModels.make_model_id = Cars.make_model_id
    INNER JOIN Colors ON Colors.color_id = Cars.color_id;

	car_id	Make	Model	Year	color	available
0	1	Ford	Explorer	2019	Red	1
1	2	Toyota	Camry	2010	Red	1
2	3	Honda	Accord	2015	Red	1
3	4	Ford	Explorer	2019	Blue	0
4	5	Ford	Explorer	2019	Blue	0
5	6	Honda	Accord	2015	Green	1

13.1.10. Simple Delete#

You can easily delete a non-referenced row.

DELETE FROM Cars
WHERE available = 0;

	car_id	Make	Model	Year	color	available
0	1	Ford	Explorer	2019	Red	1
1	2	Toyota	Camry	2010	Red	1
2	3	Honda	Accord	2015	Red	1
3	6	Honda	Accord	2015	Green	1

13.1.11. Deleting a referenced row#

A referenced row can be deleted or not depending on how the table was defined. By default, a referenced row cannot be deleted. This default behavior can be changed. You can either set the foreign key to ON DELETE SET NULL or ON DELETE SET CASCADE. In the former case, the references to the row being deleted is set to NULL. In the latter case, the anything that references the row being deleted is also deleted.

DELETE FROM Colors
WHERE color = "Red";

Execution finished with errors.
Result: FOREIGN KEY constraint failed
At line 1:
DELETE FROM Colors
WHERE color = "Red";

DELETE FROM Cars
WHERE car_id IN (SELECT car_id from Cars INNER JOIN Colors clr USING(color_id ) WHERE clr.color='Red');
DELETE FROM Colors
WHERE color = "Red";

SELECT Cars.car_id, MakeModels.Make, MakeModels.Model, MakeModels.year, Colors.color, Cars.available
FROM Cars
    INNER JOIN MakeModels ON MakeModels.make_model_id = Cars.make_model_id
    INNER JOIN Colors ON Colors.color_id = Cars.color_id;

	car_id	Make	Model	Year	color	available
0	6	Honda	Accord	2015	Green	1

13.1.11.1. `ON DELETE`#

PRAGMA foreign_keys = ON;
DROP TABLE IF EXISTS Cars;
DROP TABLE IF EXISTS MakeModels;
DROP TABLE IF EXISTS Colors;


CREATE TABLE Colors (
   color_id INTEGER NOT NULL PRIMARY KEY,
   color  TEXT NOT NULL,
   UNIQUE (color)
   
);

CREATE TABLE MakeModels (
  make_model_id     INTEGER NOT NULL PRIMARY KEY, 
  Make   TEXT NOT NULL,
  Model   TEXT NOT NULL,
  Year INTEGER NOT NULL,
  UNIQUE (Make, Model, Year)
);

CREATE TABLE Cars (
   car_id INTEGER NOT NULL PRIMARY KEY,
   make_model_id INTEGER NOT NULL,
   color_id INTEGER, --> Have to allow for NULL values for ON DELETE SET NULL to work!!!!
   available INTEGER NOT NULL,
   FOREIGN KEY(make_model_id) REFERENCES MakeModels(make_model_id) ON DELETE CASCADE,
   FOREIGN KEY(color_id) REFERENCES Colors(color_id) ON DELETE SET NULL
);

INSERT INTO Colors (color) VALUES ('Red');
INSERT INTO Colors (color) VALUES ('Blue');
INSERT INTO Colors (color) VALUES ('Green');

INSERT INTO MakeModels (Make, Model, Year) VALUES ('Ford', 'Explorer', 2019);
INSERT INTO MakeModels (Make, Model, Year) VALUES ('Toyota', 'Camry', 2010);
INSERT INTO MakeModels (Make, Model, Year) VALUES ('Honda', 'Accord', 2015);

INSERT INTO Cars (make_model_id, color_id, available) VALUES (1, 1, 1);
INSERT INTO Cars (make_model_id, color_id, available) VALUES (2, 1, 1);
INSERT INTO Cars (make_model_id, color_id, available) VALUES (3, 1, 1);
INSERT INTO Cars (make_model_id, color_id, available) VALUES (1, 2, 0);
INSERT INTO Cars (make_model_id, color_id, available) VALUES (1, 2, 0);
INSERT INTO Cars (make_model_id, color_id, available) VALUES (3, 3, 0);

DELETE FROM Colors
WHERE color = "Red";

DELETE FROM MakeModels
WHERE make_model_id = 1;

SELECT Cars.car_id, MakeModels.Make, MakeModels.Model, MakeModels.year, Colors.color, Cars.available
FROM Cars
    INNER JOIN MakeModels ON MakeModels.make_model_id = Cars.make_model_id
    INNER JOIN Colors ON Colors.color_id = Cars.color_id;

	car_id	Make	Model	Year	color	available
0	6	Honda	Accord	2015	Green	0

SELECT Cars.car_id, MakeModels.Make, MakeModels.Model, MakeModels.year, Colors.color, Cars.available
FROM Cars
    LEFT JOIN MakeModels ON MakeModels.make_model_id = Cars.make_model_id
    LEFT JOIN Colors ON Colors.color_id = Cars.color_id;

	car_id	Make	Model	Year	color	available
0	1	None	None	nan	None	1
1	2	Toyota	Camry	2010.000000	None	1
2	3	Honda	Accord	2015.000000	None	1
3	4	None	None	nan	Blue	0
4	5	None	None	nan	Blue	0
5	6	Honda	Accord	2015.000000	Green	0