SQL - SQL Constraints

In SQL (Structured Query Language), constraints are rules applied to columns in a table to enforce data integrity, accuracy, and consistency. They ensure that only valid data is entered into a database.

Types of SQL Constraints

1. NOT NULL

   Ensures that a column cannot have a NULL (empty) value.

   CREATE TABLE users (
     id INT,
     name VARCHAR(100) NOT NULL
   );
   

2. UNIQUE

   Ensures all values in a column are unique (no duplicates).

   CREATE TABLE employees (
     email VARCHAR(100) UNIQUE
   );
   

3. PRIMARY KEY

   A combination of NOT NULL and UNIQUE. Uniquely identifies each row in a table.

   CREATE TABLE students (
     student_id INT PRIMARY KEY,
     name VARCHAR(100)
   );
   

4. FOREIGN KEY

   Ensures referential integrity between two tables. It links a column in one table to the PRIMARY KEY of another.

   CREATE TABLE orders (
     order_id INT PRIMARY KEY,
     customer_id INT,
     FOREIGN KEY (customer_id) REFERENCES customers(id)
   );
   

5. CHECK

   Ensures that all values in a column meet a specific condition.

   CREATE TABLE accounts (
     balance DECIMAL(10,2),
     CHECK (balance >= 0)
   );
   

6. DEFAULT

   Sets a default value for a column when no value is specified.

   CREATE TABLE products (
     id INT,
     status VARCHAR(10) DEFAULT 'active'
   );
   

Why Use Constraints?

• Prevent invalid data (e.g., negative age, empty name).

• Maintain relationships between tables (using foreign keys).

• Ensure uniqueness (like for usernames or emails).

• Set sensible defaults to reduce missing data.