---

Table of Contents

1. Introduction
2. Why Use JSON in SQL Databases?
3. Storing JSON Data in Tables
4. JSON Data Types and Columns
5. Basic JSON Syntax
6. Inserting JSON Values
7. Retrieving JSON Data
8. Accessing JSON Fields (->, ->>, JSON_EXTRACT)
9. Modifying JSON Fields
10. Querying with JSON Conditions
11. Validating JSON with IS JSON, JSON_VALID()
12. Using JSON_OBJECT, JSON_ARRAY
13. JSON Aggregation Functions
14. Searching Inside JSON Arrays
15. Indexing JSON Columns
16. Real-World Example: Storing User Profiles
17. Real-World Example: Product Attributes
18. Limitations and Considerations
19. Best Practices for JSON in SQL
20. Summary and What’s Next

---

1. Introduction

JSON (JavaScript Object Notation) has become a popular format for storing semi-structured data. Many modern SQL databases now support native JSON operations, allowing developers to store, query, and manipulate JSON just like regular tabular data.

---

2. Why Use JSON in SQL Databases?

• Store flexible, dynamic schemas
• Embed nested or hierarchical data structures
• Exchange data easily with APIs and applications
• Avoid joining too many lookup tables for small, infrequent fields

---

3. Storing JSON Data in Tables

You can store JSON in:

• TEXT or VARCHAR columns (basic usage)
• JSON or JSONB (native types in PostgreSQL, MySQL 5.7+)

Example:

sqlCopyEditCREATE TABLE users (
  id INT PRIMARY KEY,
  profile JSON
);

---

4. JSON Data Types and Columns

Database	Native JSON Support	Notes
MySQL	JSON	Validates syntax, compresses storage
PostgreSQL	JSON, JSONB	JSONB is binary, indexable, faster
SQL Server	NVARCHAR + JSON functions	No native type, but full support

---

5. Basic JSON Syntax

jsonCopyEdit{
  "name": "Alice",
  "age": 30,
  "hobbies": ["reading", "hiking"],
  "address": { "city": "Delhi", "zip": "110001" }
}

---

6. Inserting JSON Values

sqlCopyEditINSERT INTO users (id, profile)
VALUES (1, '{
  "name": "Alice",
  "age": 30,
  "hobbies": ["reading", "hiking"]
}');

Ensure valid JSON format and escape special characters if needed.

---

7. Retrieving JSON Data

sqlCopyEditSELECT profile FROM users WHERE id = 1;

Returns JSON string. To extract values, use access operators or functions.

---

8. Accessing JSON Fields

MySQL:

sqlCopyEdit-- Get field
SELECT profile->'$.name' FROM users;

-- Get scalar (text)
SELECT profile->>'$.name' FROM users;

PostgreSQL:

sqlCopyEdit-- Get JSON object
SELECT profile->'name' FROM users;

-- Get text
SELECT profile->>'name' FROM users;

---

9. Modifying JSON Fields

MySQL:

sqlCopyEditUPDATE users
SET profile = JSON_SET(profile, '$.age', 31)
WHERE id = 1;

PostgreSQL:

sqlCopyEditUPDATE users
SET profile = jsonb_set(profile, '{age}', '31', true)
WHERE id = 1;

---

10. Querying with JSON Conditions

MySQL:

sqlCopyEditSELECT * FROM users
WHERE JSON_EXTRACT(profile, '$.age') > 25;

PostgreSQL:

sqlCopyEditSELECT * FROM users
WHERE profile->>'age'::int > 25;

---

11. Validating JSON with IS JSON, JSON_VALID()

MySQL:

sqlCopyEditSELECT JSON_VALID('{"a":1}') AS is_valid; -- returns 1

You can also enforce JSON validity at the schema level with CHECK (JSON_VALID(column)).

---

12. Using JSON_OBJECT, JSON_ARRAY

MySQL:

sqlCopyEditSELECT JSON_OBJECT('name', 'Alice', 'age', 30);
SELECT JSON_ARRAY('reading', 'hiking');

