Constraints in Oracle

In order to enforce business rules and preserve the accuracy and consistency of data, constraints are an essential component of database management systems (DBMS), such as Oracle. In order to guarantee data integrity which is the general correctness, comprehensiveness, and dependability of data in a database they are essential. Applications that interface with the database are guaranteed to adhere to the established standards since constraints help stop the entry of invalid data by being directly embedded into the database schema. Applications need less effort to preserve data integrity when more restrictions are introduced to a table definition, yet this could result in longer update times.

Oracle offers a number of integrity constraints that can be defined at the end of the CREATE TABLE statement (out-of-line constraint) or as part of the column description (inline constraint). To better recognise and handle constraints, particularly when reading error messages, naming them is advised. Oracle stops the invalid data operation when a constraint is broken by returning an error message.

Let’s investigate the main categories of constraints:

NOT NULL Constraint

The presence of null (empty) values in a particular column is guaranteed by a NOT NULL constraint. A value for a column must be provided for each row that is added or changed if the column is designated NOT NULL. For columns like names or identifiers that are essential to a row’s meaning, this is very helpful.

Example: Consider an Employees table where EmployeeName must always have a value.

-- CREATE TABLE statement (Data Definition Language - DDL)
CREATE TABLE Employees (
    EmployeeID   NUMBER(6) PRIMARY KEY,
    EmployeeName VARCHAR2(50) NOT NULL,
    Email        VARCHAR2(100) UNIQUE
);

-- INSERT statements (Data Manipulation Language - DML)
-- Successful insert:
INSERT INTO Employees (EmployeeID, EmployeeName, Email)
VALUES (101, 'Alice Smith', 'alice.s@example.com');

-- Attempt to insert a null EmployeeName:
INSERT INTO Employees (EmployeeID, EmployeeName, Email)
VALUES (102, NULL, 'bob.j@example.com');

Output/Error: The first INSERT statement will succeed. The second INSERT statement will likely result in an error similar to:

ERROR at line 1:
ORA-01400: cannot insert NULL into ("YOUR_SCHEMA"."EMPLOYEES"."EMPLOYEENAME")

This indicates that there has been a violation of the NOT NULL constraint for EmployeeName.

UNIQUE Constraint

A UNIQUE constraint guarantees that every value in a designated column or set of columns is distinct for every table row. A unique constraint permits null values, unlike a primary key, as long as no other values in that column are the same (unless the column is also NOT NULL). When you define a UNIQUE constraint, Oracle automatically generates a unique index to enforce uniqueness.

Example: In the Employees table, each employee should have a unique email address.

-- CREATE TABLE statement (already defined above)
-- The Email column has a UNIQUE constraint.
-- CREATE TABLE Employees (
--     EmployeeID   NUMBER(6) PRIMARY KEY,
--     EmployeeName VARCHAR2(50) NOT NULL,
--     Email        VARCHAR2(100) UNIQUE
-- );

-- INSERT statements
-- Successful inserts:
INSERT INTO Employees (EmployeeID, EmployeeName, Email)
VALUES (103, 'Charlie Brown', 'charlie.b@example.com');

INSERT INTO Employees (EmployeeID, EmployeeName, Email)
VALUES (104, 'Diana Prince', 'diana.p@example.com');

-- Attempt to insert a duplicate email:
INSERT INTO Employees (EmployeeID, EmployeeName, Email)
VALUES (105, 'Charlie Chaplin', 'charlie.b@example.com');

Output/Error: The first two INSERT statements will succeed. The third INSERT statement will result in an error:

ERROR at line 1:
ORA-00001: unique constraint (YOUR_SCHEMA.SYS_C00XXXX) violated

If no name is expressly supplied for the constraint, Oracle will construct a name such as SYS_C00XXXX.) This error signifies that the UNIQUE constraint on the Email column has been broken.

PRIMARY KEY Constraint

A PRIMARY KEY constraint guarantees that no two rows in a table have the same primary key value and that no portion of the primary key can be null. It also uniquely identifies each row in the table. UNIQUE and NOT NULL requirements are essentially combined. Only one main key may be present in each table. For the purpose of creating associations between tables, primary keys are essential.

Example: The EmployeeID in the Employees table is designated as the primary key.

-- CREATE TABLE statement (already defined above)
-- The EmployeeID column has a PRIMARY KEY constraint.
-- CREATE TABLE Employees (
--     EmployeeID   NUMBER(6) PRIMARY KEY,
--     EmployeeName VARCHAR2(50) NOT NULL,
--     Email        VARCHAR2(100) UNIQUE
-- );

-- INSERT statements
-- Successful insert:
INSERT INTO Employees (EmployeeID, EmployeeName, Email)
VALUES (106, 'Eve Green', 'eve.g@example.com');

-- Attempt to insert a duplicate EmployeeID:
INSERT INTO Employees (EmployeeID, EmployeeName, Email)
VALUES (106, 'Frank Black', 'frank.b@example.com');

