SQL Constraints

Constraints are rules enforced on data columns in a table. They are used to limit the type of data that can go into a table, ensuring the accuracy and reliability of the data. If there is any violation between the constraint and the data action, the action is aborted.

Constraints can be specified when the table is created (with CREATE TABLE) or after the table is created (with ALTER TABLE).

Common SQL Constraints

1. NOT NULL

Ensures that a column cannot have a NULL value.

DROP TABLE IF EXISTS Students;
CREATE TABLE Students (
    ID INTEGER PRIMARY KEY,
    Name VARCHAR(255) NOT NULL
);

-- Try to insert a student without a name (this will fail)
-- INSERT INTO Students (ID) VALUES (1);
SELECT 'Table Students Created' AS Status;

2. UNIQUE

Ensures that all values in a column are different.

DROP TABLE IF EXISTS Users;
CREATE TABLE Users (
    ID INTEGER PRIMARY KEY,
    Email VARCHAR(255) UNIQUE
);

SELECT 'Table Users Created' AS Status;

(Note: You can have many UNIQUE constraints per table, but only one PRIMARY KEY.)

3. PRIMARY KEY

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

DROP TABLE IF EXISTS Orders;
CREATE TABLE Orders (
    OrderID INTEGER PRIMARY KEY,
    OrderDate DATE
);

SELECT 'Table Orders Created' AS Status;

4. FOREIGN KEY

Prevents actions that would destroy links between tables. A FOREIGN KEY is a field in one table that refers to the PRIMARY KEY in another table.

-- First we need the parent table
DROP TABLE IF EXISTS Customers;
CREATE TABLE Customers (
    CustomerID INTEGER PRIMARY KEY,
    CustomerName VARCHAR(100)
);

DROP TABLE IF EXISTS Orders_FK;
CREATE TABLE Orders_FK (
    OrderID INTEGER PRIMARY KEY,
    CustomerID INTEGER,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

SELECT 'Tables Created' AS Status;

This ensures you cannot add an order for a non-existent customer.

5. CHECK

Ensures that the values in a column satisfy a specific condition.

DROP TABLE IF EXISTS Persons;
CREATE TABLE Persons (
    ID INTEGER PRIMARY KEY,
    Age INTEGER,
    CHECK (Age >= 18)
);

-- This will work
INSERT INTO Persons (ID, Age) VALUES (1, 25);
-- This would fail: INSERT INTO Persons (ID, Age) VALUES (2, 16);

SELECT * FROM Persons;

If you try to insert a person with Age 16, the database will reject it.

6. DEFAULT

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

DROP TABLE IF EXISTS Orders_Default;
CREATE TABLE Orders_Default (
    OrderID INTEGER PRIMARY KEY,
    OrderDate DATE DEFAULT (CURRENT_DATE)
);

INSERT INTO Orders_Default (OrderID) VALUES (1);
SELECT * FROM Orders_Default;

Hands-on Exercise

Task: Create a table named Inventory with the following columns and constraints:

• ItemID: Integer, Primary Key.
• ItemName: String, cannot be null.
• Quantity: Integer, default value of 0.
• Price: Decimal, must be greater than 0 (use a CHECK constraint).

Insert one record into the table with only ItemID, ItemName, and Price to see the default Quantity in action.

Your Query:

-- Write your query here

Expected Results:

If you insert a new order without a date, it will automatically use the current system date.