PostgreSQL:

sqlCopyEditSELECT jsonb_build_object('name', 'Alice', 'age', 30);
SELECT jsonb_build_array('reading', 'hiking');

---

13. JSON Aggregation Functions

MySQL:

sqlCopyEditSELECT JSON_ARRAYAGG(name) FROM users;

PostgreSQL:

sqlCopyEditSELECT json_agg(name) FROM users;

Useful for returning arrays of values in a single JSON result.

---

14. Searching Inside JSON Arrays

MySQL:

sqlCopyEditSELECT * FROM users
WHERE JSON_CONTAINS(profile->'$.hobbies', '["reading"]');

PostgreSQL:

sqlCopyEditSELECT * FROM users
WHERE 'reading' = ANY (jsonb_array_elements_text(profile->'hobbies'));

---

15. Indexing JSON Columns

• MySQL: Functional indexes on extracted fields

sqlCopyEditCREATE INDEX idx_name ON users ((profile->>'$.name'));

• PostgreSQL: GIN indexes on JSONB for containment searches

sqlCopyEditCREATE INDEX idx_profile ON users USING GIN (profile jsonb_path_ops);

---

16. Real-World Example: Storing User Profiles

sqlCopyEdit{
  "first_name": "John",
  "last_name": "Doe",
  "settings": {
    "theme": "dark",
    "notifications": true
  }
}

This eliminates the need for multiple settings columns in the table.

---

17. Real-World Example: Product Attributes

jsonCopyEdit{
  "color": "red",
  "size": "L",
  "features": ["waterproof", "lightweight"]
}

Used in e-commerce systems for products with dynamic attributes.

---

18. Limitations and Considerations

• JSON data is not normalized — harder to join
• Slower to query than structured columns unless indexed
• Constraints are harder to enforce (e.g., mandatory fields)
• Can lead to schema drift if not managed carefully

---

19. Best Practices for JSON in SQL

• Use JSON for flexible or semi-structured data
• Always validate JSON inputs
• Combine JSON with relational columns when appropriate
• Create indexes on frequently queried fields
• Avoid overloading logic inside JSON — keep it readable and minimal

---

20. Summary and What’s Next

JSON support in SQL lets you handle semi-structured, flexible data inside a relational schema. With native functions and indexing, it’s now easier than ever to integrate JSON workflows into traditional SQL applications.

---

Table of Contents

1. Introduction
2. Why Error Handling Matters in SQL
3. Types of Errors in SQL
4. Syntax Errors vs Runtime Errors
5. Understanding Transaction Errors
6. Rollbacks for Safe Error Recovery
7. Error Handling in SQL Scripts
8. Handling Errors in Stored Procedures
9. Using DECLARE HANDLER in MySQL
10. Handling Errors in PostgreSQL (EXCEPTION)
11. SQL Server’s TRY...CATCH Syntax
12. Identifying Errors with SQLSTATE and Error Codes
13. Logging Errors for Auditing and Debugging
14. Real-World Use Case: Bank Transfer with Error Handling
15. Preventing Errors Through Constraints
16. Retrying, Skipping, or Continuing After Errors
17. Common Error Patterns and Troubleshooting
18. Best Practices for Error Handling
19. Database-Specific Features for Error Management
20. Summary and What’s Next

---

1. Introduction

Error handling is a critical aspect of SQL programming. Whether you’re writing a script, stored procedure, or a high-throughput application backend, managing exceptions and transaction failures properly can protect your data and improve system reliability.

---

2. Why Error Handling Matters in SQL

• Maintains data consistency and integrity
• Prevents partial updates or corrupt states
• Helps diagnose and log application-level issues
• Enables clean recovery or user-friendly messages
• Supports robust and maintainable code

---

3. Types of Errors in SQL

