Integrity Constraints in DBMS

Integrity in data means maintaining and assuring the accuracy and consistency of data over its entire life cycle.

Database Systems ensure data integrity through Integrity Constraints in DBMS that are used to restrict data that can be entered or modified in the database.

Constraints are used to limit the type of data that can go into a table.

Integrity Constraints in SQL can be specified when a table is created (with the CREATE TABLE statement) or after the table is created (with the ALTER TABLE statement).

SQL Constraints

Constraints are used to limit the type of data that can go into a table.
This ensures the accuracy and reliability of the data in the table.
If there is any violation between the constraint and the data action, the action is aborted.
Constraints can be column level or table level. Column-level constraints apply to a column, and table-level constraints apply to the whole table.

Types of Integrity constraints in DBMS

NOT NULL constraints

Ensures that a column cannot have a NULL value

UNIQUE Constraints

Unique Constraint ensures that all values in a column are different. But Unique constraints can accept null values.

PRIMARY KEY constraints

A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table. There is only one Primary key in a table.

Referential integrity constraints | FOREIGN KEY Constraints

Referential integrity constraint identifies any column referencing the Primary Key in another table. It establishes a relationship between two columns within the same table or between different tables.

For a column to be defined as a foreign Key, it should be outlined as a Primary Key within the table to which it’s referring. One or more columns are often defined as a Foreign key.

CHECK Constraints

Ensures that all values in a column satisfy a specific condition

DEFAULT Constraints

Sets a default value for a column when no value is specified

Note:-

We can also specify the DEFAULT value for a column. Oracle database does not consider DEFAULT as a constraint.

Various constraints that can be created on database tables are:

NOT NULL
PRIMARY KEY
CHECK
UNIQUE
FOREIGN KEY
DEFAULT

Domain integrity constraints

NOT NULL Constraint

By default, a column can hold NULL values.
The NOT NULL constraint enforces a column to NOT accept NULL values.
Oracle NOT NULL constraint to enforce a column not to accept NULL values.
An Oracle NOT NULL constraint specifies that a column cannot contain NULL values.

CREATE TABLE table_name (
column_name data_type NOT NULL
…
);

It is possible to add a NOT NULL constraint to an existing table by using the ALTER TABLE statement.

ALTER TABLE table_name MODIFY ( column_name NOT NULL);

In this case, the column_name must not contain any NULL value before applying the NOT NULL constraint.

Oracle NOT NULL constraint examples

The following statement creates the surcharges table:

CREATE TABLE surcharges (
surcharge_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
surcharge_name VARCHAR2(255) NOT NULL,
amount NUMBER(9,2),
PRIMARY KEY (surcharge_id)
);

Check constraint

COLUMN LEVEL

An Oracle check constraint allows you to enforce domain integrity by limiting the values accepted by one or more columns.

Creating Check constraint syntax

CREATE TABLE table_name (
    …
    column_name data_type CHECK (expression),
    …
);

If you want to assign the check constraint an explicit name, you use the CONSTRAINT clause below:

CONSTRAINT check_constraint_name

CHECK (expression);

In addition, you can use the out-of-line constraint syntax as follows:

CREATE TABLE table_name (
…,
CONSTRAINT check_constraint_name CHECK (expresssion)
);

Creating SQL Check constraint examples

The following example creates the parts table whose buy prices are positive:

CREATE TABLE parts (
    part_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    part_name VARCHAR2(255) NOT NULL,
    buy_price NUMBER(9,2) CHECK(buy_price > 0),
    PRIMARY KEY(part_id)
);

Unique Key constraints

A unique constraint is an integrity constraint that ensures the data stored in a column, or a group of columns, is unique among the rows in a table.
Ensures that all values in a column are different

syntax as follows:

CREATE TABLE table_name (
    …
    column_name data_type UNIQUE
    …
)

This unique constraint specifies that the values in the column_name is unique across the whole table.

You can also use the out-of-line constraint syntax to define a unique constraint:

CREATE TABLE table_name (
…,
UNIQUE(column_name)
);

It’s possible to assign a unique constraint a name by using the CONSTRAINT clause followed by the constraint name:

CREATE TABLE table_name (
    …
    column_name data_type CONSTRAINT unique_constraint_name UNIQUE
    …
);

or with out-of-line constraint syntax:

CREATE TABLE table_name (
    …
    column_name data_type,
    …,
    CONSTRAINT unique_constraint_name UNIQUE(column_name)
);

To define a unique constraint for a group of columns or composite key, you use the out-of-line constraint syntax:

CREATE TABLE table_name (
    …
    column_name1 data_type,
    column_name2 data_type,
    …,
    CONSTRAINT unique_constraint_name UNIQUE(column_name1, column_name2)
);

