Kekkei - Advancing Financial Science For Everyone

Introduction to Constraints

Databases need rules to keep data valid and consistent. What if someone tries to:

Insert a product with a negative price
Create a user with a duplicate email
Add an employee without a required name field
Enter an invalid age (150 years old)

Constraints are rules you define on table columns to enforce data integrity. They're like validators that automatically check data before it's saved.

A rule defined on a table column that restricts what values can be inserted or updated. Constraints ensure data quality, prevent errors, and maintain referential integrity. They're enforced by the database automatically.

Why use constraints?

Data integrity: Keep data valid and consistent
Automatic validation: Database checks rules, not application code
Prevent errors: Catch bad data before it's saved
Document requirements: Constraints show what data is allowed
Performance: Database can optimize based on constraints

Main constraint types:

PRIMARY KEY - unique identifier for rows
FOREIGN KEY - links to another table
UNIQUE - no duplicate values allowed
NOT NULL - value required, can't be empty
CHECK - custom validation rules
DEFAULT - automatic value if none provided

PRIMARY KEY Constraint

A PRIMARY KEY uniquely identifies each row in a table. Every table should have one!

sql

-- Create table with PRIMARY KEY
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

PRIMARY KEY rules:

Must be UNIQUE (no duplicates)
Cannot be NULL
Only one PRIMARY KEY per table
Usually an auto-incrementing ID column

Try to insert duplicates:

sql

INSERT INTO users (user_id, username, email)
VALUES (1, 'alice', 'alice@example.com');

-- Try duplicate ID
INSERT INTO users (user_id, username, email)
VALUES (1, 'bob', 'bob@example.com');

UNIQUE Constraint

UNIQUE ensures no two rows have the same value in that column. Unlike PRIMARY KEY, you can have multiple UNIQUE columns:

sql

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    phone VARCHAR(20) UNIQUE
);

sql

-- Insert first user successfully
INSERT INTO users (username, email, phone)
VALUES ('alice', 'alice@example.com', '555-1234');

-- Try duplicate email
INSERT INTO users (username, email, phone)
VALUES ('bob', 'alice@example.com', '555-5678');

UNIQUE vs PRIMARY KEY:

Table can have one PRIMARY KEY but multiple UNIQUE columns
PRIMARY KEY cannot be NULL; UNIQUE can be NULL (unless also NOT NULL)
PRIMARY KEY is the main identifier; UNIQUE is for alternate unique values

NOT NULL Constraint

NOT NULL means the column must have a value - it cannot be empty:

sql

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(200) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    description TEXT,
    stock_quantity INT NOT NULL DEFAULT 0
);

sql

-- This works - all NOT NULL columns provided
INSERT INTO products (product_name, price)
VALUES ('Laptop', 999.99);

-- This fails - missing required price
INSERT INTO products (product_name)
VALUES ('Mouse');

When to use NOT NULL:

Essential fields: product name, customer email, order date
Fields required for business logic
Foreign keys that must reference another record

When to allow NULL:

Optional fields: middle name, phone number
Fields that might not apply: end_date for current employees

CHECK Constraint

CHECK constraints enforce custom rules using conditions:

sql

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(200) NOT NULL,
    price DECIMAL(10, 2) NOT NULL CHECK (price > 0),
    discount_percent INT CHECK (discount_percent BETWEEN 0 AND 100),
    stock_quantity INT NOT NULL DEFAULT 0 CHECK (stock_quantity >= 0),
    rating DECIMAL(3, 2) CHECK (rating >= 0 AND rating <= 5.00)
);

sql

-- This works - passes all checks
INSERT INTO products (product_name, price, discount_percent, rating)
VALUES ('Laptop', 999.99, 15, 4.5);

-- This fails - negative price
INSERT INTO products (product_name, price)
VALUES ('Mouse', -29.99);

sql

-- This fails - invalid discount
INSERT INTO products (product_name, price, discount_percent)
VALUES ('Keyboard', 79.99, 150);

Common CHECK constraints:

Price/salary > 0 (no negative values)
Age BETWEEN 18 AND 120
Percentage BETWEEN 0 AND 100
Status IN ('active', 'inactive', 'pending')
email LIKE '%@%.%' (basic email format)

DEFAULT Constraint

DEFAULT provides automatic values when none is specified:

sql

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL DEFAULT CURRENT_DATE,
    status VARCHAR(20) DEFAULT 'pending',
    total DECIMAL(10, 2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_paid BOOLEAN DEFAULT FALSE
);

sql

-- Insert without specifying defaults
INSERT INTO orders (customer_id, total)
VALUES (1, 299.99);

SELECT * FROM orders WHERE order_id = 1;

Default values automatically applied:

order_date = today's date
status = 'pending'
created_at = current timestamp
is_paid = FALSE (0)

Common DEFAULT values:

Numbers: 0, -1, 100
Dates: CURRENT_DATE, CURRENT_TIMESTAMP
Strings: 'active', 'N/A', 'unknown'
Booleans: TRUE, FALSE

Combining Multiple Constraints

Columns can have multiple constraints:

sql

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(100) NOT NULL UNIQUE CHECK (email LIKE '%@%.%'),
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    salary DECIMAL(10, 2) NOT NULL CHECK (salary >= 30000 AND salary <= 500000),
    department VARCHAR(50) NOT NULL DEFAULT 'Unassigned',
    hire_date DATE NOT NULL DEFAULT CURRENT_DATE,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    age INT CHECK (age >= 18 AND age <= 75)
);

This table enforces:

email is required, unique, and has basic email format
first_name and last_name are required
salary must be between $30k and$ 500k
department defaults to 'Unassigned' if not provided
hire_date defaults to today
is_active defaults to TRUE
age must be between 18 and 75 (if provided)

Adding Constraints to Existing Tables

Use ALTER TABLE to add constraints after table creation:

sql

-- Add UNIQUE constraint
ALTER TABLE users
ADD CONSTRAINT unique_email UNIQUE (email);

-- Add CHECK constraint
ALTER TABLE products
ADD CONSTRAINT positive_price CHECK (price > 0);

-- Add NOT NULL constraint
ALTER TABLE orders
MODIFY COLUMN customer_id INT NOT NULL;

-- Add DEFAULT constraint
ALTER TABLE products
ALTER COLUMN stock_quantity SET DEFAULT 0;

Dropping Constraints

Remove constraints when no longer needed:

sql

-- Drop named constraint
ALTER TABLE users
DROP CONSTRAINT unique_email;

-- Drop CHECK constraint
ALTER TABLE products
DROP CHECK positive_price;

Be careful: Dropping constraints removes data validation. Existing data stays, but new data won't be validated against the dropped constraint.

Viewing Table Constraints

See what constraints exist on a table:

sql

-- MySQL
SHOW CREATE TABLE products;

-- Or use DESCRIBE
DESCRIBE products;

Practical Example: E-commerce System

Real-world table with comprehensive constraints:

sql

CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(100) NOT NULL UNIQUE CHECK (email LIKE '%@%.%'),
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    phone VARCHAR(20) UNIQUE,
    date_of_birth DATE CHECK (date_of_birth <= CURRENT_DATE),
    account_balance DECIMAL(10, 2) NOT NULL DEFAULT 0.00 CHECK (account_balance >= 0),
    loyalty_points INT NOT NULL DEFAULT 0 CHECK (loyalty_points >= 0),
    account_status VARCHAR(20) NOT NULL DEFAULT 'active'
        CHECK (account_status IN ('active', 'suspended', 'closed')),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP NULL
);

sql

-- Insert valid customer
INSERT INTO customers (email, password_hash, first_name, last_name, phone)
VALUES ('alice@example.com', 'hashed_pw_123', 'Alice', 'Smith', '555-1234');

SELECT customer_id, email, first_name, account_balance, loyalty_points, account_status
FROM customers WHERE email = 'alice@example.com';

Common Mistakes to Avoid

Don't make these mistakes:

Too many constraints: ❌
- Over-constraining makes data hard to insert
- Balance validation with flexibility
Missing NOT NULL on important fields: ❌
- Required fields should be NOT NULL
- Prevents incomplete data
No CHECK on numeric ranges: ❌
- Always validate prices > 0, ages reasonable, etc.
- Database catches errors automatically
Not using UNIQUE on natural keys: ❌
- Email, username, SSN should be UNIQUE
- Prevents duplicate accounts
Complex CHECK constraints: ⚠️
- Keep CHECK simple and clear
- Complex business logic might belong in application
Forgetting DEFAULT for optional fields: ❌
- Provide sensible defaults (0, FALSE, 'unknown')
- Makes INSERT statements simpler
Using CHECK for format validation: ⚠️
- CHECK email LIKE '%@%' is basic
- Application should do detailed format checking

Constraint Benefits

Why Use Constraints

Benefit	Description
Data integrity	Ensures data is valid and consistent
Error prevention	Catches bad data before it's saved
Self-documenting	Constraints show what data is valid
Performance	Database can optimize based on constraints
Security	Prevents SQL injection through validation
Maintenance	Centralized validation in database, not scattered in code

Key Takeaways

What you learned:

✅ PRIMARY KEY - unique identifier (unique + not null) ✅ UNIQUE - no duplicate values allowed ✅ NOT NULL - value required, cannot be empty ✅ CHECK - custom validation rules (price > 0) ✅ DEFAULT - automatic value if none provided ✅ Constraints enforce data integrity automatically ✅ Can combine multiple constraints on one column ✅ Add constraints with CREATE TABLE or ALTER TABLE ✅ View constraints with SHOW CREATE TABLE or DESCRIBE ✅ Drop constraints with ALTER TABLE DROP CONSTRAINT

Best practices:

Always use PRIMARY KEY
Mark required fields as NOT NULL
Add CHECK for valid ranges
Use UNIQUE for natural keys (email, username)
Provide DEFAULT for optional fields
Keep constraints simple and clear

Practice Exercise: Create tables with appropriate constraints:

users table - email unique and not null, username unique, age >= 18
products table - price > 0, stock >= 0, rating between 0 and 5
orders table - status in ('pending', 'shipped', 'delivered'), total > 0
employees table - salary range 30k-500k, hire_date not in future
Add CHECK constraint to existing table for percentage field (0-100)

Introduction to ConstraintsFocusStart Focus Mode

PRIMARY KEY ConstraintFocusStart Focus Mode

UNIQUE ConstraintFocusStart Focus Mode

NOT NULL ConstraintFocusStart Focus Mode

CHECK ConstraintFocusStart Focus Mode

DEFAULT ConstraintFocusStart Focus Mode

Combining Multiple ConstraintsFocusStart Focus Mode

Adding Constraints to Existing TablesFocusStart Focus Mode

Dropping ConstraintsFocusStart Focus Mode

Viewing Table ConstraintsFocusStart Focus Mode

Practical Example: E-commerce SystemFocusStart Focus Mode

Common Mistakes to AvoidFocusStart Focus Mode

Constraint BenefitsFocusStart Focus Mode

Key TakeawaysFocusStart Focus Mode