Assignment 3 (constraints)

Data Constraints in SQL: Implementing Rules on Tables Effectively

When you're building a real database, just creating tables and inserting data isn't enough. You need rules — rules that prevent bad data from getting in the first place. That's exactly what data constraints are for. They act as a validation layer at the database level, so even if your application has a bug, the database itself won't accept invalid entries.

In this post we'll cover all the major SQL constraints — NULL, PRIMARY KEY, DEFAULT, FOREIGN KEY, CHECK, and UNIQUE — and then implement them in a real two-table example. If you haven't worked with tables yet, start with Assignment 1 (Introduction to SQL) first.

What Are Data Constraints?

Data constraints are rules applied to table columns that control what kind of data can be stored. Think of them as database-level validation. Instead of catching bad data in your app, you stop it from entering the database at all.

Constraints can be applied in two ways:

The choice between column-level and table-level is mostly about readability and whether your constraint involves more than one column.

Types of SQL Constraints

NOT NULL

By default, any column in SQL can hold a NULL value — meaning no data at all. Adding NOT NULL makes the column mandatory.

CREATE TABLE table_name (
    column_name datatype(size) NOT NULL
);

A missing value in a NOT NULL column will throw an error on insert.

PRIMARY KEY

The primary key uniquely identifies every row in a table. It enforces two rules automatically: values must be unique, and they cannot be NULL.

Column level:

CREATE TABLE table_name (
    column_name datatype(size) PRIMARY KEY
);

Table level (composite primary key across multiple columns):

CREATE TABLE table_name (
    col1 datatype(size),
    col2 datatype(size),
    PRIMARY KEY(col1, col2)
);

Use the table-level syntax when your primary key is made up of more than one column.

DEFAULT

Assigns a fallback value when no value is provided during insertion.

CREATE TABLE table_name (
    column_name datatype(size) DEFAULT value
);

Useful for columns like status, created_at, or salary where a sensible default always exists.

FOREIGN KEY

A foreign key links a column in one table to the primary key of another table. This is how you define relationships between tables and enforce referential integrity — you can't insert a value that doesn't exist in the referenced table.

Column level:

CREATE TABLE table_name (
    column_name datatype(size) REFERENCES other_table(col_name)
);

Table level:

CREATE TABLE table_name (
    column_name datatype(size),
    FOREIGN KEY(column_name) REFERENCES other_table(col_name)
);

Always create the referenced table first. If Employee references Department, Department must exist before you can create Employee.

CHECK

Validates that a value meets a specific condition before it's inserted. Good for enforcing business rules at the database level.

CREATE TABLE table_name (
    column_name datatype(size)
        CONSTRAINT constraint_name CHECK(condition)
);

Example: CHECK(Salary > 0) ensures no one can be inserted with a negative or zero salary.

UNIQUE

Ensures all values in a column are distinct — no two rows can have the same value. Unlike a primary key, a UNIQUE column can contain NULL values (though behavior varies across databases).

CREATE TABLE table_name (
    column_name datatype(size) UNIQUE
);

Email addresses and phone numbers are classic use cases for UNIQUE constraints.

Lab Assignment: Department and Employee Tables

This assignment uses two tables — Department and Employee. The employee table references the department table via a foreign key, so we create Department first.

Step 1: Create the Department Table

CREATE TABLE Department (
    Deptid NUMBER(4) PRIMARY KEY,
    Department_name VARCHAR2(30) NOT NULL
);

Step 2: Create the Employee Table

This table uses almost every constraint type — a good example of how they work together in a real schema:

CREATE TABLE Employee (
    Employee_id NUMBER(6) PRIMARY KEY,
    First_name  VARCHAR2(10) NOT NULL,
    Last_name   VARCHAR2(10) NOT NULL,
    Email       VARCHAR2(25) UNIQUE NOT NULL,
    Phone_no    VARCHAR2(20),
    Hiredate    DATE NOT NULL,
    Job_id      VARCHAR2(20) NOT NULL,
    Salary      NUMBER(8,2) DEFAULT 5000 CHECK (Salary > 0),
    Comm        NUMBER(5,2),
    Manager_id  NUMBER(6) REFERENCES Employee(Employee_id),
    Deptid      NUMBER(4) REFERENCES Department(Deptid)
);

A few things worth noting here:

• Salary has both a DEFAULT and a CHECK — two constraints on the same column is perfectly valid

• Manager_id references the same Employee table — this is a self-referencing foreign key, used to model reporting hierarchies

• Email has both UNIQUE and NOT NULL — it must exist and must be different for every employee

Step 3: Insert Data

INSERT INTO Department VALUES (10, 'Sales');
INSERT INTO Department VALUES (20, 'HR');

INSERT INTO Employee VALUES (101, 'Amit', 'Sharma', 'amit@gmail.com', '9876543210', '12-JAN-2024', 'DEV', 12000, NULL, NULL, 10);
INSERT INTO Employee VALUES (102, 'Ravi', 'Verma', 'ravi@gmail.com', '9898989898', '05-FEB-2024', 'HR', 8000, 5, 101, 20);

Step 4: Retrieve Data

SELECT * FROM Department;
SELECT * FROM Employee;

Department output:

Deptid | Department_name
10     | Sales
20     | HR

Employee output:

101 | Amit | Sharma | amit@gmail.com | 9876543210 | 12-JAN-24 | DEV | 12000 | NULL | NULL | 10
102 | Ravi | Verma  | ravi@gmail.com | 9898989898 | 05-FEB-24 | HR  | 8000  | 5    | 101  | 20

Step 5: View All Constraints on the Tables

Oracle stores constraint metadata in the user_constraints system view. This query shows all constraints applied to both tables:

SELECT constraint_name, constraint_type, table_name
FROM user_constraints
WHERE table_name IN ('EMPLOYEE', 'DEPARTMENT');

Expected output:

Constraint_Name  | Type | Table_Name
SYS_C0012345     | P    | DEPARTMENT   -- Primary Key
SYS_C0012346     | P    | EMPLOYEE     -- Primary Key
SYS_C0012347     | U    | EMPLOYEE     -- Email UNIQUE
SYS_C0012348     | C    | EMPLOYEE     -- Salary CHECK
SYS_C0012349     | R    | EMPLOYEE     -- FK to Department
SYS_C0012350     | R    | EMPLOYEE     -- FK to Employee (self)

Constraint types: P = Primary Key, U = Unique, C = Check, R = Referential (Foreign Key).

Also Explore These Topics

• SQL Introduction — create tables and insert data from scratch

• SQL DML — UPDATE, DELETE, WHERE, ORDER BY and pattern matching

• SQL Assignment 4 — next in the series

• SQL Quick Recap — fast revision of all core SQL concepts

• Triggers in SQL — automate actions when data changes

• Views in SQL — virtual tables built from queries

• DBMS Overview — understand the full database landscape before going deeper

For the complete reference on all constraint types and syntax, see the Oracle SQL constraints documentation. If you want to practice these interactively, W3Schools SQL editor is a quick no-setup option.