Error Type	Examples
Syntax errors	Misspelled SQL keywords, missing commas
Constraint errors	Violations of NOT NULL, UNIQUE, or FK
Runtime errors	Division by zero, invalid data type operations
Deadlocks	Competing transactions causing locks
Connection errors	Timeouts, disconnections, missing privileges

---

4. Syntax Errors vs Runtime Errors

• Syntax errors are caught during query parsing.
• Runtime errors occur during execution and can be caught or handled in stored routines.

Example:

sqlCopyEditSELECT 1 / 0; -- Runtime error: division by zero

---

5. Understanding Transaction Errors

Many SQL statements are executed inside transactions. If one fails, the entire transaction must be rolled back to maintain data integrity.

---

6. Rollbacks for Safe Error Recovery

sqlCopyEditSTART TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Error occurs here
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

ROLLBACK; -- Ensures nothing is committed

---

7. Error Handling in SQL Scripts

Basic SQL scripts usually stop execution when an error is encountered. To control this, use database-specific constructs like:

• DECLARE HANDLER in MySQL
• BEGIN ... EXCEPTION in PostgreSQL
• TRY...CATCH in SQL Server

---

8. Handling Errors in Stored Procedures

MySQL:

sqlCopyEditDECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
  ROLLBACK;
  INSERT INTO error_log (message) VALUES ('Something failed');
END;

PostgreSQL:

sqlCopyEditBEGIN
  -- statements
EXCEPTION
  WHEN OTHERS THEN
    RAISE WARNING 'Error: %', SQLERRM;
END;

---

9. Using DECLARE HANDLER in MySQL

sqlCopyEditDECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
  SET @has_error = TRUE;
END;

Handler types:

• CONTINUE: continue execution after error
• EXIT: exit the block
• UNDO: rarely supported

---

10. Handling Errors in PostgreSQL (EXCEPTION)

sqlCopyEditDO $$
BEGIN
  -- Attempt risky logic
EXCEPTION
  WHEN division_by_zero THEN
    RAISE NOTICE 'Divide by zero error';
  WHEN OTHERS THEN
    RAISE WARNING 'Unhandled error: %', SQLERRM;
END $$;

PostgreSQL provides GET STACKED DIAGNOSTICS for deeper debugging.

---

11. SQL Server’s TRY...CATCH Syntax

sqlCopyEditBEGIN TRY
  -- risky SQL
END TRY
BEGIN CATCH
  PRINT ERROR_MESSAGE();
  ROLLBACK TRANSACTION;
END CATCH;

Also access: ERROR_NUMBER(), ERROR_LINE(), ERROR_SEVERITY()

---

12. Identifying Errors with SQLSTATE and Error Codes

Code	Meaning
22012	Division by zero
23000	Constraint violation
40001	Serialization failure (deadlock)
42000	Syntax error or access violation

Useful for creating custom error logic based on type.

---

13. Logging Errors for Auditing and Debugging

sqlCopyEditCREATE TABLE error_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  error_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  message TEXT
);

-- Log error
INSERT INTO error_log (message) VALUES ('Update failed at step 3');

---

14. Real-World Use Case: Bank Transfer with Error Handling

sqlCopyEditBEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    INSERT INTO error_log (message) VALUES ('Transfer failed');
  END;

  START TRANSACTION;

  UPDATE accounts SET balance = balance - 1000 WHERE id = 101;
  UPDATE accounts SET balance = balance + 1000 WHERE id = 202;

  COMMIT;
END;

---

15. Preventing Errors Through Constraints

Define constraints in schema:

• CHECK (quantity >= 0)
• NOT NULL to enforce required fields
• UNIQUE to prevent duplicates
• FOREIGN KEY to enforce relational integrity

---

16. Retrying, Skipping, or Continuing After Errors

Use CONTINUE HANDLER or write loop logic to skip faulty records during ETL.

sqlCopyEditDECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SET @skipped_rows = @skipped_rows + 1;

---

17. Common Error Patterns and Troubleshooting

