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.
- Primary Key
- Foreign Key
- Unique Key
- Index
- Check
- 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