Constraints in Oracle SQL

Constraints are used to specify the rules concerning data in the table. It can be applied for single or multiple fields in an SQL table during creation of table or after creationg using the ALTER TABLE command. The constraints are:

• NOT NULL – Restricts NULL value from being inserted into a column.
• CHECK – Verifies that all values in a field satisfy a condition.
• DEFAULT – Automatically assigns a default value if no value has been specified for the field.
• UNIQUE – Ensures unique values to be inserted into the field.
• PRIMARY KEY – Uniquely identifies each record in a table.
• FOREIGN KEY – Ensures referential integrity for a record in another table.

Not null Constraint

create table temp20210302_0741
(
T_NAME VARCHAR(50) NOT NULL
);

create table temp20210302_0805
(
T_NAME VARCHAR(50)
);

ALTER TABLE temp20210302_0805 MODIFY ( T_NAME NOT NULL);

Check Constraint

create table temp20210302_0807
(
AGE NUMBER,
CITY VARCHAR2(20),
CONSTRAINT AGE_CHECK CHECK(AGE>=18 and CITY='MUMBAI')
);
insert into temp20210302_0807 values (18,'MUMBAI');
insert into temp20210302_0807 values (19,'MUMBAI');
insert into temp20210302_0807 values (15,'DELHI');


insert into temp20210302_0807 values (15,'DELHI')
Error report -
ORA-02290: check constraint (ROOT.AGE_CHECK) violated

Default Constrain

CREATE TABLE Persons20210302 (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    City varchar(255) DEFAULT 'Sandnes'
);

Unique Constrain

CREATE TABLE Persons20210302_0929 (
    ID int NOT NULL UNIQUE,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
);

Primary Key Constraint

CREATE TABLE Persons (
    ID int NOT NULL PRIMARY KEY,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
);

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);

Foreign Key Constraint

CREATE TABLE Orders (
    OrderID int NOT NULL PRIMARY KEY,
    OrderNumber int NOT NULL,
    PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
    REFERENCES Persons(PersonID)
);

ALTER TABLE Orders
ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);