Keys and Constraints

Primary Key: Unique Identifier

A primary key is a column or set of columns that uniquely identifies each row in a table. It ensures that no two rows have the same value and that the column cannot contain NULL values.

Properties of a Primary Key

1. Uniqueness: Every value must be distinct
2. Not Null: Cannot contain NULL values
3. One per table: A table can have only one primary key
4. Immutable: Values should not change (stable identifier)

Creating a Primary Key

-- Method 1: Inline definition
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL
);

-- Method 2: Table constraint (allows composite keys)
CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    PRIMARY KEY (student_id, course_id)
);

-- Method 3: Auto-incrementing primary key
CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL
);

Adding Primary Key to Existing Table

-- Add primary key to existing table
ALTER TABLE employees
ADD CONSTRAINT pk_employees PRIMARY KEY (employee_id);

-- Create table then add primary key
CREATE TABLE orders (
    order_number VARCHAR(20),
    customer_id INT,
    order_date DATE
);

ALTER TABLE orders
ADD CONSTRAINT pk_orders PRIMARY KEY (order_number);

Primary Key Selection Best Practices

• Use surrogate keys (auto-incrementing integers) for most cases
• Natural keys (like email) can be used but are less stable
• Avoid business data as primary keys (they may change)
• Use meaningful names like id or tablename_id

-- Good: Surrogate key
CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL
);

-- Acceptable: Natural key for lookup tables
CREATE TABLE countries (
    country_code CHAR(2) PRIMARY KEY,
    country_name VARCHAR(100) NOT NULL
);

Primary keys are essential for data integrity and for establishing relationships between tables.

Foreign Key: Reference to Another Table

A foreign key is a column that creates a link between two tables by referencing the primary key of another table. It enforces referential integrity, ensuring that relationships between tables remain consistent.

Foreign Key Properties

1. References: Points to a primary key or unique key in another table
2. Nullable: Can contain NULL values (unless constrained otherwise)
3. Multiple allowed: A table can have multiple foreign keys
4. Values must exist: Values must match referenced values or be NULL

Creating Foreign Keys

-- Create tables with foreign key
CREATE TABLE departments (
    dept_id INT PRIMARY KEY AUTO_INCREMENT,
    dept_name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(100) NOT NULL,
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

-- Foreign key with ON DELETE and ON UPDATE actions
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    FOREIGN KEY (customer_id) 
        REFERENCES customers(customer_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

Foreign Key Actions

Action	Behavior
CASCADE	Delete/update referencing rows
SET NULL	Set foreign key to NULL
SET DEFAULT	Set foreign key to default value
RESTRICT	Prevent delete/update if referencing rows exist
NO ACTION	Same as RESTRICT (default)

-- Example: Different foreign key actions
CREATE TABLE order_items (
    item_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    FOREIGN KEY (order_id) 
        REFERENCES orders(order_id)
        ON DELETE CASCADE,
    FOREIGN KEY (product_id) 
        REFERENCES products(product_id)
        ON DELETE RESTRICT
);

Adding Foreign Key to Existing Table

-- Add foreign key constraint
ALTER TABLE employees
ADD CONSTRAINT fk_emp_dept
FOREIGN KEY (dept_id) REFERENCES departments(dept_id);

-- Drop foreign key
ALTER TABLE employees
DROP FOREIGN KEY fk_emp_dept;

Referential Integrity

Foreign keys prevent orphaned records. You cannot insert a row with a foreign key value that doesn't exist in the referenced table (unless it's NULL).

-- This will fail if department 99 doesn't exist
INSERT INTO employees (emp_name, dept_id)
VALUES ('John Doe', 99);  -- ERROR: foreign key constraint fails

-- This will succeed
INSERT INTO employees (emp_name, dept_id)
VALUES ('John Doe', 1);  -- Assuming dept_id 1 exists

-- This will succeed (NULL is allowed)
INSERT INTO employees (emp_name, dept_id)
VALUES ('Jane Doe', NULL);

Foreign keys are crucial for maintaining data consistency across related tables.

Candidate Key: Potential Primary Keys

A candidate key is a column or set of columns that could serve as the primary key. It must be unique and contain no NULL values. A table can have multiple candidate keys, but only one can be the primary key.

Candidate Key Properties

1. Uniqueness: Every combination of values is distinct
2. Minimal: No subset of the key has the same properties
3. Non-null: Cannot contain NULL values

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
);

-- Candidate keys: user_id, username, email
-- user_id is the primary key
-- username, email, phone are alternate keys

Composite Key: Multi-Column Key

A composite key is a primary key consisting of two or more columns. It's used when no single column can uniquely identify a row.

