Relational concepts

What you will be able to do

Find out which columns connect the three tables, and if you can trust them. A key that is not unique, or that points to nothing, gives wrong counts later without an error.

NotebookDo this lesson in a notebookColab · Kaggle · GitHub

The notebook has every query, task and check of this lesson, with the instructions, and the small tables and the constraint examples. Its first cell downloads the practice data and builds the database, so it needs nothing on your computer. Read the explanations here, and run the code there.

Open in ColabOpen in KaggleView on GitHub

Colab and Kaggle open your own copy. They need a free Google or Kaggle account. Stuck? See the solution notebook.

Before you startWhat to know and to have readyAbout 50 minutes

What you need to know

• Rows, columns, values and NULL, from Dataset anatomy: Rows, columns and values.
• The grain of each table: one customer, one ticket, one event, from What does one row mean?.
• SELECT … FROM … LIMIT and WHERE, from Run your first query and Your turn: one customer’s tickets. COUNT(*), from How big is each table?.

What you need on your computer

• Nothing. All SQL in this lesson runs in your browser, and the notebook runs in Colab or Kaggle. You build the database on your computer in the next lesson.

The browser boxes use SQLite 3.49.1. The notebook was tested in Jupyter.

Three small tables

The first boxes in this lesson do not use the practice database. They use three small tables made for this lesson, so that you can see every row. There are 3 customers, 5 tickets and 5 outcome events.

customers

customer_id	segment
C-0001	home
C-0002	trade
C-0003	business

tickets

ticket_id	customer_id	team
T-20001	C-0001	delivery
T-20002	C-0002	returns
T-20003	C-0002	payment
T-20004	C-0002	delivery
T-20005	C-0001	account

outcomes

outcome_id	ticket_id	status
O-90001	T-20001	resolved
O-90002	T-20002	escalated
O-90003	T-20002	resolved
O-90004	T-20003	resolved
O-90005	T-20005	resolved

Run SQL in your browser

Try it: the small tables

Run the query. You should see the 5 tickets. Then change tickets to outcomes, and to customers, and run each one.

SQL query (you can change it)SELECT * FROM tickets;

Keys

Primary key. A primary key is a column whose value is different in every row and never NULL. It identifies one row. In customers, customer_id is the primary key: C-0002 names exactly one customer. In tickets, it is ticket_id, and in outcomes, outcome_id.

Foreign key. A foreign key is a column whose values point to the primary key of another table. In tickets, the column customer_id is a foreign key: the value C-0002 says “look up C-0002 in customers”. In outcomes, ticket_id is a foreign key to tickets.

The two kinds of key have different rules:

	Primary key	Foreign key
Example	customers.customer_id	tickets.customer_id
Can a value appear twice?	No. Each value names one row.	Yes. C-0002 appears in three tickets.
Can it be NULL?	No	Yes, if “no match” is allowed. A guest ticket has no customer.
What must be true?	Unique and not NULL	Each value that is not NULL exists in the other table

The notation customers.customer_id means “the column customer_id of the table customers”. It helps when two tables have a column with the same name.

One-to-many

Look at C-0002 in the small tables. One customer has three tickets: T-20002, T-20003 and T-20004. Each of those tickets has exactly one customer. This is a one-to-many relationship: one row on one side, zero or more rows on the other side.

Customer	Its tickets	Count
C-0001	T-20001, T-20005	2
C-0002	T-20002, T-20003, T-20004	3
C-0003	none	0

The same holds one level down. A ticket has zero or more outcome events. T-20002 has two (escalated, then resolved). T-20004 has none yet, because it is still open.

Run SQL in your browser

Try it: the many side

Predict the number of rows, then run the query. You should see 3 rows. Then change the query so that it shows the events of ticket T-20002 from outcomes. You should see 2 rows.

SQL query (you can change it)SELECT * FROM tickets WHERE customer_id = 'C-0002';

The foreign key is always on the “many” side. tickets holds customer_id, not the other way round. A customer row cannot hold a list of tickets, but each ticket can name its one customer.

The entity relationship diagram

