SQL Constraints & Rules

In SQL, constraints are rules that are enforced on the data stored in a table to maintain the integrity and consistency of the database. Constraints define certain conditions that must be satisfied for the data to be valid and acceptable. They help in preventing invalid data from being inserted into the database and ensure that the data remains accurate and reliable.

Common Constraints in SQL

SQL supports various types of constraints that can be applied to columns in a table. Some of the common constraints include:

• PRIMARY KEY: Ensures that each row in a table is uniquely identified by a specific column or combination of columns. The primary key constraint enforces the uniqueness and non-nullability of the specified column(s).

CREATE TABLE students (
  -- id is the primary key making each student unique
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT
);

• FOREIGN KEY: Establishes a relationship between two tables by linking a column in one table to a column in another table. The foreign key constraint ensures referential integrity by enforcing that the values in the foreign key column must exist in the referenced table's primary key column.

CREATE TABLE products (
  -- id is the primary key of the products table
  id INT PRIMARY KEY,
  name VARCHAR(50),
  price DECIMAL(10, 2)
);
 
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  product_id INT,
  quantity INT,
  -- product_id is a foreign key referencing the id column in the products table
  FOREIGN KEY (product_id) 
  REFERENCES products(id) 
);

• UNIQUE: Ensures that the values in a column or a group of columns are unique across all rows in the table. The unique constraint enforces the uniqueness of the specified column(s) but allows null values.

CREATE TABLE employees (
  id INT PRIMARY KEY,
  -- email must be unique for each employee
  email VARCHAR(50) UNIQUE,
  name VARCHAR(50)
);

• CHECK: Defines a condition that must be satisfied by the values in a column. The check constraint enforces the specified condition on the column(s) to ensure that only valid data is inserted.

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  -- price must be greater than 0
  price DECIMAL(10, 2) CHECK (price > 0)
);

• NOT NULL: Ensures that a column does not contain null values. The not null constraint enforces the non-nullability of the specified column(s).

CREATE TABLE employees (
  id INT PRIMARY KEY,
  -- name cannot be null
  name VARCHAR(50) NOT NULL,
  department VARCHAR(50)
);

• DEFAULT: Specifies a default value for a column when no value is provided during insertion. The default constraint assigns the specified default value to the column if no value is explicitly provided.

CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  -- age defaults to 18 if not provided
  age INT DEFAULT 18
);

• INDEX: Improves the performance of queries by creating an index on one or more columns in a table. The index constraint allows faster retrieval of data based on the indexed columns.

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    price DECIMAL(10, 2),
    -- Create an index on the name column
    INDEX idx_name (name)
);
 
-- Insert a product into the table
INSERT INTO products (id, name, price) VALUES (1, 'Laptop', 999.99); 
 
-- Retrieve the product using the indexed column
SELECT * FROM products WHERE name = 'Laptop';