MYSQL Tutorial

MySQL Constraints

Constraints enforce rules at the table level to ensure data integrity. Common constraints include:

1. NOT NULL

Ensures a column cannot have NULL values.

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

2. UNIQUE

Ensures all values in a column are distinct.

CREATE TABLE products (
  sku VARCHAR(20) UNIQUE,
  name VARCHAR(100),
  price DECIMAL(8,2)
);
  

3. PRIMARY KEY

Uniquely identifies each row and implies NOT NULL and UNIQUE.

CREATE TABLE orders (
  order_id INT,
  customer_id INT,
  PRIMARY KEY (order_id)
);
  

4. FOREIGN KEY

Enforces referential integrity between two tables.

CREATE TABLE order_items (
  item_id INT AUTO_INCREMENT PRIMARY KEY,
  order_id INT,
  product_id INT,
  quantity INT,
  FOREIGN KEY (order_id) REFERENCES orders(order_id),
  FOREIGN KEY (product_id) REFERENCES products(sku)
);
  

5. DEFAULT

Specifies a default value for a column when none is provided.

CREATE TABLE posts (
  post_id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
  

6. CHECK (MySQL 8.0+)

Ensures values in a column meet a Boolean condition.

CREATE TABLE accounts (
  account_id INT AUTO_INCREMENT PRIMARY KEY,
  balance DECIMAL(10,2),
  CHECK (balance >= 0)
);
  

Best Practices & Tips

• Define primary keys on every table for uniqueness.
• Use foreign keys to maintain referential integrity.
• Apply NOT NULL and DEFAULT to enforce required data and sensible defaults.
• Use UNIQUE to prevent duplicate entries (e.g., emails, SKUs).
• Leverage CHECK for custom business rules (MySQL 8.0+).