Issue	Solution
Uncaught exceptions	Use TRY...CATCH or HANDLER
Silent failures	Always log or raise feedback
Rollback missed	Always rollback in failure handlers
Ambiguous error cause	Use SQLSTATE, ERROR_MESSAGE() etc.

---

18. Best Practices for Error Handling

• Always wrap complex DML in transactions
• Log meaningful context with every failure
• Use specific error handling when possible
• Test edge cases (nulls, duplicates, failures)
• Avoid silent failure paths unless explicitly desired

---

19. Database-Specific Features for Error Management

DBMS	Mechanism
MySQL	DECLARE HANDLER, error codes
PostgreSQL	EXCEPTION, SQLERRM, diagnostics
SQL Server	TRY...CATCH, XACT_ABORT, severity levels
Oracle	EXCEPTION WHEN, SQLCODE, SQLERRM

---

20. Summary and What’s Next

Error handling in SQL empowers you to build robust, resilient, and fault-tolerant systems. Whether using handlers, exception blocks, or structured logs, it’s essential for modern database operations.

---

Table of Contents

1. Introduction
2. What Are Stored Procedures and Functions?
3. Benefits of Using Stored Routines
4. Stored Procedures vs Functions: Key Differences
5. Syntax for Creating Stored Procedures
6. Executing a Stored Procedure
7. Parameters in Stored Procedures (IN, OUT, INOUT)
8. Control Flow in Stored Procedures
9. Creating and Using Functions
10. Input Parameters in Functions
11. Return Statements and Data Types in Functions
12. Using Stored Functions in SQL Queries
13. Real-World Use Case: Generating Reports
14. Real-World Use Case: Reusable Business Rules
15. Error Handling in Stored Procedures
16. Security and Permissions
17. Performance Considerations
18. Modifying and Dropping Stored Routines
19. Best Practices for Writing Stored Routines
20. Summary and What’s Next

---

1. Introduction

In SQL, stored procedures and functions are blocks of SQL code that are stored in the database and executed when needed. They allow you to encapsulate complex operations into reusable and manageable routines.

---

2. What Are Stored Procedures and Functions?

• A Stored Procedure is a set of SQL statements that performs a task. It may or may not return a value.
• A Stored Function is similar but must return a value and is often used inside expressions.

Both are often referred to collectively as stored routines.

---

3. Benefits of Using Stored Routines

• Reusability: Write once, use many times
• Maintainability: Centralize business logic
• Security: Hide implementation details
• Performance: Reduce client-server communication
• Modularity: Break down large tasks into manageable pieces

---

4. Stored Procedures vs Functions: Key Differences

Feature	Stored Procedure	Stored Function
Returns value	Optional (OUT/INOUT)	Mandatory (RETURN)
Use in SQL	Not in expressions	Can be used in SELECT, WHERE
Called by	CALL proc_name(...)	SELECT func_name(...)
Transaction control	Allowed	Not allowed in many RDBMSs

---

5. Syntax for Creating Stored Procedures

MySQL:

sqlCopyEditDELIMITER //

CREATE PROCEDURE GetEmployeeById(IN emp_id INT)
BEGIN
  SELECT * FROM employees WHERE id = emp_id;
END //

DELIMITER ;

PostgreSQL:

sqlCopyEditCREATE OR REPLACE PROCEDURE GetEmployeeById(IN emp_id INT)
LANGUAGE plpgsql
AS $$
BEGIN
  SELECT * FROM employees WHERE id = emp_id;
END;
$$;

---

6. Executing a Stored Procedure

MySQL:

sqlCopyEditCALL GetEmployeeById(3);

PostgreSQL:

sqlCopyEditCALL GetEmployeeById(3);

---

7. Parameters in Stored Procedures (IN, OUT, INOUT)

• IN: Input only
• OUT: Output only
• INOUT: Both input and output

Example:

