-- Inline definition (table-level constraint)
CREATE TABLE users (
  id    INT          NOT NULL AUTO_INCREMENT,
  email VARCHAR(255) NOT NULL,
  name  VARCHAR(100) NOT NULL,
  PRIMARY KEY (id)
);

-- Shorthand (column-level constraint)
CREATE TABLE users (
  id    INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  name  VARCHAR(100) NOT NULL
);

-- Single-column PK (most entity tables)
CREATE TABLE products (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(200) NOT NULL,
  price       DECIMAL(10,2) NOT NULL
);

-- Composite PK (many-to-many junction table)
CREATE TABLE user_roles (
  user_id    INT NOT NULL,
  role_id    INT NOT NULL,
  granted_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, role_id)
);

-- Another composite PK example: order line items
CREATE TABLE order_items (
  order_id   INT            NOT NULL,
  product_id INT            NOT NULL,
  quantity   INT            NOT NULL DEFAULT 1,
  unit_price DECIMAL(10, 2) NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

-- Range scan on PK: sequential disk reads, very fast
SELECT * FROM orders WHERE id BETWEEN 5000 AND 5100;

-- Secondary index lookup: two B-tree traversals
-- 1. Find email in the secondary index -> get the PK value
-- 2. Use the PK to fetch the full row from the clustered index
SELECT * FROM users WHERE email = 'alice@example.com';

-- EXPLAIN shows the difference
EXPLAIN SELECT * FROM orders WHERE id = 42;
-- type: const  -- single PK lookup, best possible

EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';
-- type: ref    -- secondary index ref + clustered index lookup

-- AUTO_INCREMENT: sequential, low fragmentation
CREATE TABLE events (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  event_type VARCHAR(100) NOT NULL,
  created_at DATETIME     DEFAULT CURRENT_TIMESTAMP
);

-- UUID v4 (random): high fragmentation at scale
CREATE TABLE events_uuid (
  id         CHAR(36)     NOT NULL DEFAULT (UUID()),
  event_type VARCHAR(100) NOT NULL,
  PRIMARY KEY (id)
);

-- UUID v7 / ordered UUID (MySQL 8.0): time-sortable, low fragmentation
-- MySQL built-in: swap flag=1 reorders time fields for locality
CREATE TABLE events_ordered_uuid (
  id         BINARY(16)   NOT NULL DEFAULT (UUID_TO_BIN(UUID(), 1)),
  event_type VARCHAR(100) NOT NULL,
  PRIMARY KEY (id)
);

INSERT INTO events_ordered_uuid (event_type)
VALUES ('page_view');

SELECT BIN_TO_UUID(id, 1) AS id, event_type
FROM events_ordered_uuid;

-- MySQL 8.0 ordered UUID workaround (swap=1 moves timestamp to front)
INSERT INTO events (id, event_type)
VALUES (UUID_TO_BIN(UUID(), 1), 'login');

-- Check whether inserts are ordered by comparing raw hex values
SELECT HEX(id), event_type, created_at
FROM events
ORDER BY id
LIMIT 5;
-- The hex values should increase monotonically if using swap=1

-- Add a primary key to a table that has none
ALTER TABLE legacy_table ADD PRIMARY KEY (id);

-- Drop and replace the primary key
ALTER TABLE legacy_table DROP PRIMARY KEY;
ALTER TABLE legacy_table ADD PRIMARY KEY (new_id_col);

-- Add AUTO_INCREMENT to an existing PK column
ALTER TABLE legacy_table
  MODIFY COLUMN id INT NOT NULL AUTO_INCREMENT;

-- Check for tables without a primary key in the current database
SELECT t.table_name
FROM information_schema.tables t
LEFT JOIN information_schema.table_constraints c
  ON t.table_name    = c.table_name
 AND t.table_schema  = c.table_schema
 AND c.constraint_type = 'PRIMARY KEY'
WHERE t.table_schema = DATABASE()
  AND t.table_type   = 'BASE TABLE'
  AND c.constraint_name IS NULL;

-- 1. High-volume OLTP table: INT AUTO_INCREMENT PK
--    Low storage per row, sequential writes, fast range scans
CREATE TABLE page_views (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  page_slug  VARCHAR(200) NOT NULL,
  user_id    INT UNSIGNED,
  viewed_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_page_viewed (page_slug, viewed_at)
);

-- 2. Distributed system / microservice: ordered UUID PK
--    ID generated at application tier, no central sequence needed
CREATE TABLE audit_events (
  id          BINARY(16)   NOT NULL DEFAULT (UUID_TO_BIN(UUID(), 1)),
  service     VARCHAR(50)  NOT NULL,
  action      VARCHAR(100) NOT NULL,
  occurred_at DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id)
);

