Learn how PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, CHECK, and DEFAULT values ensure data integrity, consistency, and optimize your Oracle database design.

Table Constraints

PRIMARY KEY

The PRIMARY KEY constraint uniquely identifies each row in a table. It ensures that all values in the column(s) are unique and not null, serving as the principal identifier for records.

Example 1: PRIMARY KEY on a Single Column

CREATE TABLE departments (
    dept_id NUMBER(4) PRIMARY KEY, -- Defines dept_id as the primary key
    dept_name VARCHAR2(30) NOT NULL
);

Explanation This code creates a departments table where dept_id is the PRIMARY KEY, ensuring each department has a unique and non-null identifier.

Example 2: PRIMARY KEY as a Table Constraint

CREATE TABLE employees (
    employee_id NUMBER(6),
    first_name VARCHAR2(20),
    last_name VARCHAR2(25) NOT NULL,
    CONSTRAINT pk_employee_id PRIMARY KEY (employee_id) -- Defines primary key at table level
);

Explanation This example defines the PRIMARY KEY for employee_id at the table level, providing a named constraint pk_employee_id.

Example 3: Composite PRIMARY KEY

CREATE TABLE order_items (
    order_id NUMBER(6),
    item_id NUMBER(4),
    quantity NUMBER(3),
    CONSTRAINT pk_order_item PRIMARY KEY (order_id, item_id) -- Composite primary key on two columns
);

Explanation Here, order_id and item_id together form a PRIMARY KEY, ensuring unique combinations for each order item.

Example 4: Adding PRIMARY KEY to Existing Table

ALTER TABLE products
ADD CONSTRAINT pk_product_id PRIMARY KEY (product_id); -- Adds primary key to an existing table

Explanation This SQL statement adds a PRIMARY KEY constraint named pk_product_id to the product_id column of the products table.

Example 5: Violating PRIMARY KEY Constraint

INSERT INTO departments (dept_id, dept_name) VALUES (10, 'IT');
INSERT INTO departments (dept_id, dept_name) VALUES (10, 'HR'); -- This will fail (duplicate key)

Explanation The second INSERT statement will raise an error because dept_id 10 already exists, violating the PRIMARY KEY constraint.

FOREIGN KEY

The FOREIGN KEY constraint establishes a link between two tables, ensuring referential integrity. It ensures that values in a column (or set of columns) in the referencing table match values in the primary key or unique key of the referenced table.

Example 1: Basic FOREIGN KEY Definition

CREATE TABLE employees (
    employee_id NUMBER(6) PRIMARY KEY,
    first_name VARCHAR2(20),
    dept_id NUMBER(4),
    CONSTRAINT fk_dept_id FOREIGN KEY (dept_id) REFERENCES departments (dept_id) -- Foreign key referencing departments
);

Explanation This code creates an employees table with dept_id as a FOREIGN KEY that references the dept_id (primary key) in the departments table.

Example 2: FOREIGN KEY with ON DELETE CASCADE

CREATE TABLE orders (
    order_id NUMBER(6) PRIMARY KEY,
    customer_id NUMBER(6),
    CONSTRAINT fk_customer_id FOREIGN KEY (customer_id) REFERENCES customers (customer_id) ON DELETE CASCADE -- Deletes child rows when parent is deleted
);

Explanation ON DELETE CASCADE ensures that if a row in the customers table is deleted, all corresponding orders in the orders table are also deleted.

Example 3: FOREIGN KEY with ON DELETE SET NULL

CREATE TABLE tasks (
    task_id NUMBER(6) PRIMARY KEY,
    task_name VARCHAR2(50),
    assigned_to_employee_id NUMBER(6),
    CONSTRAINT fk_assigned_emp FOREIGN KEY (assigned_to_employee_id) REFERENCES employees (employee_id) ON DELETE SET NULL -- Sets foreign key to NULL when parent is deleted
);

Explanation ON DELETE SET NULL means if an employee is deleted, their assigned_to_employee_id in the tasks table will be set to NULL.

Example 4: Adding FOREIGN KEY to Existing Table