sqlCopyEditCREATE PROCEDURE UpdateSalary(IN emp_id INT, IN raise DECIMAL(10,2), OUT new_salary DECIMAL(10,2))
BEGIN
  UPDATE employees SET salary = salary + raise WHERE id = emp_id;
  SELECT salary INTO new_salary FROM employees WHERE id = emp_id;
END;

---

8. Control Flow in Stored Procedures

You can use IF, CASE, LOOP, WHILE, and REPEAT:

sqlCopyEditIF status = 'active' THEN
  UPDATE users SET last_login = NOW() WHERE id = user_id;
END IF;

Also includes exception handling via DECLARE ... HANDLER.

---

9. Creating and Using Functions

sqlCopyEditCREATE FUNCTION GetTotalOrders(cust_id INT)
RETURNS INT
DETERMINISTIC
BEGIN
  DECLARE total INT;
  SELECT COUNT(*) INTO total FROM orders WHERE customer_id = cust_id;
  RETURN total;
END;

---

10. Input Parameters in Functions

Functions can take multiple IN parameters:

sqlCopyEditCREATE FUNCTION AddNumbers(a INT, b INT)
RETURNS INT
RETURN a + b;

---

11. Return Statements and Data Types in Functions

• Use RETURNS type to define return type
• Must include a RETURN statement
• Only one value can be returned directly

sqlCopyEditRETURN CONCAT(first_name, ' ', last_name);

---

12. Using Stored Functions in SQL Queries

sqlCopyEditSELECT name, GetTotalOrders(id) AS total_orders
FROM customers;

Stored functions can be used in:

• SELECT
• WHERE
• ORDER BY
• GROUP BY

---

13. Real-World Use Case: Generating Reports

Procedure:

sqlCopyEditCREATE PROCEDURE MonthlySalesReport(IN report_month DATE)
BEGIN
  SELECT product_id, SUM(quantity), SUM(total)
  FROM sales
  WHERE MONTH(sale_date) = MONTH(report_month)
  GROUP BY product_id;
END;

Used for dashboards or automated report generation.

---

14. Real-World Use Case: Reusable Business Rules

Function:

sqlCopyEditCREATE FUNCTION IsEligibleForDiscount(purchase_total DECIMAL(10,2))
RETURNS BOOLEAN
RETURN purchase_total > 500;

Used inside SELECT, WHERE, or other procedures.

---

15. Error Handling in Stored Procedures

Use DECLARE HANDLER to catch and handle errors:

sqlCopyEditDECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
  -- Log error or set default values
END;

This improves stability in mission-critical operations.

---

16. Security and Permissions

• Routines can be granted EXECUTE privileges separately
• Users can call a procedure without knowing the internal query
• You can restrict DML operations inside routines with role control

---

17. Performance Considerations

• Reduces round-trips between client and server
• Use compiled execution plans
• Avoid excessive nesting or recursion
• Reuse prepared logic for heavy tasks

---

18. Modifying and Dropping Stored Routines

Modify:

sqlCopyEditDROP PROCEDURE IF EXISTS UpdateSalary;
CREATE PROCEDURE UpdateSalary(...) ...

Drop:

sqlCopyEditDROP PROCEDURE procedure_name;
DROP FUNCTION function_name;

Use SHOW PROCEDURE STATUS or SHOW CREATE FUNCTION to inspect definitions.

---

19. Best Practices for Writing Stored Routines

• Use clear, consistent naming (sp_, fn_, etc.)
• Comment thoroughly for maintainability
• Keep logic modular and focused
• Handle exceptions explicitly
• Separate input validation from core logic

---

20. Summary and What’s Next

Stored procedures and functions are vital for modular, secure, and high-performance SQL development. They help standardize business logic, automate tasks, and improve performance through reusable logic blocks.

---

Table of Contents

