Lesson 14 of 2228 min read

CREATE TABLE in SQL: Column Definitions and Constraints Explained

Master the CREATE TABLE statement in SQL, including column definitions and constraints like NOT NULL, UNIQUE, DEFAULT, and CHECK.

CREATE TABLE in SQL: Column Definitions and Constraints Explained

If CREATE DATABASE builds the cabinet, CREATE TABLE builds the folders inside it — and this is where most of the real design work in SQL happens. Every column you define and every constraint you attach shapes what data the database will accept, reject, or quietly correct using a default. This lesson covers the full create table in sql syntax along with the four foundational constraints — NOT NULL, UNIQUE, DEFAULT, and CHECK — that every well-designed table relies on.

What Is CREATE TABLE?

CREATE TABLE is the DDL statement that defines a new table by listing its column names, data types, and constraints. Once created, the table's structure (called its schema) determines exactly what kind of data each column will accept going forward, until the table is altered or dropped.

What You'll Learn

  • Write a complete CREATE TABLE statement with multiple columns and data types.
  • Apply NOT NULL, UNIQUE, DEFAULT, and CHECK constraints correctly.
  • Understand the difference between column-level and table-level constraints.
  • Design a table schema that enforces data quality from the start.

Key Terms to Know

  • Schema: The structural definition of a table — its columns, types, and constraints.
  • Constraint: A rule attached to a column or table that restricts what values are allowed.
  • NOT NULL: A constraint requiring a column to always have a value, rejecting NULL.
  • DEFAULT: A constraint that supplies an automatic value when none is provided during INSERT.
  • CHECK: A constraint that validates a column's value against a custom boolean condition.

Basic CREATE TABLE Syntax and Column Definitions

A CREATE TABLE statement lists the table name followed by a parenthesized, comma-separated list of column definitions, each consisting of a column name, a data type, and optional constraints. For example, CREATE TABLE employees (employee_id INT, full_name VARCHAR(100)); defines a bare-minimum table with no constraints at all — which is rarely what you actually want in production.

NOT NULL and UNIQUE: Enforcing Required and Distinct Values

NOT NULL forces a column to always contain a value — without it, MySQL happily accepts NULL, which can silently break later calculations like SUM or AVG if not handled carefully. Use NOT NULL on any column that must always be filled in, such as a customer's name or an order's total amount.

UNIQUE ensures no two rows can share the same value in that column, which is essential for things like email addresses or usernames where duplicates would break login logic. A table can have multiple UNIQUE columns, unlike PRIMARY KEY, which is limited to one per table (covered in the next lesson).

DEFAULT and CHECK: Automatic Values and Custom Validation

DEFAULT supplies a fallback value automatically when an INSERT statement doesn't specify one for that column — for example, DEFAULT 0 for a stock_qty column, or DEFAULT CURRENT_TIMESTAMP for a created_at column. This removes the need for application code to remember to always pass every value.

CHECK validates a column against a custom boolean expression, rejecting any INSERT or UPDATE that violates it — for instance, CHECK (price > 0) prevents a product from ever being stored with a negative or zero price. CHECK constraints can reference a single column or combine multiple columns within the same row.

Visual Summary

Imagine a table as a printed form with labeled blank fields. Each column definition draws one field and decides what kind of answer fits (data type). NOT NULL means the field can't be left blank, UNIQUE means no two forms can have the same answer in that field, DEFAULT pre-fills an answer if left blank, and CHECK is a rule the form-checker enforces before accepting the form at all.

Core Column Constraints at a Glance

ConstraintPurposeExample
NOT NULLDisallow missing valuesemail VARCHAR(100) NOT NULL
UNIQUEDisallow duplicate valuesemail VARCHAR(100) UNIQUE
DEFAULTAuto-fill a value if none givenstatus VARCHAR(20) DEFAULT 'pending'
CHECKCustom validation ruleprice DECIMAL(10,2) CHECK (price > 0)

SQL Example

-- A table demonstrating NOT NULL, UNIQUE, DEFAULT, and CHECK together
CREATE TABLE employees (
  employee_id   INT            PRIMARY KEY AUTO_INCREMENT,
  full_name     VARCHAR(100)   NOT NULL,
  email         VARCHAR(150)   NOT NULL UNIQUE,
  department    VARCHAR(50)    NOT NULL DEFAULT 'Unassigned',
  salary        DECIMAL(10, 2) NOT NULL CHECK (salary > 0),
  hire_date     DATE           NOT NULL DEFAULT (CURRENT_DATE),
  is_active     BOOLEAN        NOT NULL DEFAULT TRUE
);

-- This insert relies on defaults for department, hire_date, and is_active
INSERT INTO employees (full_name, email, salary)
VALUES ('Meera Iyer', 'meera.iyer@example.com', 55000.00);

Even though the INSERT statement only supplies full_name, email, and salary, the row still gets a department of 'Unassigned', today's date for hire_date, and is_active set to TRUE automatically because of the DEFAULT constraints. The CHECK constraint on salary would reject any attempt to insert a zero or negative salary.

