Oracle SQL - Constraints

SQL Constraints

SQL Constraints are rules used to limit the type of data that can go into a table, to maintain the accuracy and integrity of the data inside table.

Syntax

CREATE TABLE table_name (
    column1 datatype constraint, 
    column2 datatype constraint, 
    column3 datatype constraint
    ....
)

List of Commonly Used SQL Constraints

• NOT NULL : the data of the column must have a value (can not have NULL value)
• UNIQUE : ensures that all values in a column are different
• PRIMARY KEY : ensures to uniquely identify each row in table (UNIQUE + NOT NULL)
• FOREIGN KEY : key used to link two tables together, commonly dependant on Primary Key
• CHECK : ensures that all values in a column satisfies a specific condition (boolean expression whether the condition is satisfied)
• DEFAULT : when no value is assigned to a column, assigns specific default value
• INDEX : used to quickly retrieve data from the table

Example

create a table students containing student's basic information

CREATE TABLE students (
    student_id NUMBER PRIMARY KEY,
    student_firstName VARCHAR2(50) NOT NULL,
    student_lastName VARCHAR2(50) NOT NULL,
    student_major VARCHAR2(50) NOT NULL,
);