1. Introduction
2. What Is a Trigger in SQL?
3. Why Use Triggers?
4. Types of Triggers
5. Syntax of a Trigger
6. BEFORE vs AFTER Triggers
7. INSERT, UPDATE, and DELETE Triggers
8. Trigger Events: Row-Level vs Statement-Level
9. Creating a Simple Trigger
10. Using NEW and OLD Pseudorecords
11. Real-World Example: Logging Table Changes
12. Real-World Example: Enforcing Business Logic
13. Preventing Invalid Updates
14. Using Triggers for Auditing
15. Nested and Recursive Triggers
16. Managing Trigger Order and Dependencies
17. Performance Considerations
18. Best Practices for Triggers
19. Viewing, Modifying, and Dropping Triggers
20. Summary and What’s Next

---

1. Introduction

SQL triggers allow developers to automate actions in response to events on a table. Whether you’re auditing changes, enforcing complex rules, or cascading updates, triggers help enforce consistent behavior directly in the database.

---

2. What Is a Trigger in SQL?

A trigger is a stored set of instructions that is automatically executed (or “triggered”) when a specific database event occurs, such as an INSERT, UPDATE, or DELETE.

---

3. Why Use Triggers?

• Automatically enforce business rules
• Log changes for auditing
• Validate or transform data
• Synchronize or replicate changes
• Prevent invalid operations
• Reduce redundant application code

---

4. Types of Triggers

Trigger Type	Description
BEFORE	Executes before the DML operation
AFTER	Executes after the DML operation
INSTEAD OF	Used to override actions on views (SQL Server, Oracle)

---

5. Syntax of a Trigger (General Form)

sqlCopyEditCREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
  -- trigger logic
END;

Varies slightly depending on DBMS.

---

6. BEFORE vs AFTER Triggers

• BEFORE: Ideal for validations or modifications to data before committing
• AFTER: Ideal for logging or notifications after the change is committed

---

7. INSERT, UPDATE, and DELETE Triggers

Event Type	Use Case Example
INSERT	Set timestamps, enforce uniqueness
UPDATE	Track old vs new values, prevent changes
DELETE	Archive records, log deletions

---

8. Trigger Events: Row-Level vs Statement-Level

• Row-Level Triggers: Executes once per row affected
• Statement-Level Triggers: Executes once per statement regardless of row count

PostgreSQL supports both via FOR EACH ROW and FOR EACH STATEMENT.

---

9. Creating a Simple Trigger

Example: Add timestamp on new row (MySQL):

sqlCopyEditCREATE TRIGGER set_created_at
BEFORE INSERT ON users
FOR EACH ROW
SET NEW.created_at = NOW();

This automatically sets created_at on insert.

---

10. Using NEW and OLD Pseudorecords

• NEW.column_name refers to the new value in INSERT/UPDATE
• OLD.column_name refers to the previous value in UPDATE/DELETE

sqlCopyEditCREATE TRIGGER audit_salary_change
AFTER UPDATE ON employees
FOR EACH ROW
INSERT INTO salary_audit (emp_id, old_salary, new_salary, changed_at)
VALUES (OLD.id, OLD.salary, NEW.salary, NOW());

---

11. Real-World Example: Logging Table Changes

sqlCopyEditCREATE TRIGGER log_user_activity
AFTER UPDATE ON users
FOR EACH ROW
INSERT INTO user_logs (user_id, action, timestamp)
VALUES (OLD.id, 'Profile Updated', NOW());

---

12. Real-World Example: Enforcing Business Logic

sqlCopyEditCREATE TRIGGER prevent_negative_stock
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
  IF NEW.stock < 0 THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Stock cannot be negative';
  END IF;
END;

---

13. Preventing Invalid Updates

sqlCopyEditCREATE TRIGGER block_salary_cut
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
  IF NEW.salary < OLD.salary THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Salary decrease not allowed';
  END IF;
END;

---

14. Using Triggers for Auditing

sqlCopyEditCREATE TRIGGER audit_delete_orders
AFTER DELETE ON orders
FOR EACH ROW
INSERT INTO deleted_orders (order_id, deleted_at)
VALUES (OLD.id, NOW());

Useful for tracking deletions or soft-deletion implementation.

---

15. Nested and Recursive Triggers

