How to Use SQL Constraints

-- NOT NULL constraint
CREATE TABLE accounts (
  id INTEGER PRIMARY KEY,
  username TEXT NOT NULL,
  email TEXT NOT NULL,
  age INTEGER
);

-- This will fail (username is NULL)
-- INSERT INTO accounts (email) VALUES ('test@example.com');

-- This works
INSERT INTO accounts (username, email, age) VALUES
  ('alice', 'alice@example.com', 25),
  ('bob', 'bob@example.com', 30);

-- UNIQUE constraint
CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  sku TEXT UNIQUE,
  name TEXT NOT NULL
);

INSERT INTO products (sku, name) VALUES
  ('SKU001', 'Laptop'),
  ('SKU002', 'Mouse');

-- This will fail (duplicate SKU)
-- INSERT INTO products (sku, name) VALUES ('SKU001', 'Keyboard');

-- CHECK constraint
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  quantity INTEGER CHECK(quantity > 0),
  price REAL CHECK(price >= 0),
  status TEXT CHECK(status IN ('pending', 'shipped', 'delivered'))
);

INSERT INTO orders (quantity, price, status) VALUES
  (5, 99.99, 'pending'),
  (2, 49.99, 'shipped');

-- This will fail (quantity must be > 0)
-- INSERT INTO orders (quantity, price, status) VALUES (0, 10.00, 'pending');

-- This will fail (invalid status)
-- INSERT INTO orders (quantity, price, status) VALUES (1, 10.00, 'invalid');

-- DEFAULT constraint
CREATE TABLE articles (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  status TEXT DEFAULT 'draft',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO articles (title) VALUES ('My First Article');

SELECT * FROM articles;

-- FOREIGN KEY constraint
CREATE TABLE categories (
  id INTEGER PRIMARY KEY,
  name TEXT UNIQUE
);

INSERT INTO categories (name) VALUES ('Electronics'), ('Books');

CREATE TABLE items (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  category_id INTEGER,
  FOREIGN KEY (category_id) REFERENCES categories(id)
);

INSERT INTO items (name, category_id) VALUES
  ('Laptop', 1),
  ('Novel', 2);

-- This will fail (category 99 doesn't exist)
-- INSERT INTO items (name, category_id) VALUES ('Invalid', 99);

SELECT * FROM items;