ALTER TABLE products
ADD CONSTRAINT fk_category_id FOREIGN KEY (category_id) REFERENCES categories (category_id); -- Adds a foreign key to an existing table

Explanation This SQL statement adds a FOREIGN KEY constraint named fk_category_id to the products table, linking it to the categories table.

Example 5: Violating FOREIGN KEY Constraint

INSERT INTO employees (employee_id, first_name, dept_id)
VALUES (200, 'Alice', 999); -- This will fail (dept_id 999 does not exist in departments)

Explanation This INSERT statement will fail because dept_id 999 does not exist in the departments table, violating the FOREIGN KEY constraint.

NOT NULL

The NOT NULL constraint ensures that a column cannot contain NULL values. This is crucial for columns that must always have a value, such as names or identifiers.

Example 1: NOT NULL on a Single Column

CREATE TABLE suppliers (
    supplier_id NUMBER(4) PRIMARY KEY,
    supplier_name VARCHAR2(50) NOT NULL -- supplier_name cannot be NULL
);

Explanation This code defines supplier_name as NOT NULL, requiring a value for every supplier record.

Example 2: Adding NOT NULL to Existing Column

ALTER TABLE customers
MODIFY (customer_name VARCHAR2(50) NOT NULL); -- Adds NOT NULL to an existing column

Explanation This SQL statement modifies the customer_name column in the customers table to enforce NOT NULL.

Example 3: Violating NOT NULL Constraint

INSERT INTO suppliers (supplier_id, supplier_name) VALUES (1, 'Acme Corp');
INSERT INTO suppliers (supplier_id, supplier_name) VALUES (2, NULL); -- This will fail (supplier_name cannot be NULL)

Explanation The second INSERT statement will fail because it attempts to insert a NULL value into the supplier_name column, which is defined as NOT NULL.

Example 4: NOT NULL in Composite Key (Implicit)

CREATE TABLE registrations (
    student_id NUMBER,
    course_id NUMBER,
    CONSTRAINT pk_registration PRIMARY KEY (student_id, course_id) -- Both columns are implicitly NOT NULL
);

Explanation When columns are part of a PRIMARY KEY, they are implicitly NOT NULL by definition.

Example 5: Dropping NOT NULL Constraint

ALTER TABLE suppliers
MODIFY (supplier_name VARCHAR2(50) NULL); -- Removes the NOT NULL constraint

Explanation This statement removes the NOT NULL constraint from the supplier_name column, allowing it to accept NULL values.

UNIQUE

The UNIQUE constraint ensures that all values in a column or set of columns are unique across all rows in the table. It allows NULL values, unlike a PRIMARY KEY, but only one NULL value is permitted.

Example 1: UNIQUE on a Single Column

CREATE TABLE user_profiles (
    user_id NUMBER(6) PRIMARY KEY,
    username VARCHAR2(30) UNIQUE, -- username must be unique
    email VARCHAR2(100) NOT NULL
);

Explanation This code defines username as UNIQUE, ensuring no two users can have the same username.

Example 2: UNIQUE as a Table Constraint

CREATE TABLE product_codes (
    product_code VARCHAR2(10),
    description VARCHAR2(100),
    CONSTRAINT uk_product_code UNIQUE (product_code) -- Defines unique constraint at table level
);

Explanation This example defines a UNIQUE constraint named uk_product_code on the product_code column at the table level.

Example 3: Composite UNIQUE Key

CREATE TABLE course_offerings (
    course_id NUMBER,
    semester VARCHAR2(10),
    year NUMBER(4),
    CONSTRAINT uk_course_semester UNIQUE (course_id, semester, year) -- Combination of these three must be unique
);

Explanation The combination of course_id, semester, and year must be unique, preventing duplicate course offerings for the same period.

Example 4: Adding UNIQUE to Existing Column

ALTER TABLE user_profiles
ADD CONSTRAINT uk_email UNIQUE (email); -- Adds unique constraint to an existing column

Explanation This SQL statement adds a UNIQUE constraint named uk_email to the email column of the user_profiles table.

Example 5: Violating UNIQUE Constraint

