Primary Keys and Constraints

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

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