What are Integrity Constraints in DBMS?

What are Integrity Constraints in DBMS?

Integrity constraints are rules that help to maintain the accuracy and consistency of data in a database. They can be used to enforce business rules or to ensure that data is entered correctly. For example, a simple integrity constraint in DBMS might state that all customers must have a valid email address. This would prevent someone from accidentally entering an invalid email address into the database. Integrity constraints can also be used to enforce relationships between tables.

For example, if a customer can only have one shipping address, then an integrity constraint can be used to ensure that only one shipping address is entered for each customer. Enforcing integrity constraints in SQL can help prevent data inconsistencies and errors, making it easier to manage and query the data.

1. Primary Key Constraints

A primary key constraint (also known as a "primary key") is a type of key constraint that requires every value in a given column to be unique. In other words, no two rows in a table can have the same value for their primary key column(s). A primary key can either be a single column or multiple columns (known as a "composite" primary key). The null value is not allowed in the primary key column(s).

2. Unique Key Constraints

A unique key constraint is a column or set of columns that ensures that the values stored in the column are unique. A table can have more than one unique key constraint, unlike the primary key. A unique key column can contain NULL values. Like primary keys, unique keys can be made up of a single column or multiple columns.

3. Foreign Key Constraints

A foreign key constraint defines a relationship between two tables. A foreign key in one table references a primary key in another table. Foreign keys prevent invalid data from being inserted into the foreign key column. Foreign keys can reference a single column or multiple columns.

4. NOT NULL Constraints

A NOT NULL constraint is used to ensure that no row can be inserted into the table without a value being specified for the column(s) with this type of constraint. Thus, every row must have a non-NULL value for these columns.

5. Check Constraints

A check constraint enforces data integrity by allowing you to specify conditions that must be met for data to be inserted into a column. For example, you could use a check constraint to ensure that only positive integer values are inserted into a particular column. Check constraints are usually used in combination with other constraints (such as NOT NULL constraints) to enforce more complex rules.