Integrity constraints in DBMS Theory - Step-by-Step Execution

Concept Flow - Integrity constraints

Start: Insert/Update/Delete

↓

Check NOT NULL

↓

Check UNIQUE

↓

Check PRIMARY KEY

↓

Check FOREIGN KEY

↓

Check CHECK constraints

↓

If all pass

→Operation Allowed

↓

If any fail

→Operation Rejected

When data is changed, the system checks each integrity rule step-by-step. If all rules pass, the change is allowed; if any fail, it is rejected.

Execution Sample

DBMS Theory

Insert into Students (ID, Name, Age) values (101, 'Amy', 20);
-- ID is PRIMARY KEY
-- Name is NOT NULL
-- Age must be >= 18 (CHECK)

This inserts a student record while checking key integrity rules like primary key uniqueness, not null, and age condition.

Analysis Table

Step	Constraint Checked	Condition	Result	Action
1	NOT NULL on Name	Name='Amy' is not null	Pass	Continue
2	PRIMARY KEY on ID	ID=101 unique?	Pass	Continue
3	CHECK on Age	Age=20 >= 18?	Pass	Continue
4	All constraints passed	-	-	Insert allowed

💡 All constraints passed, so the insert operation is allowed.

State Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
Name	null	'Amy'	'Amy'	'Amy'	'Amy'
ID	null	null	101	101	101
Age	null	null	null	20	20

Key Insights - 3 Insights

Why does the operation stop if a NOT NULL constraint fails?

Because NOT NULL ensures a column must have a value. If it is null, the operation is rejected immediately as shown in step 1 of the execution_table.

What happens if the PRIMARY KEY value already exists?

The operation is rejected because PRIMARY KEY must be unique. This would be caught in step 2 of the execution_table where uniqueness is checked.

How does a CHECK constraint affect data insertion?

CHECK verifies a condition on data (like Age >= 18). If false, the operation is rejected as shown in step 3 of the execution_table.

Visual Quiz - 3 Questions

Look at the execution_table, what is the result of the PRIMARY KEY check at step 2?

A Pass

B Fail

C Not checked

D Skipped

💡 Hint

Check the 'Result' column in row 2 of the execution_table.

At which step does the system verify that Age is at least 18?

A Step 1

B Step 3

C Step 2

D Step 4

💡 Hint

Look at the 'Constraint Checked' column for the CHECK constraint in the execution_table.

If the Name was null, what would happen at step 1?

A Skip NOT NULL check

B Pass and continue

C Fail and reject operation

D Check other constraints first

💡 Hint

Refer to the key_moments section about NOT NULL failure and step 1 in the execution_table.

Concept Snapshot

Integrity constraints ensure data correctness.
Common types: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK.
Each data change is checked against these rules.
If any fail, the operation is rejected.
They keep the database reliable and consistent.

Full Transcript

Integrity constraints are rules that a database uses to keep data correct and reliable. When you add or change data, the database checks these rules one by one. For example, NOT NULL means a column cannot be empty. PRIMARY KEY means each value must be unique. CHECK means a value must meet a condition like age being at least 18. If all checks pass, the data is saved. If any fail, the change is stopped. This step-by-step checking helps keep the database clean and trustworthy.