PRIMARY KEY constraint At Column Level

A primary key is a column of a combination of columns in a table that uniquely identifies a row in the table.

The following are rules that make a column a primary key:

A primary key column cannot contain a NULL value or an empty string.
A primary key value must be unique within the entire table.
A primary key value should not be changed over time.

Creating a primary key that consists of one column

The following CREATE TABLE statement creates the purchase_orderstable:

CREATE TABLE purchase_orders (
    po_nr NUMBER PRIMARY KEY,
    vendor_id NUMBER NOT NULL,
    po_status NUMBER(1,0) NOT NULL,
);

PRIMARY KEY constraint At Table Level

CREATE TABLE purchase_orders (
    po_nr NUMBER
    vendor_id NUMBER NOT NULL,
    po_status NUMBER(1,0) NOT NULL,
    Constraint po_nr_ck PRIMARY KEY(po_nr)
);

PRIMARY KEY constraint At ALTER Level

ALTER TABLE table_name

ADD CONSTRAINT constraint_name

PRIMARY KEY (column1, column2, …);

The following example creates the vendor’s table first and then adds a primary key constraint to it:

CREATE TABLE vendors (
    vendor_id NUMBER,
    vendor_name VARCHAR2(255) NOT NULL,
    address VARCHAR2(255) NOT NULL
);

ALTER TABLE vendors

ADD CONSTRAINT pk_vendors PRIMARY KEY (vendor_id)

Dropping an SQL PRIMARY KEY constraint

You will rarely drop a PRIMARY KEY constraint from a table. If you have to do so, you use the following ALTER TABLE statement.

ALTER TABLE table_name

DROP CONSTRAINT primary_key_constraint_name;

FOREIGN KEY is used to relate two tables.

foreign key in SQL:

A foreign key is all about the relationship.
A foreign key is a way to enforce referential integrity within your Oracle database.
A foreign key means that values in one table must also appear in another table.
The referenced table is called the parent table while the table with the foreign key is called the child table.
The foreign key in the child table will generally reference a primary key in the parent table.

Create a foreign key constraint

The following statement illustrates the syntax of creating a foreign key constraint when you create a table:

CREATE TABLE child_table (
    …
    CONSTRAINT fk_name
    FOREIGN KEY(col1, col2,…) REFERENCES parent_table(col1,col2)
    ON DELETE [ CASCADE | SET NULL ]
);

Suppose, we have two tables supplier_groups and suppliers:

CREATE TABLE supplier_groups(
    group_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    group_name VARCHAR2(255) NOT NULL,
    PRIMARY KEY (group_id)
);

CREATE TABLE suppliers (
    supplier_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    supplier_name VARCHAR2(255) NOT NULL,
    group_id NUMBER NOT NULL,
    PRIMARY KEY(supplier_id)
);

Add a foreign key constraint to a table

If you want to add a foreign key constraint to an existing table, you use the ALTER TABLE statement as follows:

ALTER TABLE child_table

ADD CONSTRAINT fk_name

FOREIGN KEY (col1,col2) REFERENCES parent_table(col1,col2);

Drop a foreign key constraint

To drop a foreign key constraint, you use the ALTER TABLE statement below:

ALTER TABLE child_table

DROP CONSTRAINT fk_name;

Disable a foreign key constraint

To temporarily disable a foreign constraint you use the following ALTER TABLE statement:

ALTER TABLE child_table

DISABLE CONSTRAINT fk_name;

Enable a foreign constraint

Similarly, you use also use the ALTER TABLE statement to enable a disabled foreign key constraint:

ALTER TABLE child_table

ENABLE CONSTRAINT fk_name;

The foreign key with Cascade DELETE in SQL?

A foreign key with cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted. This is called a cascade delete in SQL.

A foreign key with a cascade delete can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.

Using a CREATE TABLE statement

Syntax

The syntax for creating a foreign key with cascade delete using a CREATE TABLE statement in Oracle/PLSQL is:

CREATE TABLE table_name
(
column1 datatype null/not null,
column2 datatype null/not null,
…
CONSTRAINT fk_column
     FOREIGN KEY (column1, column2, … column_n)
     REFERENCES parent_table (column1, column2, … column_n)
     ON DELETE CASCADE
);

Using an ALTER TABLE statement

Syntax

The syntax for creating a foreign key with cascade delete in an ALTER TABLE statement in Oracle/PLSQL is:

ALTER TABLE table_name

ADD CONSTRAINT constraint_name

FOREIGN KEY (column1, column2, … column_n)

REFERENCES parent_table (column1, column2, … column_n)

ON DELETE CASCADE;

If you have any queries on the database management system then comment your questions

Thank you