SQL for Data Analytics · Constraints
Constraints
Constraints21 min
The video starts with the students table from the INSERT, UPDATE, DELETE video, in the Changing the data lesson. The box below starts with that table's script, in case your college database doesn't have it. Running the script drops students, if it's there, and creates it again, empty.
Run queries here while you watch. When I tell you in the video to open Workbench and try something, use this box. It runs on your own MySQL server, the same one as every box in this unit's lessons, and whatever you run stays. This is for your own practice. It isn't recorded or part of your unit submission.
Near the end of the video I asked you to stop and think back over it. What is a constraint for? Which constraints did the video cover, and what does each one stop?
How I'd answer it
A constraint is a rule the database enforces on the data, so a mistake gets refused instead of stored. The video covered four:
- NOT NULL stops a row with no value in that column.
- UNIQUE stops two rows from having the same value in that column.
- PRIMARY KEY marks the column that identifies each row. It can't be NULL, and no two rows can share a value.
- FOREIGN KEY stops you from deleting a row that other rows still point at, like Ken Hang in the video. It also refuses a value that doesn't match any row in the other table.
Here's the students table with the constraints from the video, and the advisors table its foreign key points at.
CREATE TABLE advisors (
ID INT,
Name VARCHAR(255) NOT NULL,
PRIMARY KEY (ID)
);
CREATE TABLE students (
ID INT,
FirstName VARCHAR(255) NOT NULL,
LastName VARCHAR(255) NOT NULL,
Age INT,
Email VARCHAR(255) NOT NULL UNIQUE,
AdvisorID INT,
PRIMARY KEY (ID),
FOREIGN KEY (AdvisorID) REFERENCES advisors(ID)
);
NOT NULL and UNIQUE go right after a column's type. PRIMARY KEY and FOREIGN KEY can go at the bottom, each naming its column in parentheses. A FOREIGN KEY also says which table and column it points at, after REFERENCES.
The data type is a rule too. An INT column refuses text, so 'Susie' in the ID column gets Incorrect integer value.
Each constraint has its own error when a statement breaks it:
| Constraint | The error |
|---|---|
| NOT NULL | Field 'LastName' doesn't have a default value when the INSERT leaves the column out. Column 'LastName' cannot be null when it puts NULL there. |
| UNIQUE | Duplicate entry 'test@mail.com' for key 'Email' |
| PRIMARY KEY | Duplicate entry '5' for key 'PRIMARY' |
| FOREIGN KEY | Cannot add or update a child row: a foreign key constraint fails for a value with no match. Cannot delete or update a parent row: a foreign key constraint fails for deleting a row that's in use. |
In the Constraints video I asked you to predict which of two INSERTs would run before I ran them. Here are five more, against an advisors table and a students table with the constraints from that video.
Predict, then run
This script made the two tables, with their constraints. Under it is what's in them.
advisors
students
The statements below run one after another, against those tables. For each one, decide whether it runs or fails. Then press Run them.
It's easy to run an INSERT twice by accident. Running a whole script again runs its INSERTs again too. A primary key changes what happens then.
The same INSERTs again
Both clubs tables already have the two rows this script adds. Only one has a primary key. Before you press the button, decide what running the script again does to each table.
clubs, no primary key
clubs, primary key on ID
In a table with no key, a run you didn't mean leaves copies behind, and you have to find them and delete them. With a key, the database refuses the copies.
A foreign key makes the order of statements matter. The table the key points at is the parent, and the table that has the key is the child. In the Constraints video, advisors is the parent and students is the child.
- Create the parent table first. MySQL won't create a foreign key that points at a table that isn't there yet.
- Insert the parent's rows first. A child row can only point at a row that's already in the parent.
- Change or delete the child rows that point at a parent row before you delete that parent row.
- Drop the child table before the parent table.
When the parent table isn't there yet, the error in these boxes doesn't name it. It reads Can't create table followed by Foreign key constraint is incorrectly formed.
The script in the box runs its statements in an order that doesn't work. Put them in an order that does, and run it. The script makes a database of its own, advising, so it doesn't change college.
A table that an earlier run already made can't be created again. Select the statements that haven't run yet and press Run selection.