Real-World Examples

  • User registration systems use UNIQUE on email or username columns to prevent duplicate accounts.
  • E-commerce platforms use CHECK constraints to ensure product prices and stock quantities are never negative.
  • Subscription platforms use DEFAULT values for plan_status so every new signup automatically starts as 'trial' without extra application logic.
  • HR systems enforce NOT NULL on employee_id and full_name since an employee record without these is meaningless.

Best Practices and Pro Tips

  • Add constraints at table creation time rather than retrofitting them later — fixing constraint violations in data that already exists in production is far more painful than preventing them upfront.
  • Use DEFAULT CURRENT_TIMESTAMP (or DEFAULT (CURRENT_DATE) for DATE columns) for audit columns like created_at instead of relying on application code to set them.
  • Combine NOT NULL with UNIQUE for any column that must always be present and never duplicated, such as email — using only one of the two leaves a gap in data integrity.

Common Mistakes to Avoid

  • Forgetting NOT NULL on columns that should always have a value, allowing NULLs to silently creep into reports and aggregate calculations.
  • Relying on application code alone to prevent duplicates instead of also adding a UNIQUE constraint at the database level.
  • Writing overly complex CHECK constraints that are hard to read instead of breaking validation logic into simpler, well-named rules.
  • Assuming older MySQL versions enforce CHECK constraints — versions before MySQL 8.0.16 silently ignored CHECK, so it's worth confirming your MySQL version.

Interview Questions

Q1. What is the difference between NOT NULL and DEFAULT?

NOT NULL forces a value to be provided and rejects NULL outright. DEFAULT supplies an automatic fallback value when none is explicitly given in an INSERT, but the column could still technically allow NULL unless NOT NULL is also applied.

Q2. Can a table have more than one UNIQUE constraint?

Yes. A table can have multiple UNIQUE constraints across different columns, unlike PRIMARY KEY, which is limited to one definition per table (though it can span multiple columns as a composite key).

Q3. What does a CHECK constraint do, and when did MySQL start enforcing it?

CHECK validates that a column's value satisfies a custom boolean condition, such as CHECK (age >= 18). MySQL only began enforcing CHECK constraints starting in version 8.0.16; earlier versions parsed but silently ignored them.

Q4. What happens if you try to insert a row that violates a UNIQUE constraint?

MySQL rejects the insert and raises a duplicate entry error, and the row is not added to the table. The application must handle this error, typically by informing the user that the value already exists.

Practice MCQs

1. Which constraint prevents a column from ever containing NULL?

  1. UNIQUE
  2. DEFAULT
  3. NOT NULL
  4. CHECK

Answer: C. NOT NULL

Explanation: NOT NULL is the constraint that disallows NULL values in a column.

2. Which constraint would best ensure a product's stock quantity is never negative?

  1. UNIQUE
  2. DEFAULT
  3. CHECK (stock_qty >= 0)
  4. NOT NULL

Answer: C. CHECK (stock_qty >= 0)

Explanation: CHECK validates the value against a custom boolean condition, which is exactly what's needed to prevent negative stock.

3. What happens if an INSERT omits a column that has a DEFAULT value?

  1. The insert fails
  2. MySQL uses the default value automatically
  3. The column stays empty string
  4. MySQL throws a warning only

Answer: B. MySQL uses the default value automatically

Explanation: When a column with a DEFAULT is omitted from an INSERT, MySQL automatically fills it with the specified default.

Quick Revision Points

  • NOT NULL = value required; UNIQUE = no duplicates; DEFAULT = auto-fill; CHECK = custom validation rule.
  • A table can have many UNIQUE constraints but only one PRIMARY KEY definition.
  • CHECK constraints are only enforced from MySQL 8.0.16 onward.
  • Constraints should be defined at table creation time wherever possible to enforce data quality from day one.

Conclusion

  • CREATE TABLE is where data quality is designed in, not bolted on later.
  • NOT NULL, UNIQUE, DEFAULT, and CHECK together cover the majority of everyday data validation needs.
  • Well-chosen constraints reduce the amount of validation logic your application code needs to handle.

CREATE TABLE in SQL defines both the shape of your data through column definitions and the rules that protect its quality through constraints. NOT NULL, UNIQUE, DEFAULT, and CHECK form the foundation of reliable schema design, catching bad data before it ever reaches a report or an application screen. With tables and their core constraints understood, the next step is PRIMARY KEY and AUTO_INCREMENT — the constraint that uniquely identifies every row.

Frequently Asked Questions

A column-level constraint is written directly after a single column's definition, like email VARCHAR(150) UNIQUE. A table-level constraint is written separately, often used when a constraint spans multiple columns, like PRIMARY KEY (order_id, product_id) for a composite key.

Yes, using ALTER TABLE with ADD CONSTRAINT, which is covered in detail in the ALTER TABLE lesson later in this module. However, adding a constraint to an existing table can fail if current data already violates it.

Yes. Using CONSTRAINT constraint_name CHECK (condition) (or similarly for other constraint types) gives the constraint a custom name, which makes it easier to reference when dropping or modifying it later.

Constraints do add a small amount of validation overhead on writes, but this cost is usually negligible compared to the risk of bad data, and is almost always worth the trade-off for data integrity.

Use DATE or DATETIME so MySQL can correctly compare values chronologically, such as CHECK (end_date > start_date). Storing dates as VARCHAR would make such comparisons unreliable.