INSERT INTO user_profiles (user_id, username, email) VALUES (1, 'jdoe', 'john.doe@example.com');
INSERT INTO user_profiles (user_id, username, email) VALUES (2, 'jdoe', 'jane.doe@example.com'); -- This will fail (duplicate username)

Explanation The second INSERT will fail because username 'jdoe' already exists, violating the UNIQUE constraint.

CHECK

The CHECK constraint specifies a condition that each row must satisfy. It ensures that the values in a column meet certain criteria, like being within a specific range or adhering to a pattern.

Example 1: CHECK for Positive Value

CREATE TABLE products (
    product_id NUMBER PRIMARY KEY,
    price NUMBER(8,2) CHECK (price > 0) -- Price must be greater than zero
);

Explanation This code defines a CHECK constraint on the price column, ensuring that only positive values can be inserted.

Example 2: CHECK with IN Operator

CREATE TABLE orders (
    order_id NUMBER PRIMARY KEY,
    order_status VARCHAR2(10) CHECK (order_status IN ('PENDING', 'SHIPPED', 'DELIVERED')) -- Status must be one of the specified values
);

Explanation This example uses a CHECK constraint with the IN operator to restrict order_status to a predefined set of values.

Example 3: CHECK for Date Range

CREATE TABLE events (
    event_id NUMBER PRIMARY KEY,
    event_date DATE,
    CHECK (event_date >= TO_DATE('2024-01-01', 'YYYY-MM-DD')) -- Event date must be after a certain date
);

Explanation This CHECK constraint ensures that event_date is always on or after January 1, 2024.

Example 4: Adding CHECK to Existing Table

ALTER TABLE employees
ADD CONSTRAINT chk_salary CHECK (salary >= 0); -- Adds check constraint to an existing column

Explanation This SQL statement adds a CHECK constraint named chk_salary to the salary column, requiring non-negative values.

Example 5: Violating CHECK Constraint

INSERT INTO products (product_id, price) VALUES (1, 10.99);
INSERT INTO products (product_id, price) VALUES (2, -5.00); -- This will fail (price must be > 0)

Explanation The second INSERT statement will fail because the price value is negative, violating the CHECK constraint.

DEFAULT Values

DEFAULT values automatically assign a predefined value to a column if no value is explicitly provided during an INSERT operation. This ensures consistency and simplifies data entry.

Example 1: DEFAULT Value for Numeric Column

CREATE TABLE inventory (
    product_id NUMBER PRIMARY KEY,
    product_name VARCHAR2(50),
    stock_quantity NUMBER DEFAULT 0 -- Defaults to 0 if not specified
);

Explanation This code sets the stock_quantity to 0 by DEFAULT if no value is provided during an insert.

Example 2: DEFAULT Value for Date Column

CREATE TABLE audit_trail (
    log_id NUMBER PRIMARY KEY,
    action_description VARCHAR2(200),
    log_timestamp TIMESTAMP DEFAULT SYSTIMESTAMP -- Defaults to current timestamp
);

Explanation The log_timestamp column will automatically be populated with the current system timestamp if not explicitly provided.

Example 3: DEFAULT Value for Character Column

CREATE TABLE tasks (
    task_id NUMBER PRIMARY KEY,
    task_description VARCHAR2(100),
    status VARCHAR2(20) DEFAULT 'NEW' -- Defaults to 'NEW'
);

Explanation If status is not specified during an INSERT, it will default to the string 'NEW'.

Example 4: Altering Column to Add DEFAULT

ALTER TABLE employees
MODIFY (hire_date DATE DEFAULT SYSDATE); -- Adds default value to an existing column

Explanation This SQL statement modifies the hire_date column in the employees table to automatically set SYSDATE as its default value.

Example 5: Inserting with and without DEFAULT

INSERT INTO inventory (product_id, product_name, stock_quantity) VALUES (1, 'Laptop', 50); -- Explicit value
INSERT INTO inventory (product_id, product_name) VALUES (2, 'Mouse'); -- stock_quantity will default to 0

Explanation The first INSERT provides an explicit stock_quantity. The second INSERT relies on the DEFAULT value for stock_quantity, which will be 0.