• Nested triggers: Trigger fired by another trigger
• Recursive triggers: A trigger firing itself directly or indirectly
  • May be disabled by default or require special config

Use SET NOCOUNT ON or control flags to avoid infinite loops.

---

16. Managing Trigger Order and Dependencies

Some databases (e.g., Oracle, SQL Server) allow you to define trigger execution order.

In general:

• Use modular triggers per use case
• Avoid creating large monolithic triggers

---

17. Performance Considerations

• Triggers add overhead — especially on bulk operations
• Use only where essential
• Avoid complex logic that introduces latency
• Beware of hidden side-effects (e.g., unexpected updates)

---

18. Best Practices for Triggers

• Name triggers clearly: e.g., before_insert_users, audit_delete_customers
• Keep them short and specific
• Use AFTER triggers for non-critical tasks
• Always test thoroughly before deploying
• Document trigger purpose and logic for future maintainers

---

19. Viewing, Modifying, and Dropping Triggers

View existing triggers:

sqlCopyEditSHOW TRIGGERS;              -- MySQL  
SELECT * FROM pg_trigger;   -- PostgreSQL  

Drop a trigger:

sqlCopyEditDROP TRIGGER trigger_name ON table_name; -- MySQL  
DROP TRIGGER trigger_name;               -- PostgreSQL  

---

20. Summary and What’s Next

Triggers allow automatic execution of logic in response to table events, making them ideal for auditing, validation, logging, and enforcing business rules. Use them wisely to ensure consistency without adding hidden complexity.

---

Table of Contents

1. Introduction
2. What Are Constraints in SQL?
3. Why Use Constraints?
4. NOT NULL Constraint
5. UNIQUE Constraint
6. CHECK Constraint
7. DEFAULT Constraint
8. Combining Multiple Constraints
9. Adding Constraints to Existing Tables
10. Dropping Constraints
11. Naming Constraints for Clarity
12. Column-Level vs Table-Level Constraints
13. Real-World Example: Enforcing Data Validity
14. Constraint Violation Errors
15. Performance Impact of Constraints
16. Difference Between UNIQUE and PRIMARY KEY
17. Best Practices for Using Constraints
18. Using Constraints in Insert Statements
19. Compatibility in Different SQL Dialects
20. Summary and What’s Next

---

1. Introduction

Constraints in SQL are rules applied to columns to enforce the correctness, validity, and integrity of the data stored in tables. They prevent bad data from entering your database and reduce the need for application-level validations.

---

2. What Are Constraints in SQL?

A constraint is a rule that restricts the values that can be stored in a column or set of columns. They are enforced by the database engine automatically.

---

3. Why Use Constraints?

• Prevent invalid or inconsistent data
• Maintain relational integrity
• Reduce bugs caused by bad input
• Ensure business logic enforcement at the database level
• Improve confidence in data quality

---

4. NOT NULL Constraint

The NOT NULL constraint ensures that a column must always have a value — it cannot be NULL.

sqlCopyEditCREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

If you try to insert a row without a name, it will throw an error.

---

5. UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column (or combination of columns) are distinct.

sqlCopyEditCREATE TABLE users (
  email VARCHAR(255) UNIQUE
);

You can also create multi-column uniqueness:

sqlCopyEditCONSTRAINT unique_user_email UNIQUE (user_id, email)

Unlike PRIMARY KEY, a table can have multiple UNIQUE constraints, and NULL values are usually allowed (depends on RDBMS).

---

6. CHECK Constraint

The CHECK constraint validates that data meets a specific condition.

sqlCopyEditCREATE TABLE employees (
  salary INT,
  CHECK (salary >= 0)
);

If someone tries to insert a negative salary, the operation fails.

You can also use complex conditions:

sqlCopyEditCHECK (status IN ('active', 'inactive', 'suspended'))

---

7. DEFAULT Constraint

The DEFAULT constraint assigns a default value if no value is specified during insertion.

