Constraints ( Primary Key, Foreign Key , Unique Key, Not Null and Check ) in Oracle SQL | Oracle SQL Tutorials -30

Hi,

I will explain Constraints ( Primary Key, Foreign Key , Unique Key, Not Null and Check ) in Oracle SQL in this post of Oracle SQL Tutorial series.

Read the previous post of this tutorial series before this.

https://ittutorial.org/alter-table-and-alter-table-add-or-drop-column-in-oracle-sql-oracle-sql-tutorials-29/

 

 

 

 

CONSTRAINTS

Constraints are the rules that are used to restrict the values in a database

• Constraints are used table-level rules to be applied forcibly.
• it prevents deletion of dependent data.
• it prevents incorrect data entry.
• They ensure that data is unique.
• They can be used at table or column level.

 

There are six types of integrity constraints in the Oracle database as follows.

1. Primary Key
2. Foreign Key
3. Unique Key
4. Index
5. Check
6. NOT Null

 

 

PRIMARY KEY CONSTRAINT

• A table can only have one primary key.
• Primary key column can not be Null.
• It also works as an index.

 

You can define primary key as follows.

CREATE TABLE HR.WORKERS
(
    EMPLOYEE_ID    NUMBER (6) CONSTRAINT E_PK PRIMARY KEY,
    FIRST_NAME     VARCHAR2 (20 BYTE),
    LAST_NAME      VARCHAR2 (25 BYTE),
    HIRE_DATE      DATE,
    JOB_ID         VARCHAR2 (10 BYTE),
    SALARY         NUMBER (8, 2)
)

• ‘Employee_id’ column is primary key, so can not get same values ( duplicated records ) and can not be null.
• Index is created on ‘Employee_id’ column automatically.

 

Primary key combines NOT NULL constraint and a unique constraint in a single declaration.

 

 

 

FOREIGN KEY CONSTRAINT

• It guarantees that, the data in one column of a table must match from another table. ( Parent – Child relationship )

• Let’s create a table with foreign key constraint.

 

CREATE TABLE HR.WORKERS
(
    EMPLOYEE_ID    NUMBER (6) CONSTRAINT E_PK PRIMARY KEY,
    FIRST_NAME     VARCHAR2 (20 BYTE),
    LAST_NAME      VARCHAR2 (25 BYTE),
    HIRE_DATE      DATE,
    JOB_ID         VARCHAR2 (10 BYTE),
    SALARY         NUMBER (8, 2),
    DEPARTMENT_ID  NUMBER (4),
    CONSTRAINT D_FK
    FOREIGN KEY (DEPARTMENT_ID)
    REFERENCES HR.DEPARTMENTS(department_id)
);

 

• Here is the Departments table’s data.

 

 

 

• I will try to insert ‘123’ to department_id column. I will get an error like below. Because we have foreign key constraint in department_id columnd and ‘123’ doesn’t exist in the Departments table.
• For example; I can insert ‘70’ to department_id because it exists on the other table.

INSERT INTO hr.workers (employee_id, department_id)
     VALUES (1, 123);

 

 

 

 

NOT NULL CONSTRAINT

• Prevents specified columns value from being null.

You can define it as follows.

CREATE TABLE HR.WORKERS
(
    EMPLOYEE_ID   NUMBER (6),
    FIRST_NAME    VARCHAR2 (20 BYTE) NOT NULL,
    LAST_NAME     VARCHAR2 (25 BYTE),
    HIRE_DATE     DATE,
    JOB_ID        VARCHAR2 (10 BYTE),
    SALARY        NUMBER (8, 2)
);

 

• Let’s try to insert null value to ‘first_name’ column.

INSERT INTO hr.workers (first_name)
     VALUES ('');

 

 

UNIQUE CONSTRAINT

• This Constraint type prevents multiple rows from having the same value in the same column. Namely it prevents duplication of data at columns.
• Unique Constraint can be created for one or more columns.
• More than one UC can be placed in a table.,

CREATE TABLE HR.WORKERS
(
    EMPLOYEE_ID   NUMBER (6) UNIQUE,
    FIRST_NAME    VARCHAR2 (20 BYTE),
    LAST_NAME     VARCHAR2 (25 BYTE),
    HIRE_DATE     DATE,
    JOB_ID        VARCHAR2 (10 BYTE),
    SALARY        NUMBER (8, 2)
)

 

• Let’s run this insert command two times and see the results. It will throw error at the second time we run. Because ‘employee_id’ colums must be unique.

INSERT INTO hr.workers (employee_id)
     VALUES (1);

 

 

 

 

CHECK CONSTRAINT

• it is used to a value in the database to comply with a specified condition.
• It defines the requirements for each row.
• CURRVAL, NEXTVAL, LEVEL and ROWNUM aliases can not be used.
• SYSDATE, UID, USER and USERNV functions can not be called.
• Queries referring to other values in other rows can not be performed.

CREATE TABLE HR.WORKERS
(
    EMPLOYEE_ID    NUMBER (6) CONSTRAINT E_PK PRIMARY KEY,
    FIRST_NAME     VARCHAR2 (20 BYTE),
    LAST_NAME      VARCHAR2 (25 BYTE),
    HIRE_DATE      DATE,
    JOB_ID         VARCHAR2 (10 BYTE),
    SALARY         NUMBER (8, 2) CONSTRAINT S_MIN CHECK (SALARY > 3000)
);

 

• We can’t insert ‘2500’ to salary column because of the check constraint.I can only insert values that higher than ‘3000’.

INSERT INTO hr.workers (employee_id, salary)
     VALUES (1, 2500);

 

 

 

 

Dou want to learn Oracle SQL Tutorial for Beginners, then read the following articles.

Oracle SQL Tutorials For Beginners – Learn Oracle SQL from scratch with Oracle SQL Online Course

See more