-- Composite primary key example
CREATE TABLE course_enrollments (
    student_id INT,
    course_id INT,
    semester VARCHAR(20),
    enrollment_date DATE,
    grade VARCHAR(2),
    PRIMARY KEY (student_id, course_id, semester)
);

-- Composite foreign key
CREATE TABLE section_students (
    section_id INT,
    student_id INT,
    course_id INT,
    semester VARCHAR(20),
    PRIMARY KEY (section_id, student_id),
    FOREIGN KEY (student_id, course_id, semester) 
        REFERENCES course_enrollments(student_id, course_id, semester)
);

When to Use Composite Keys

• Many-to-many relationships: Junction tables
• Time-series data: Entity + timestamp combinations
• Partitioning: Composite keys for table partitioning

-- Example: Junction table for many-to-many
CREATE TABLE student_courses (
    student_id INT,
    course_id INT,
    enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

Surrogate vs Natural Keys

Type	Pros	Cons
Surrogate (ID)	Stable, simple, fast joins	Extra column, meaningless
Natural (Name/Code)	Meaningful, no extra column	May change, composite, larger

-- Surrogate key approach
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sku VARCHAR(20) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL
);

-- Natural key approach
CREATE TABLE countries (
    iso_code CHAR(2) PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    population INT
);

Unique Constraint

-- Add unique constraint to existing table
ALTER TABLE users
ADD CONSTRAINT uq_users_email UNIQUE (email);

-- Unique constraint on multiple columns
ALTER TABLE employees
ADD CONSTRAINT uq_emp_dept UNIQUE (first_name, last_name, department_id);

Candidate and composite keys are powerful tools for modeling complex relationships and ensuring data uniqueness.

Constraints: Enforcing Data Integrity

Constraints are rules enforced on data columns to ensure the accuracy, reliability, and integrity of the data in the database. They prevent invalid data from being inserted.

Types of Constraints

1. NOT NULL

Ensures a column cannot contain NULL values.

CREATE TABLE contacts (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20)  -- NULL allowed
);

-- Adding NOT NULL to existing column
ALTER TABLE contacts
MODIFY COLUMN phone VARCHAR(20) NOT NULL;

2. UNIQUE

Ensures all values in a column are different.

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE
);

-- Composite unique constraint
CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_number VARCHAR(20) NOT NULL,
    UNIQUE (customer_id, order_number)
);

3. CHECK

Validates that values meet a specific condition.

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL CHECK (price > 0),
    stock INT NOT NULL CHECK (stock >= 0),
    discount DECIMAL(3,2) CHECK (discount BETWEEN 0 AND 0.5)
);

-- Multiple conditions
CREATE TABLE employees (
    id INT PRIMARY KEY,
    age INT CHECK (age >= 18 AND age <= 65),
    salary DECIMAL(10,2) CHECK (salary > 0)
);

4. DEFAULT

Provides a default value when no value is specified.

CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    status VARCHAR(20) DEFAULT 'draft',
    views INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_published BOOLEAN DEFAULT FALSE
);

-- Insert without specifying defaults
INSERT INTO articles (title) VALUES ('My Article');
-- status = 'draft', views = 0, created_at = current time

5. Primary Key Constraint

Combines NOT NULL and UNIQUE.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

6. Foreign Key Constraint

Referential integrity between tables.

CREATE TABLE enrollments (
    id INT PRIMARY KEY,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

Adding Constraints to Existing Tables

-- Add NOT NULL
ALTER TABLE employees
MODIFY COLUMN email VARCHAR(100) NOT NULL;

-- Add UNIQUE
ALTER TABLE employees
ADD CONSTRAINT uq_emp_email UNIQUE (email);

-- Add CHECK
ALTER TABLE employees
ADD CONSTRAINT chk_salary CHECK (salary > 0);

-- Add DEFAULT
ALTER TABLE employees
ALTER COLUMN status SET DEFAULT 'active';

-- Drop constraint
ALTER TABLE employees
DROP CONSTRAINT chk_salary;

-- Drop unique constraint
ALTER TABLE employees
DROP INDEX uq_emp_email;

Constraint Naming Best Practices

• Use pk_tablename for primary keys
• Use fk_tablename_referenced for foreign keys
• Use uq_tablename_column for unique constraints
• Use chk_tablename_rule for check constraints

CREATE TABLE orders (
    order_id INT,
    customer_id INT,
    amount DECIMAL(10,2),
    CONSTRAINT pk_orders PRIMARY KEY (order_id),
    CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    CONSTRAINT chk_orders_amount CHECK (amount > 0)
);

Constraints are your first line of defense against data corruption and inconsistencies.