Integrity Constraints
Integrity constraints are guidelines that support maintaining the consistency and correctness of data in a database. For example, a basic DBMS integrity constraint may require that each student needs to have a valid roll number. This would stop someone from accidentally putting a roll number that isn't valid into the database.
Table relationships can also be enforced using integrity constraints. To ensure that just one Aadhar number is recorded for every student, for example, an integrity limitation can be applied if a student cannot have more than one.
Types of Integrity Constraints
- Domain Constraint
- Entity Integer Constraint
- Referential Integrity Constraint
- Key Constraints
1. Domain Constraint
A limitation on the values that can be kept in a column is known as a domain constraint. Examples of the data type of domain constraints include strings, characters, time, integers, currencies, dates, and so on.
For instance, domain integrity constraints in a DBMS would guarantee that only integer values could be inserted into a column named "age." This guarantees that the database only contains accurate data.
2. Entity Integrity Constraint
Constraints on entity integrity would make sure that no required columns were filled with null values. The value of the primary key must not be null, it says. We are unable to identify individual rows in a relation if the main key value is null. This is because the primary key value is utilized to identify individual rows.
For instance, an entity integrity constraint in a database management system would guarantee that a field named "roll_number" could not contain any null entries.
3. Referential Integrity Constraint
A restriction on the use of foreign keys is known as a referential integrity constraint. A column in one table that points to a primary key in another is called a foreign key.
As an example, suppose you have two tables: one for students and one for marks. A foreign key referencing the "roll_number" column in the Students table would be the "roll_number" column in the Marks table.
4. Key Constraints
A rule that specifies how information in a column or columns can be kept in a table is known as a key constraint. Each table row is uniquely identified by the values of one or more columns which make up a key.
In DBMS, there are various kinds of key constraints, each of which serves its own purpose.
i) Unique Key
A unique key refers to a column or a collection of columns that identifies every record uniquely in a table. This key's values would all need to be unique. Only one null value can exist in a unique key, and duplicate values are not accepted.
ii) Primary Key
One column in a table that helps in uniquely identifying each entry in the table is known as the primary key. Only one primary key constraint may be present in any table. Values of a primary key will not permit duplicate or null values.
iii) Foreign Key
To create relationships between two available tables, we use a foreign key. Every value in a column or group of columns would need to match the primary key of the referential table in order for the foreign key to function. We can preserve referential integrity and data by using a foreign key.
iv) Composite Key
The composite key refers to a set of various attributes that let us uniquely identify every tuple within a table. When we look at the attributes of a set separately, they might not be unique. Therefore, when we combine them all, complete uniqueness will be guaranteed.
v) Super Key
The collection of all the keys that enable us to uniquely identify each row in a table is known as a superkey. It indicates that each of the columns in a table that have the ability to uniquely identify its columns serves as a super key.
vi) Candidate Key
The characteristics that allow rows in a table to be uniquely identified are known as candidate keys. We choose the primary key from a list of candidate keys in a table. As a result, a candidate key shares characteristics with the primary keys that we have already discussed. There may be more than one candidate key in a table.
vii) Alternate Key
As previously mentioned, any table may have more than one primary key choice. However, it can only select one. As a result, alternate keys are any keys that did not become primary keys.