An entity relationship diagram (ERD) draws the tables, their keys and the relationships between them. Here is the ERD of the practice database.

The ERD of the practice database: customers 1 to many tickets, tickets 1 to many outcome events. Each box shows the grain of its table and the key columns.

Text description of the diagram

The diagram has three boxes. customers (one row is one customer) has the columns customer_id, which is the primary key (PK), segment, region and joined_on. tickets (one row is one ticket) has ticket_id (PK), created_at and closed_at. A note says that 14 IDs appear twice. Its customer_id is a foreign key (FK), with the note “NULL for guests”. outcomes (one row is one event) has outcome_id (PK), ticket_id (FK), recorded_at and status. The diagram shows only some columns of tickets and outcomes. A line with the label “has 0 or more” connects customers to tickets. It means that a ticket has zero or one customer, and a customer has zero or more tickets. A second line with the same label connects tickets to outcomes. It means that an event has exactly one ticket, and a ticket has zero or more events.

Read the ends of each line. The fork (three short lines) means “many”. The circle means “zero is allowed”. A short bar means “one”. So the line between tickets and outcomes says: each event belongs to exactly one ticket, and a ticket has zero or more events. Between customers and tickets, the customer end has a circle too, because a guest ticket has no customer.

What a constraint does

A database can enforce key rules for you. A constraint is a rule in the schema that the database checks on every change. PRIMARY KEY rejects a second row with the same value. FOREIGN KEY rejects a value that does not exist in the other table.

Run the next box. It creates two tables with constraints, then inserts the same ticket twice.

Run SQL in your browser

Try it: constraints reject bad rows

Run the query. You should see Error: UNIQUE constraint failed: tickets.ticket_id. The database refused the second T-20001. Then do these changes, one at a time, and run after each:

1. Delete the second INSERT INTO tickets line. You see 1 row.
2. Add INSERT INTO tickets VALUES ('T-20002', 'C-0009', 'returns'); before the SELECT. You see Error: FOREIGN KEY constraint failed, because C-0009 is not in customers.
3. Change 'C-0009' to NULL. The row is accepted. A foreign key allows NULL: a guest ticket.
4. Change NULL back to 'C-0009', and delete the first line, PRAGMA foreign_keys = ON;. The bad row is accepted.

SQL query (you can change it)PRAGMA foreign_keys = ON; CREATE TABLE customers ( customer_id TEXT NOT NULL PRIMARY KEY, segment TEXT ); CREATE TABLE tickets ( ticket_id TEXT NOT NULL PRIMARY KEY, customer_id TEXT, team TEXT, FOREIGN KEY (customer_id) REFERENCES customers (customer_id) ); INSERT INTO customers VALUES ('C-0001', 'home'), ('C-0002', 'trade'); INSERT INTO tickets VALUES ('T-20001', 'C-0001', 'delivery'); INSERT INTO tickets VALUES ('T-20001', 'C-0001', 'delivery'); SELECT * FROM tickets;

Step 4 shows a SQLite rule. SQLite checks foreign keys only after PRAGMA foreign_keys = ON;, and you must run it again on every new connection. Without it, FOREIGN KEY is only a note in the schema. PostgreSQL always checks them.

Write NOT NULL next to PRIMARY KEY in SQLite

Because of an old design decision, SQLite accepts NULL in a PRIMARY KEY column of type TEXT. Most other databases do not. The box above writes NOT NULL PRIMARY KEY to close this gap.

Why the export has no constraints

Now look at the real database. Its tables have types, but no PRIMARY KEY and no FOREIGN KEY constraints. This is on purpose. The export is a copy of another system’s data, and the export does not guarantee keys. If Mei added constraints, the first bad row would stop the load, and she would have no data to inspect. So she loads every row as it is, then checks the keys herself with queries. The rule for this course: the export does not guarantee keys, so you check them yourself.

There are two checks for each key:

1. Is the primary key unique? Compare the number of rows with the number of different values.
2. Does each foreign key point to a row? Look for values that are not in the other table. A row whose foreign key points to nothing is an orphan.

Worked example: check ticket_id