sqlCopyEditCREATE TABLE users (
  is_active BOOLEAN DEFAULT TRUE
);

Now, if is_active is omitted, it will default to TRUE.

---

8. Combining Multiple Constraints

You can combine constraints on the same column:

sqlCopyEditCREATE TABLE accounts (
  account_id INT PRIMARY KEY,
  balance DECIMAL(10, 2) NOT NULL CHECK (balance >= 0) DEFAULT 0.00
);

This ensures no nulls, no negative balances, and initializes to zero.

---

9. Adding Constraints to Existing Tables

Use ALTER TABLE to add constraints later:

sqlCopyEditALTER TABLE users
ADD CONSTRAINT chk_age CHECK (age >= 18);

For NOT NULL:

sqlCopyEditALTER TABLE users
MODIFY age INT NOT NULL; -- MySQL

Or

sqlCopyEditALTER TABLE users
ALTER COLUMN age SET NOT NULL; -- PostgreSQL

---

10. Dropping Constraints

Constraints can be removed with ALTER TABLE:

sqlCopyEditALTER TABLE users
DROP CONSTRAINT chk_age; -- PostgreSQL, SQL Server

-- MySQL for UNIQUE
ALTER TABLE users
DROP INDEX email;

You must know the constraint name in some RDBMSs.

---

11. Naming Constraints for Clarity

Give custom names to constraints:

sqlCopyEditCONSTRAINT chk_positive_balance CHECK (balance >= 0)

Helps with debugging and managing schema migrations.

---

12. Column-Level vs Table-Level Constraints

• Column-level: Defined right after the column name
• Table-level: Defined after all columns

Example:

sqlCopyEdit-- Column-level
salary DECIMAL(10,2) CHECK (salary > 0)

-- Table-level
CHECK (salary > 0)

Use table-level for constraints involving multiple columns.

---

13. Real-World Example: Enforcing Data Validity

sqlCopyEditCREATE TABLE registrations (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  age INT CHECK (age >= 18),
  registered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Ensures:

• Email is unique and not null
• Age is at least 18
• Default registration timestamp is set

---

14. Constraint Violation Errors

If you violate a constraint, the database throws an error like:

• ERROR: null value in column "name" violates not-null constraint
• ERROR: duplicate key value violates unique constraint
• ERROR: new row for relation violates check constraint

Always validate data before insertion or use TRY-CATCH or equivalent in application logic.

---

15. Performance Impact of Constraints

Constraints have minimal read overhead, but:

• May slow inserts/updates due to validation
• Improve query optimization (e.g., using UNIQUE for index)
• Help databases infer cardinality during planning

---

16. Difference Between UNIQUE and PRIMARY KEY

Feature	PRIMARY KEY	UNIQUE
Null allowed?	No	Yes (usually, varies)
Count per table	One	Multiple
Index behavior	Automatically indexed	Automatically indexed

---

17. Best Practices for Using Constraints

• Always use NOT NULL unless nulls are required
• Define DEFAULT values for optional fields
• Use CHECK to enforce business rules early
• Name constraints clearly for future reference
• Document constraint logic as part of schema design

---

18. Using Constraints in Insert Statements

sqlCopyEditINSERT INTO users (name) VALUES ('Alice'); -- `is_active` defaults to TRUE

Will fail if:

• name is null and NOT NULL is enforced
• A duplicate email is inserted under UNIQUE

---

19. Compatibility in Different SQL Dialects

Constraint	PostgreSQL	MySQL	SQL Server	Oracle
NOT NULL	✅	✅	✅	✅
UNIQUE	✅	✅	✅	✅
CHECK	✅	⚠️ (enforced only in InnoDB 8+)	✅	✅
DEFAULT	✅	✅	✅	✅

---

20. Summary and What’s Next

SQL constraints like NOT NULL, UNIQUE, CHECK, and DEFAULT are essential tools for enforcing data integrity at the schema level. When used correctly, they reduce bugs, improve consistency, and push validation into the database layer.