Objective

Create tables with various constraints to ensure data integrity. Constraints enforce rules on data columns, preventing invalid data from being inserted.

Step 1: Primary Key Constraint

Create a table with a primary key for unique row identification.

CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255),
    hire_date DATE
);

INSERT INTO employees (employee_id, first_name, last_name, email, hire_date) VALUES
    (1, 'John', 'Smith', 'john.smith@company.com', '2023-01-15'),
    (2, 'Jane', 'Doe', 'jane.doe@company.com', '2023-02-20'),
    (3, 'Bob', 'Wilson', 'bob.wilson@company.com', '2023-03-10');

Step 2: UNIQUE Constraint

Create a table with unique constraints on specific columns.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    display_name VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO users (id, username, email, display_name) VALUES
    (1, 'alice', 'alice@example.com', 'Alice Johnson'),
    (2, 'bob', 'bob@example.com', 'Bob Smith'),
    (3, 'charlie', 'charlie@example.com', 'Charlie Brown');

Step 3: NOT NULL Constraint

Create a table where certain fields must always have values.

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_name VARCHAR(255) NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    shipping_address TEXT NOT NULL,
    notes TEXT
);

INSERT INTO orders (order_id, customer_name, order_date, total_amount, shipping_address) VALUES
    (1, 'Customer A', '2024-01-15', 99.99, '123 Main St, City, State 12345'),
    (2, 'Customer B', '2024-01-16', 149.50, '456 Oak Ave, Town, State 67890');

Step 4: CHECK Constraint

Create a table with check constraints to validate data values.

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL CHECK (price > 0),
    quantity INTEGER NOT NULL CHECK (quantity >= 0),
    discount_percent DECIMAL(5, 2) CHECK (discount_percent >= 0 AND discount_percent <= 100),
    status VARCHAR(20) CHECK (status IN ('active', 'inactive', 'discontinued'))
);

INSERT INTO products (id, name, price, quantity, discount_percent, status) VALUES
    (1, 'Widget A', 29.99, 100, 10.00, 'active'),
    (2, 'Widget B', 49.99, 50, 0.00, 'active'),
    (3, 'Widget C', 19.99, 0, 25.00, 'discontinued');

Step 5: Multiple Constraints Combined

Create a comprehensive table with multiple constraint types.

CREATE TABLE customer_accounts (
    account_id INTEGER PRIMARY KEY,
    customer_email VARCHAR(255) NOT NULL UNIQUE,
    customer_phone VARCHAR(20) UNIQUE,
    account_balance DECIMAL(12, 2) NOT NULL DEFAULT 0.00 CHECK (account_balance >= 0),
    credit_limit DECIMAL(12, 2) CHECK (credit_limit >= 0),
    account_status VARCHAR(20) NOT NULL DEFAULT 'pending'
        CHECK (account_status IN ('pending', 'active', 'suspended', 'closed')),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_activity TIMESTAMP
);

INSERT INTO customer_accounts (account_id, customer_email, customer_phone, account_balance, credit_limit, account_status) VALUES
    (1, 'premium@example.com', '555-0100', 5000.00, 10000.00, 'active'),
    (2, 'standard@example.com', '555-0200', 250.00, 1000.00, 'active'),
    (3, 'new@example.com', NULL, 0.00, 500.00, 'pending');

Step 6: Query with Constraint Awareness

Query data respecting the constraints.

-- Find active products with inventory
SELECT name, price, quantity
FROM products
WHERE status = 'active' AND quantity > 0;

-- Find accounts with high balances
SELECT customer_email, account_balance, credit_limit
FROM customer_accounts
WHERE account_status = 'active'
ORDER BY account_balance DESC;

Cleanup (Optional)

DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS customer_accounts;

Expected Outcomes

PRIMARY KEY enforces unique row identification
UNIQUE prevents duplicate values in columns
NOT NULL requires values in specified columns
CHECK validates data against conditions
DEFAULT provides automatic values

Constraint Summary

Constraint	Purpose	Example
PRIMARY KEY	Unique row identifier	`id INTEGER PRIMARY KEY`
UNIQUE	Prevent duplicates	`email VARCHAR(255) UNIQUE`
NOT NULL	Require value	`name VARCHAR(100) NOT NULL`
CHECK	Validate conditions	`CHECK (price > 0)`
DEFAULT	Auto-fill value	`DEFAULT CURRENT_TIMESTAMP`

Key Concepts Learned

Data integrity through constraints
Combining multiple constraints
CHECK for business rule validation
DEFAULT for automatic values
Proper constraint placement in CREATE TABLE

Primary Keys and Constraints