SQL for Data Analytics · Constraints

Constraints

Constraints21 min

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.

⌘/Ctrl + Enter

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.
Constraints in a CREATE TABLE5 min

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.
Predict, then run5 min

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.

    Running an INSERT twice3 min

    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.

    Foreign keys and order5 min

    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.

    ⌘/Ctrl + Enter

    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.