-- 3. Reference table: natural key is appropriate
--    Country codes are stable, short, and globally meaningful
CREATE TABLE countries (
  code       CHAR(2)      NOT NULL,  -- ISO 3166-1 alpha-2
  name       VARCHAR(100) NOT NULL,
  PRIMARY KEY (code)
);

-- 4. Junction table: composite PK, no surrogate needed
CREATE TABLE product_tags (
  product_id INT UNSIGNED NOT NULL,
  tag_id     INT UNSIGNED NOT NULL,
  tagged_at  DATETIME     DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (product_id, tag_id),
  INDEX idx_tag (tag_id)  -- secondary index for reverse lookup
);

-- Illustrate clustered index lookup cost
-- Secondary index on email -> stores (email, id) in the index leaf
-- Query: SELECT name FROM users WHERE email = 'alice@example.com'
-- Step 1: seek email index -> find id=42
-- Step 2: seek clustered index using id=42 -> find name

-- A covering index eliminates step 2 by including name in the secondary index
ALTER TABLE users ADD INDEX idx_email_name (email, name);
-- Now the query is answered entirely from the secondary index (no clustered lookup)
EXPLAIN SELECT name FROM users WHERE email = 'alice@example.com';
-- Extra: 'Using index'  (covering index -- step 2 eliminated)

-- InnoDB stores the AUTO_INCREMENT counter in memory (not on disk in older versions)
-- After a server restart, the counter is recovered from MAX(id)
-- This means gaps can appear after rollbacks or inserts that fail

-- Rolled-back INSERT still consumes the AUTO_INCREMENT value
START TRANSACTION;
INSERT INTO orders (customer_id, total) VALUES (1, 100.00);
SELECT LAST_INSERT_ID();  -- e.g. returns 1001
ROLLBACK;
-- Next successful INSERT will get 1002, not 1001 (1001 is permanently "lost")

-- This is by design: InnoDB cannot reclaim rolled-back IDs
-- Application code must NEVER assume IDs are consecutive

-- MySQL 8.0 change: AUTO_INCREMENT counter is now persisted to the redo log
-- so the counter survives a server crash/restart correctly
SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode';
-- 2 = interleaved (best performance, default in 8.0 with row-based replication)

-- Inspect the primary key of any table
SHOW INDEX FROM orders WHERE Key_name = 'PRIMARY';

-- Get PK column names from information_schema
SELECT COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME   = 'orders'
  AND CONSTRAINT_NAME = 'PRIMARY';

-- Show size consumed by PK vs total table size
SELECT
  table_name,
  ROUND(data_length  / 1024 / 1024, 2) AS data_mb,
  ROUND(index_length / 1024 / 1024, 2) AS index_mb,
  ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY total_mb DESC;

-- INT UNSIGNED: 0 to 4,294,967,295 (~4.3 billion)
-- Suitable for tables expected to stay under 2 billion rows

-- BIGINT UNSIGNED: 0 to 18,446,744,073,709,551,615 (~18 quintillion)
-- Safe for virtually any table

-- MEDIUMINT UNSIGNED: 0 to 16,777,215 (~16 million)
-- Good for small lookup/reference tables to save storage

-- Check current max id and headroom
SELECT MAX(id) AS current_max,
  4294967295 - MAX(id) AS int_headroom,
  CONCAT(ROUND((MAX(id) / 4294967295) * 100, 2), '%') AS int_pct_used
FROM orders;

-- If approaching INT_MAX, migrate to BIGINT before running out
-- Online migration (no downtime):
ALTER TABLE orders MODIFY COLUMN id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;
-- In MySQL 8.0+ this uses an instant ALTER for many column type changes