Table Constraints

Data integrity in MySQL is enforced through constraints, ensuring accuracy and consistency within tables.

Basic Constraints: NOT NULL, UNIQUE, and DEFAULT

These fundamental rules control nullability, uniqueness, and default assignments for column values.

CREATE TABLE users (
    user_id INT UNIQUE,
    username VARCHAR(50) NOT NULL,
    status VARCHAR(20) DEFAULT 'active'
);

Primary Key Constraint

A primary key uniquely identifies each record and inherently combines both the NOT NULL and UNIQUE constraints.

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    tier VARCHAR(10) DEFAULT 'standard'
);

Composite Primary Keys

When a primary key must span multiple columns, the CONSTRAINT keyword is used to define it.

CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    CONSTRAINT pk_order_product PRIMARY KEY (order_id, product_id)
);

Auto-Increment

The AUTO_INCREMENT attribute automatically generates a unique sequential integer for new rows, typically applied to primary keys.

CREATE TABLE transactions (
    txn_id INT PRIMARY KEY AUTO_INCREMENT,
    amount DECIMAL(10,2) NOT NULL,
    txn_date DATE
);

Foreign Key Constraints

Foreign keys enforce referential integrity by linking a column to the primary key of another table. The referenced values must exist, though the foreign key column itself can accept NULL.

CREATE TABLE payments (
    payment_id INT,
    customer_id INT,
    amount DECIMAL(10,2),
    CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Index Operations

Indexes function similarly to a book's table of contents, drastically improving data retrieval speeds by creating lookup keys. While they accelerate queries, excessive indexing can increase disk I/O overhead during write operations. MySQL supports various index types: normal, unique, fulltext, single-column, multi-column, and spatial.

Optimal Indexing Scenarios

Indexes should be applied to columns frequently used in WHERE or ON clauses, GROUP BY aggregations, join relationships (foreign keys), and columns requiring uniqueness. Conversely, avoid indexing columns with low cardinality or those seldom used in queries.

Creating Indexes During Table Creation

CREATE TABLE products (
    product_id INT,
    product_name VARCHAR(100),
    category VARCHAR(50),
    INDEX idx_product_id (product_id)
);

Viewing Indexes

You can inspect table creation syntax or analyze query execution plans to verify index usage.

SHOW CREATE TABLE products\G
EXPLAIN SELECT * FROM products WHERE product_id = 1\G

Adding Indexes to Existing Tables

Indexes can be added using CREATE INDEX or ALTER TABLE.

CREATE INDEX idx_name ON products(product_name);
ALTER TABLE products ADD INDEX idx_category (category);

Specialized and Multi-Column Indexes

Unique and fulltext indexes follow the same syntax, simply prefixing UNIQUE or FULLTEXT before INDEX. For multi-column indexes, include all desired columns. Note that multi-column indexes only activate if the query condition involves the leftmost prefix column.

ALTER TABLE products ADD INDEX idx_name_category (product_name, category);

Dropping Indexes

DROP INDEX idx_name ON products;

View Operations

Views act as virtual tables derived from one or more base tables, enhancing SQL reusability and data security by restricting access to specific columns (e.g., masking sensitive salary data).

Characteristics of Views

• Views do not store data physically; their contents are dynamically generated from base tables.
• A single view can combine columns from multiple underlying tables.
• Creating or dropping a view does not affect the base tables.
• Data modifications (INSERT, UPDATE, DELETE) performed on a view directly alter the underlying base table.
• Inserting or deleting data is generally prohibited for views that span multiple base tables.

Creating Views

CREATE VIEW active_products AS SELECT product_id, product_name FROM products WHERE category = 'active';

Dropping Views

DROP VIEW active_products;

Modifying Views

Views can be updated by dropping and recreating, using the CREATE OR REPLACE syntax, or via the ALTER command.

CREATE OR REPLACE VIEW active_products AS SELECT product_id, product_name, category FROM products WHERE category = 'active';
ALTER VIEW active_products AS SELECT product_id, product_name FROM products;

Trigger Implementation

Triggers are database objects tied to specific table events (INSERT, UPDATE, DELETE) that execute automatically when those events occur, acting as automated interceptors.

Single-Statement Triggers

This trigger automatically logs a record into the audit_log table before a new row is inserted into the products table.

CREATE TRIGGER trg_log_new_product BEFORE INSERT ON products FOR EACH ROW 
    INSERT INTO audit_log (action, record_id, log_time) VALUES ('INSERT', NEW.product_id, NOW());

Multi-Statement Triggers

For triggers requiring multiple operations, wrap the logic within a BEGIN ... END block. To prevent MySQL from interpreting semicolons as the end of the entire trigger definition, the DELIMITER command temporarily changes the statement terminator.

DELIMITER $$
CREATE TRIGGER trg_log_new_product_multi BEFORE INSERT ON products FOR EACH ROW 
    BEGIN
        INSERT INTO audit_log (action, record_id, log_time) VALUES ('INSERT', NEW.product_id, NOW());
        INSERT INTO notification_queue (message) VALUES (CONCAT('New product added: ', NEW.product_name));
    END
    $$
DELIMITER ;

Viewing and Dropping Triggers

Triggers can be listed globally or queried specifically from the information schema.

SHOW TRIGGERS;
SELECT * FROM information_schema.TRIGGERS WHERE TRIGGER_NAME = 'trg_log_new_product'\G
DROP TRIGGER trg_log_new_product;