Integrity constraint over relations in DBMS

Integrity constraints are rules that help to maintain the accuracy and consistency of data in a database. For example, a simple integrity constraint in DBMS might state that all students must have a valid Roll Number. This would prevent someone from accidentally entering an invalid roll number into the database.

Integrity constraints can also be used to enforce relationships between tables. For example, if a student can only have one aadhaar number, then an integrity constraint can be used to ensure that only one aadhaar number is entered for each student.

Different types of Integrity Constraints

Domain Constraint

A domain constraint is a restriction on the values that can be stored in a column. Strings, character, time, integer, currency, date etc. Are examples of the data type of domain constraints.

example, if you have a column for "age" domain integrity constraints in DBMS would ensure that only integer values can be entered into that column. This ensures that only valid data is entered into the database.

Entity Integer Constraint

Entity integrity constraints would ensure that null values are not entered into any required columns. It states that primary key value can't be null. This is because the primary key value is used to identify individual rows in relation and if the primary key has a null value, then we can't identify those rows

For example, if you have a column for "roll_number" an entity integrity constraint in DBMS would ensure that this column cannot contain any null values.

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
   PRIMARY KEY(column)
);

Referential Integrity Constraint

A referential integrity constraint is a restriction on how foreign keys can be used. A foreign key is a column in one table that references a primary key in another table.

For example, let's say you have a table of Students and a table of Marks. The "roll_number" column in the Marks table would be a foreign key that references the "roll_number" column in the Students table.

CREATE TABLE table_name (
	column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
   FOREIGN KEY (column) REFERENCES table_name1(column)
);

Key Constraints

A key constraint is a rule that defines how data in a column(s) can be stored in a table. A key is composed of one or more columns whose values uniquely identify each row in the table. There are several different types of key constraints in DBMS, each with its own specific purpose.

1. Unique Key

A unique key refers to a column or a set of columns that identify every record uniquely in a table. All the values in this key would have to be unique. values of a unique key won’t allow duplicate values and it is only capable of having one null value.

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
   UNIQUE (column)
);

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
   CONSTRAINT constraint_name UNIQUE(column)
);

2. Primary Key

The primary key refers to a column of a table that helps us identify all the records uniquely present in that table. Any table can consist of only a single primary key constraint. values of a primary key won’t allow null value or a duplicate values.

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
   CONSTRAINT constraint_name PRIMARY KEY(column)
);

3. Foreign Key

We use a foreign key to establish relationships between two available tables. The foreign key would require every value present in a column/set of columns to match the referential table’s primary key. A foreign key helps us to maintain data as well as referential integrity.

CREATE TABLE table_name (
	column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
   CONSTRAINT constraint_name FOREIGN KEY (column)
    REFERENCES table_name1(column)
);

4. Composite Key

The composite key refers to a set of multiple attributes that help us uniquely identify every tuple present in a table. The attributes present in a set may not be unique whenever we consider them separately. Thus, when we take them all together, it will ensure total uniqueness.

CREATE TABLE table_name (
	column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
   CONSTRAINT constraint_name UNIQUE(column,column)
);

5. Super Key

A super key refers to the set of all those keys that help us uniquely identify all the rows present in a table. It means that all of these columns present in a table that can identify the columns of that table uniquely act as the super keys.

6. Candidate Key

The candidate keys refer to those attributes that identify rows uniquely in a table. In a table, we select the primary key from a candidate key. Thus, a candidate key has similar properties as that of the primary keys that we have explained above. In a table, there can be multiple candidate keys.

7. Alternate Key

As we have stated above, any table can consist of multiple choices for the primary key. But, it can only choose one. Thus, all those keys that did not become a primary key are known as alternate keys.

CHECK Constraint

The CHECK constraint is used to limit the value range that can be placed in a column. If you define a CHECK constraint on a column it will allow only certain values for this column. If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.

CREATE TABLE table_name (
	column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
   CONSTRAINT constraint_name CHECK (condition)
);

CREATE TABLE students (
    ID int,
    LastName varchar(30),
    FirstName varchar(30),
    Age int,
	CONSTRAINT con_age CHECK (Age>=18)
);

DEFAULT Constraint

The DEFAULT constraint is used to set a default value for a column. The default value will be added to all new records, if no other value is specified.

The DEFAULT constraint can also be used to insert system date, by using functions like GETDATE().

CREATE TABLE table_name (
	column1 datatype,
    column2 datatype DEFAULT 'value',
    column3 datatype DEFAULT GETDATE(),
   ....
);

CREATE TABLE students (
    ID int,
    LastName varchar(30),
    FirstName varchar(30),
    Age int DEFAULT 18,
    joining_date DEFAULT GETDATE()
);