Kenhuang Academy
  • English

      • Constraints

      Constraints are rules applied to table columns to ensure data integrity. This chapter introduces various types of constraints.

      Types of Constraints

      1. PRIMARY KEY - Unique identifier
      2. FOREIGN KEY - Referential integrity
      3. UNIQUE - Unique values
      4. NOT NULL - No null values
      5. CHECK - Value validation
      6. DEFAULT - Default values

      PRIMARY KEY

      -- Single column primary key
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- Composite primary key
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    PRIMARY KEY (order_id, product_id)
);

-- Add primary key to existing table
ALTER TABLE users
ADD PRIMARY KEY (id);

      FOREIGN KEY

      -- Create foreign key
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Foreign key with cascade
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

      UNIQUE

      -- Unique constraint
CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE,
    username VARCHAR(50) UNIQUE
);

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

      NOT NULL

      -- Not null constraint
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

      CHECK

      -- Check constraint
CREATE TABLE users (
    id INT PRIMARY KEY,
    age INT CHECK (age >= 18),
    email VARCHAR(100) CHECK (email LIKE '%@%')
);

      DEFAULT

      -- Default value
CREATE TABLE users (
    id INT PRIMARY KEY,
    status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

      Summary

      • PRIMARY KEY: Unique identifier
      • FOREIGN KEY: Referential integrity
      • UNIQUE: Unique values
      • NOT NULL: Required values
      • CHECK: Value validation
      • DEFAULT: Default values

      Next Step: Learn VIEWS