Mei starts with tickets. If ticket_id is a real primary key, the number of rows equals the number of different IDs. COUNT(DISTINCT ticket_id) counts the different values.

Run SQL in your browser

Try it: is ticket_id unique?

Predict: are the two numbers equal? Then run the query. You should see 1417 and 1403.

SQL query (you can change it)SELECT COUNT(*) AS ticket_rows, COUNT(DISTINCT ticket_id) AS different_ids FROM tickets;

The two numbers differ by 14. Some IDs appear more than once. Mei lists them. You learn GROUP BY and HAVING in Module 2. For now, read the query as “for each ticket ID, count its rows, and keep the IDs with more than one row”.

Run SQL in your browser

Try it: which IDs appear twice?

Run the query. You should see 14 rows, from T-10031 to T-11291, each with row_count 2. Then change the query to SELECT * FROM tickets WHERE ticket_id = 'T-10031'; and compare the two rows.

SQL query (you can change it)SELECT ticket_id, COUNT(*) AS row_count FROM tickets GROUP BY ticket_id HAVING COUNT(*) > 1;

The two rows of T-10031 are the same in all 11 columns. They are exact duplicate rows. Mei asks Grace. The export tool writes the data in pages, and two pages overlapped. So the rows are one ticket written twice, not two different tickets with the same ID. SELECT DISTINCT * keeps one copy of each different row. SELECT COUNT(*) FROM (SELECT DISTINCT * FROM tickets) returns 1,403, the same as the number of different IDs. This confirms that all 14 are exact copies.

Mei writes in her notes:

Check	Result	Meaning
Rows in tickets	1,417	Ticket rows, not tickets
Different ticket_id values	1,403	Tickets
IDs with more than one row	14, each twice	Exact duplicates from an export page overlap
Different rows (SELECT DISTINCT *)	1,403	Every duplicate is an exact copy

She does not delete the duplicates now. Module 4 removes them in a cleaning step that records what it removed. For now, she knows: 1,417 rows, 1,403 tickets.

Worked example: orphan tickets

Next, the foreign key tickets.customer_id. Mei looks for ticket rows whose customer is not in customers. NOT IN (SELECT customer_id FROM customers) means “not in the list of customer IDs”.

Run SQL in your browser

Try it: tickets whose customer is missing

Run the query. You should see 9 rows. The customer IDs are C-0241 to C-0244. Then replace the WHERE line with WHERE customer_id IS NULL, and run it again. You should see 12 rows.

SQL query (you can change it)SELECT ticket_id, customer_id FROM tickets WHERE customer_id NOT IN (SELECT customer_id FROM customers);

There are two different groups, and the first query finds only one of them:

Group	Rows	Value of customer_id	Meaning (from Grace)
Deleted accounts	9	C-0241, C-0242, C-0243, C-0244	The customer closed the account. The customer row is gone, the tickets stay.
Guests	12	NULL	The customer bought without an account.

Why does NOT IN not find the 12 guests? A comparison with NULL is never true: SQL cannot say if “no value” is in the list. So NULL NOT IN (...) does not keep the row. You need IS NULL for them. Module 2 explains this rule. Here, remember: a check for orphans must also count the NULLs.

The 9 orphan rows are real tickets. Later, a query that connects tickets to customers can lose them without an error. You see this in Module 2.

Guided practice: check customer_id in customers

1. In the box below, run the query. It compares the number of rows with the number of different customer IDs.
2. Check the result: both numbers are 240.
3. Add a third column to the SELECT: COUNT(customer_id) AS not_null. COUNT(column) counts only the values that are not NULL.
4. Run it again. You should see 240, 240 and 240. So customer_id is unique and never NULL. It works as a primary key.

Run SQL in your browser

Try it: is customer_id a primary key?

Follow the steps above.

SQL query (you can change it)SELECT COUNT(*) AS customer_rows, COUNT(DISTINCT customer_id) AS different_ids FROM customers;

Your turn: orphan outcome events

Each outcome event must belong to a ticket. Write a query that shows every column of the events whose ticket_id is not in tickets. Predict first: will the result be empty?

