Lesson 20 Intermediate Level
20 of 30 lessons

Constraints in Oracle SQL

Learn about PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and CHECK constraints. Constraints ensure data integrity in your database.

What are Constraints?

Constraints are rules applied to table columns to enforce data integrity and accuracy.

Common Types of Constraints

  • NOT NULL: Ensures a column cannot have NULL values.
  • UNIQUE: Ensures all values in a column are unique.
  • PRIMARY KEY: Uniquely identifies each row; combination of NOT NULL and UNIQUE.
  • FOREIGN KEY: Ensures referential integrity by linking to primary key in another table.
  • CHECK: Ensures that values satisfy a specified condition.

Adding Constraints

CREATE TABLE employees (
  employee_id NUMBER PRIMARY KEY,
  first_name VARCHAR2(50) NOT NULL,
  salary NUMBER CHECK (salary >= 0),
  department_id NUMBER,
  CONSTRAINT fk_dept FOREIGN KEY (department_id) REFERENCES departments(dept_id)
);

Modifying Constraints

You can add or drop constraints on existing tables using ALTER TABLE statement. 

ALTER TABLE employees
ADD CONSTRAINT chk_salary CHECK (salary >= 1000);

ALTER TABLE employees
DROP CONSTRAINT chk_salary;

Practice Task