Constraints & Picking Data Types

Why constraints matter

A constraint is a rule attached to a column that the database enforces for you. Instead of hoping your application always sends good data, you tell MySQL “this can never be empty” or “this must be unique”, and MySQL refuses any row that breaks the rule. This is the single best defence against messy, inconsistent data — the rule lives with the data, so every program that touches the table obeys it.

Here are the constraints you will use constantly:

A fully-constrained table

Let us build an accounts table where the rules are baked into the schema. Read the columns, then we walk through each constraint:

CREATE TABLE accounts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  email VARCHAR(150) NOT NULL UNIQUE,
  age INT CHECK (age >= 18),
  role VARCHAR(20) DEFAULT 'member',
  balance DECIMAL(10,2) DEFAULT 0.00
);

Line by line: id INT AUTO_INCREMENT PRIMARY KEY is the auto-numbered unique identity. username VARCHAR(50) NOT NULL UNIQUE must be present and different from every other username. email has the same two rules. age INT CHECK (age >= 18) rejects any row with an age under 18. role VARCHAR(20) DEFAULT 'member' fills in “member” when you do not supply a role. balance DECIMAL(10,2) DEFAULT 0.00 starts every account at zero with two decimal places.

Watching constraints reject bad data

Now the rules do their job. Each of these inserts breaks a rule, and MySQL stops it:

-- 1) duplicate username (UNIQUE)
INSERT INTO accounts (username, email) VALUES ('asha', 'a@x.com');
INSERT INTO accounts (username, email) VALUES ('asha', 'b@x.com');

-- 2) under-age (CHECK)
INSERT INTO accounts (username, email, age) VALUES ('ravi', 'r@x.com', 15);

-- 3) missing required email (NOT NULL)
INSERT INTO accounts (username) VALUES ('meera');

The first insert succeeds. The second fails because asha already exists and username is UNIQUE. The third fails because 15 breaks CHECK (age >= 18). The fourth fails because email is NOT NULL but none was given. In every case the bad row is rejected — your table stays clean.

Choosing the right data type

Picking the column type is the other half of a good schema. The wrong type wastes space or, worse, corrupts your data (storing money as FLOAT causes rounding errors). A quick guide:

A worked example of ENUM, which restricts a column to a small set of allowed values — perfect for a status field:

CREATE TABLE tickets (
  id INT AUTO_INCREMENT PRIMARY KEY,
  subject VARCHAR(120) NOT NULL,
  status ENUM('open', 'pending', 'closed') NOT NULL DEFAULT 'open'
);

status ENUM('open', 'pending', 'closed') means only those three words are ever allowed in the column — trying to insert 'banana' would be rejected. DEFAULT 'open' means new tickets start as “open”. This is cleaner and safer than a plain VARCHAR where any typo would slip through.

ALTER TABLE — changing a table that already exists

You will rarely get a schema perfect on the first try. ALTER TABLE lets you change a table after it has data — add a column, change a type, add a constraint, or drop something — without rebuilding it. The common forms:

ALTER TABLE accounts ADD phone VARCHAR(20);            -- add a column
ALTER TABLE accounts MODIFY age INT NOT NULL;          -- change a column
ALTER TABLE accounts ADD UNIQUE (phone);               -- add a constraint
ALTER TABLE accounts DROP COLUMN balance;              -- remove a column

Reading each line: ADD phone VARCHAR(20) tacks a new column onto the end. MODIFY age INT NOT NULL changes the age column’s definition. ADD UNIQUE (phone) attaches a UNIQUE constraint to an existing column. DROP COLUMN balance removes a column (and its data) for good. To delete an entire table you would use DROP TABLE accounts; — also permanent, so handle with care.