Types of Data Integrity in SQL

Data integrity ensures the accuracy, consistency, and reliability of data stored in a database. In SQL, there are four main types of data integrity constraints:

Entity Integrity: Ensures that each row in the table is uniquely identifiable. This is typically implemented using a PRIMARY KEY constraint.
Domain Integrity: Ensures that all data values in a column fall within a defined set of permissible values. This can be implemented using data types, CHECK constraints, DEFAULT values, NOT NULL constraints, etc.
Referential Integrity: Ensures that the relationships between tables remain consistent. For instance, if there is a foreign key in one table that references the primary key of another table, then all the values must correspond to an existing record in the referenced table. This is implemented using FOREIGN KEY constraints.
User-Defined Integrity: These are specific business rules that do not fall into the above categories. These rules can be implemented using triggers, stored procedures, or application logic.

Workshop Exercise

Requirements

MySQL Server
DBeaver or any SQL client for connecting to MySQL Server

Steps

Open your SQL client and connect to your MySQL Server.

Create a new database:

 CREATE DATABASE IntegrityWorkshop;
 USE IntegrityWorkshop;

Grant necessary permissions to the database:

 GRANT ALL PRIVILEGES ON IntegrityWorkshop.* TO 'user'@'localhost';

 FLUSH PRIVILEGES;

Create a Students table to test Entity Integrity and Domain Integrity.
```
 CREATE TABLE Students (
     student_id INT AUTO_INCREMENT PRIMARY KEY,
     name VARCHAR(100) NOT NULL,
     age INT CHECK (age >= 0 AND age <= 150),
     email VARCHAR(100) UNIQUE
 );
```
Here, student_id serves as the PRIMARY KEY, ensuring Entity Integrity. The CHECK constraint on age and the UNIQUE constraint on email ensure Domain Integrity.

Insert some data into the Students table.

 INSERT INTO Students (name, age, email)
 VALUES ('Alice', 20, 'alice@email.com'),
        ('Bob', 25, 'bob@email.com');

Try inserting invalid data to test the constraints.

 -- This should fail because of age CHECK constraint
 INSERT INTO Students (name, age, email)
 VALUES ('Charlie', 200, 'charlie@email.com');

 -- This should fail because of UNIQUE email constraint
 INSERT INTO Students (name, age, email)
 VALUES ('Dave', 22, 'alice@email.com');

Create an Enrollments table to test Referential Integrity.

 CREATE TABLE Courses (
     course_id INT AUTO_INCREMENT PRIMARY KEY,
     course_name VARCHAR(100)
 );

 CREATE TABLE Enrollments (
     enrollment_id INT AUTO_INCREMENT PRIMARY KEY,
     student_id INT,
     course_id INT,
     FOREIGN KEY (student_id) REFERENCES Students(student_id),
     FOREIGN KEY (course_id) REFERENCES Courses(course_id)
 );

 -- Insert some courses
 INSERT INTO Courses (course_name)
 VALUES ('Math'), ('History');

Try inserting some data into Enrollments.

 -- This should succeed
 INSERT INTO Enrollments (student_id, course_id)
 VALUES (1, 1);

 -- This should fail due to foreign key constraint
 INSERT INTO Enrollments (student_id, course_id)
 VALUES (5, 1);

This workshop exercise should give you a practical understanding of the various types of data integrity constraints in SQL.

References

Data integrity