Run SQL in your browser

Your turn: events without a ticket

Use the orphan-tickets query above as a pattern. The check compares your result with ours.

SQL query (you can change it)-- Write your query here

Show the example answer

SELECT *
FROM outcomes
WHERE ticket_id NOT IN (SELECT ticket_id FROM tickets);

The result has 4 rows, for the tickets T-9990, T-9991, T-9992 and T-9993. None of these IDs is in tickets. There are no NULLs in outcomes.ticket_id (you can check with IS NULL: 0 rows), so these 4 are all the orphans. They are events of tickets that the export did not include.

Independent variation: is outcome_id unique?

Check the primary key of outcomes in the same way as ticket_id. Write one query that returns two numbers: the number of rows and the number of different outcome_id values.

Run SQL in your browser

Your turn: check outcome_id

The result has one row and two columns. The names of the columns do not matter for the check.

SQL query (you can change it)-- Write your query here

Show the example answer

SELECT COUNT(*) AS event_rows,
       COUNT(DISTINCT outcome_id) AS different_ids
FROM outcomes;

Both numbers are 1,874, so outcome_id is unique. A unique key does not mean that the data has no repeats. Module 3 shows that some events were logged twice with a new outcome_id. Uniqueness is one check, not a proof that the data is clean.

Common mistake

Trusting a key without checking it

Symptom: Tomás counts tickets per team with COUNT(*) on tickets. The total is 1,417. Grace’s help-desk screen says 1,403 tickets for the same period. Nobody saw an error.

Cause: Tomás assumed that ticket_id is a primary key, because its name ends in _id. The export has no constraints, and 14 tickets appear twice. Each of them counted two times.

Fix:

1. Before you use a column as a key, compare COUNT(*) with COUNT(DISTINCT key).
2. If they differ, list the repeated values, and look at the rows.
3. Decide with the people who know the data. Here, the rows are exact copies, so you count each ticket once. Write the decision down.

In AI work: duplicated rows can land in the training set and in the test set at the same time. The model is then tested on rows that it has already seen, and the score looks better than it is. Module 5 shows this kind of problem.

Check your understanding

In the small tables, why is customer_id a primary key in customers but not in tickets?

Show the answer

In customers, each customer_id names one row: it is unique and never NULL. In tickets, customer_id is a foreign key. It points to a customer, and the same customer can have many tickets, so the value can repeat (C-0002 appears three times). It can also be NULL for a guest.

Check your understanding

You run WHERE customer_id NOT IN (SELECT customer_id FROM customers) and get 9 rows. Is it correct to say that 9 tickets have no known customer?

Show the answer

No. The query finds only the 9 rows that point to a deleted customer. A comparison with NULL is never true, so the 12 guest rows with NULL are not in the result. You find them with IS NULL. So 21 ticket rows have no matching customer row: 9 deleted accounts and 12 guests.

Check your understanding

SQLite accepted a ticket for customer C-0009, although the table has a FOREIGN KEY constraint and C-0009 does not exist. What is the most likely reason?

Show the answer

Foreign key checks were not on. SQLite checks foreign keys only after PRAGMA foreign_keys = ON;, and the setting applies to one connection only. Without it, the constraint is in the schema, but SQLite does not check it.

Recap

• A primary key identifies one row: unique and never NULL. A foreign key points to the primary key of another table. It can repeat, and it can be NULL.
• One-to-many: one customer has zero or more tickets, and one ticket has zero or more events. The foreign key is on the “many” side.
• A constraint makes the database reject bad rows. In SQLite, foreign keys are checked only with PRAGMA foreign_keys = ON;.
• The export has no constraints, so you check keys yourself: COUNT(*) against COUNT(DISTINCT key), NOT IN for orphans, and IS NULL for missing values.
• The real data: 1,417 ticket rows but 1,403 tickets (14 exact duplicates). 9 ticket rows point to deleted customers, and 12 have no customer. 4 events point to tickets that are not in the export.

Next, you build the same database on your computer and run saved queries from files.

Next: Set up a practice database