-- Attempt to insert a null EmployeeID (assuming it's not explicitly NOT NULL,
-- but PRIMARY KEY implies NOT NULL):
INSERT INTO Employees (EmployeeID, EmployeeName, Email)
VALUES (NULL, 'Grace Kelly', 'grace.k@example.com');

Output/Error: The first INSERT will succeed. The second INSERT (duplicate EmployeeID) will generate an error:

ERROR at line 1:
ORA-00001: unique constraint (YOUR_SCHEMA.SYS_C00XXXX) violated

The third INSERT (null EmployeeID) will also generate an error:

ERROR at line 1:
ORA-01400: cannot insert NULL into ("YOUR_SCHEMA"."EMPLOYEES"."EMPLOYEEID")

The UNIQUE and NOT NULL properties of the primary key are validated by these errors.

FOREIGN KEY Constraint

An association between data in two tables is enforced by a FOREIGN KEY constraint, sometimes referred to as a referential integrity constraint. It guarantees that values in one or more columns in the “child” table match values already present in a primary key or unique key column (or collection of columns) in the “parent” table. This avoids orphaned records, which occur when a child record makes reference to a parent who does not exist.

Example: Let’s create a Departments table and link Employees to it via a DepartmentID foreign key.

-- CREATE TABLE statement for parent table
CREATE TABLE Departments (
    DepartmentID   NUMBER(4) PRIMARY KEY,
    DepartmentName VARCHAR2(30) UNIQUE NOT NULL
);

-- Insert data into the parent table
INSERT INTO Departments (DepartmentID, DepartmentName) VALUES (10, 'Sales');
INSERT INTO Departments (DepartmentID, DepartmentName) VALUES (20, 'Marketing');
INSERT INTO Departments (DepartmentID, DepartmentName) VALUES (30, 'IT');
COMMIT;

-- Modify Employees table to include a DepartmentID and a foreign key constraint
ALTER TABLE Employees ADD DepartmentID NUMBER(4);

ALTER TABLE Employees ADD CONSTRAINT FK_Employee_Dept
FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID);

-- Insert data into child table
-- Successful insert:
INSERT INTO Employees (EmployeeID, EmployeeName, Email, DepartmentID)
VALUES (107, 'Harry Potter', 'harry.p@example.com', 30);

-- Attempt to insert a DepartmentID that does not exist in the Departments table:
INSERT INTO Employees (EmployeeID, EmployeeName, Email, DepartmentID)
VALUES (108, 'Ginny Weasley', 'ginny.w@example.com', 40);

Output/Error: The first INSERT statement will succeed. The second INSERT statement, attempting to link to DepartmentID 40 (which does not exist in Departments), will generate an error:

ERROR at line 1:
ORA-02291: integrity constraint (YOUR_SCHEMA.FK_EMPLOYEE_DEPT) violated - parent key not found

CHECK Constraint

With a CHECK constraint, you may define a requirement that every table row needs to meet. If a null is involved, the constraint must be satisfied if the condition evaluates to TRUE or UNKNOWN. For limiting values to a predetermined list or inside a specific range, this is helpful.

Example: An Orders table, for instance, requires that Quantity always be larger than zero and that OrderStatus come from a predetermined list.

-- CREATE TABLE statement
CREATE TABLE Orders (
    OrderID     NUMBER(6) PRIMARY KEY,
    OrderDate   DATE DEFAULT SYSDATE,
    Quantity    NUMBER(3) CONSTRAINT CHK_Order_Quantity CHECK (Quantity > 0),
    OrderStatus VARCHAR2(10) CONSTRAINT CHK_Order_Status CHECK (OrderStatus IN ('PENDING', 'SHIPPED', 'DELIVERED'))
);

-- Insert data
-- Successful inserts:
INSERT INTO Orders (OrderID, Quantity, OrderStatus)
VALUES (201, 5, 'PENDING');

INSERT INTO Orders (OrderID, Quantity, OrderStatus)
VALUES (202, 1, 'DELIVERED');

-- Attempt to insert a Quantity of zero or less:
INSERT INTO Orders (OrderID, Quantity, OrderStatus)
VALUES (203, 0, 'SHIPPED');

-- Attempt to insert an invalid OrderStatus:
INSERT INTO Orders (OrderID, Quantity, OrderStatus)
VALUES (204, 2, 'CANCELLED');

Output/Error: The first two INSERT statements will succeed. The third INSERT (quantity 0) will result in:

ERROR at line 1:
ORA-02290: check constraint (YOUR_SCHEMA.CHK_ORDER_QUANTITY) violated

The fourth INSERT (invalid status) will result in:

ERROR at line 1:
ORA-02290: check constraint (YOUR_SCHEMA.CHK_ORDER_STATUS) violated

These mistakes attest to the fact that the CHECK restrictions are actively verifying the entered data.

In conclusion, Oracle databases constraints are strong instruments for upholding business standards and data validity, which are essential for preserving data integrity and guaranteeing the precision and dependability of data. With careful use of PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, and NOT NULL constraints, database designers can build reliable systems that maintain data quality and guard against typical data entry errors.