Constraints

A constraint is a rule you attach to a column when you design a table, and MySQL enforces that rule automatically on every single insert or update from then on. This is different from checking things in your application code, because a constraint acts at the very last moment before a row is written to disk - if the data breaks the rule, MySQL refuses the statement outright and nothing bad ever gets stored. In this lesson we'll walk through the four constraints you'll reach for constantly: NOT NULL, UNIQUE, DEFAULT, and CHECK, all using StreamHub's own tables.

NOT NULL - Requiring a Value

Some columns simply must never be empty. StreamHub's users table is a good example - a user account with no username makes no sense, so we mark that column NOT NULL when we create the table:

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(255),
  signup_date DATE
);

Now try to sign up a user without a username:

INSERT INTO users (email) VALUES ('nobody@streamhub.example');

MySQL rejects it before it ever touches the table:

That's the whole point of NOT NULL - a blank username never gets a chance to be silently stored as an empty string or a null placeholder. The insert simply fails, loudly, at the moment it happens.

UNIQUE - No Duplicates Allowed

Two StreamHub accounts should never share the same email address, because email is how a user logs back in and resets a forgotten password. The UNIQUE constraint enforces exactly that:

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(255) UNIQUE,
  signup_date DATE
);

-- First signup succeeds
INSERT INTO users (username, email) VALUES ('priya_watches', 'priya@streamhub.example');

-- A second signup with the same email is rejected
INSERT INTO users (username, email) VALUES ('priya_again', 'priya@streamhub.example');

Notice this is a different failure mode than NOT NULL - the email isn't missing, it's just already taken. MySQL checks a hidden unique index behind the scenes to catch this instantly, even across millions of existing rows.

DEFAULT - Filling In a Value Automatically

DEFAULT gives a column a value to fall back on when an insert doesn't specify one. Two very natural StreamHub examples: every signup should be timestamped with today's date, and every fresh review should start life with a sane baseline rating instead of nothing at all.

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(255) UNIQUE,
  signup_date DATE DEFAULT (CURRENT_DATE)
);

CREATE TABLE reviews (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  movie_id INT,
  rating INT DEFAULT 0,
  comment TEXT,
  review_date DATE DEFAULT (CURRENT_DATE)
);

If an INSERT into users leaves out signup_date, MySQL fills in today's date on its own - nobody has to remember to pass it from the application. We revisit exactly this behavior with a full worked example in The INSERT Statement.

CHECK - Restricting the Range of Values

A CHECK constraint goes a step further than NOT NULL or UNIQUE - it lets you write an actual condition that every value in the column must satisfy. StreamHub's reviews.rating column should only ever hold a whole number from 1 to 5, so we say exactly that:

CREATE TABLE reviews (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  movie_id INT,
  rating INT,
  comment TEXT,
  review_date DATE,
  CHECK (rating BETWEEN 1 AND 5)
);

-- Rejected before it ever reaches the table
INSERT INTO reviews (user_id, movie_id, rating) VALUES (1, 1, 9);

Combining Constraints on One Column

Constraints aren't mutually exclusive - a single column can carry several of them at once. It's very common to require a value and restrict its range in the same breath:

rating INT NOT NULL CHECK (rating BETWEEN 1 AND 5)

Read this the same way you'd read it in English: rating is required, and whatever value it holds must fall between 1 and 5. Neither half of that sentence can be skipped - leaving the column out of an insert fails the NOT NULL half, and supplying a 9 fails the CHECK half.

Adding a Constraint to an Existing Table

Every example above assumed we were creating the table fresh. In practice, a constraint often needs to be added to a table that's already live with data - for example, realizing after the fact that reviews.rating needs that CHECK constraint retrofitted. That's done with ALTER TABLE ... ADD CONSTRAINT, which we cover in full in the next lesson, Altering & Dropping Tables. As a quick preview, it looks like this:

ALTER TABLE reviews
ADD CONSTRAINT chk_rating CHECK (rating BETWEEN 1 AND 5);

Constraints Are Your Last Line of Defense

It's tempting to think "my application already validates this, so why bother with database constraints too?" The honest answer is that application code is never the only path data can take into your database - a bug, a forgotten check in one form, a script run directly against the database, or a second application sharing the same tables can all bypass validation that lives only in one place. Constraints mean the database itself refuses to trust that the application always gets it right, and enforces the rule no matter where the insert came from. Application-side validation still matters for giving users a friendly error message before a request ever reaches the database - we'll pick that thread back up in Connecting from Application Code - but the constraint is what guarantees the rule holds even when everything upstream of it fails.