HTML
JAVASCRIPT
CSS
PYTHON
JAVA
SQL

SQL Constraints

SQL Constraints: Ensuring Data Integrity

SQL constraints enforce rules on table data to maintain accuracy and reliability. They help prevent invalid or inconsistent entries, ensuring that the database remains structured and trustworthy.

Types of SQL Constraints

  • NOT NULL – Prevents null values in a column.
  • UNIQUE – Ensures all values in a column are distinct.
  • PRIMARY KEY – Uniquely identifies each record in a table.
  • FOREIGN KEY – Establishes a relationship between tables.
  • CHECK – Validates data based on specific conditions.
  • DEFAULT – Assigns a default value if no value is provided.
  • INDEX – Enhances data retrieval speed (not a strict constraint but used for optimization).

1. NOT NULL Constrainte

The NOT NULL constraint ensures that a column must always have a value.

Syntax:

CREATE TABLE students (
id INT NOT NULL,
name VARCHAR(50) NOT NULL
);

Example:

INSERT INTO students (id, name) VALUES (1, 'Alice');  
INSERT INTO students (id) VALUES (2);

2. UNIQUE Constraint

Ensures that no duplicate values exist in a column.

Syntax:

CREATE TABLE employees (
emp_id INT UNIQUE,
email VARCHAR(100) UNIQUE
);

Example:

INSERT INTO employees (emp_id, email) VALUES (101, 'alice@example.com'); 
INSERT INTO employees (emp_id, email) VALUES (101, 'bob@example.com');

3. PRIMARY KEY Constraint

A PRIMARY KEY is a unique identifier for a record. It combines NOT NULL and UNIQUE properties.

Syntax:

CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_name VARCHAR(50) NOT NULL
);

Example:

INSERT INTO orders (order_id, customer_name) VALUES (1, 'John Doe'); 
INSERT INTO orders (order_id, customer_name) VALUES (1, 'Jane Doe');

4. FOREIGN KEY Constraint

A FOREIGN KEY links a column to another table's PRIMARY KEY, ensuring referential integrity.

Syntax:

CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(50)
);

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

Example:

INSERT INTO customers (customer_id, name) VALUES (1, 'Alice');
INSERT INTO orders (order_id, customer_id) VALUES (1001, 1); 
INSERT INTO orders (order_id, customer_id) VALUES (1002, 5);

5. CHECK Constraint

Validates column values against specific conditions.

Syntax:

CREATE TABLE products (
product_id INT PRIMARY KEY,
price DECIMAL(10,2) CHECK (price > 0)
);

Example:

INSERT INTO products (product_id, price) VALUES (1, 50.99);
INSERT INTO products (product_id, price) VALUES (2, -10);

6. DEFAULT Constraint

Automatically assigns a default value when none is provided.

Syntax:

CREATE TABLE employees (
emp_id INT PRIMARY KEY,
department VARCHAR(50) DEFAULT 'General'
);

Example:

INSERT INTO employees (emp_id) VALUES (101);
INSERT INTO employees (emp_id, department) VALUES (102, 'IT');

7. INDEX (Optimization Constraint)

Not a constraint in the strict sense but improves query performance.

Syntax:

CREATE INDEX idx_email ON employees(email);

Example:

SELECT * FROM employees WHERE email = 'alice@example.com';

Conclusion

SQL constraints play a critical role in data accuracy, consistency, and efficiency. They help maintain data quality while reducing errors and redundancy. By implementing constraints wisely, databases remain structured, secure, and optimized for performance.

Previous Next