Integrity constraints in DBMS Theory - Full Explanation

Introduction

Imagine entering data into a database without any rules. This could lead to mistakes like duplicate entries or missing important information. Integrity constraints help keep the data accurate and reliable by setting rules that the data must follow.

Explanation

Entity Integrity

Entity integrity ensures that every row in a table can be uniquely identified. This is usually done by having a primary key, which is a column or set of columns that cannot be empty or duplicated. This prevents confusion between records.

Entity integrity guarantees each record is unique and identifiable by a primary key.

Referential Integrity

Referential integrity makes sure that relationships between tables stay consistent. For example, if one table refers to another through a foreign key, the foreign key value must exist in the related table. This prevents broken links between data.

Referential integrity ensures foreign keys correctly reference existing records in related tables.

Domain Integrity

Domain integrity restricts the type and range of data that can be stored in a column. For example, a column for age might only allow numbers between 0 and 120. This keeps data meaningful and prevents errors like entering text where numbers are expected.

Domain integrity enforces valid data types and acceptable values for each column.

User-Defined Integrity

User-defined integrity includes custom rules set by users to meet specific needs. These rules can be more complex, like ensuring a discount is only applied if a customer is a member. They help tailor data accuracy to particular business logic.

User-defined integrity applies custom rules to enforce specific business requirements.

Real World Analogy

Think of a library where books must be organized properly. Each book has a unique ID (like a primary key), references to authors must match actual author records (like foreign keys), the book's genre must be from a set list (like domain rules), and special rules might apply, such as only allowing rare books to be borrowed by senior members (custom rules).

Entity Integrity → Each book having a unique ID number so it can be found without confusion

Referential Integrity → Ensuring every author listed for a book actually exists in the author records

Domain Integrity → Restricting book genres to a predefined list like Fiction, Non-fiction, or Science

User-Defined Integrity → Rules like only senior members can borrow rare books

Diagram

Diagram

┌───────────────────────┐       ┌───────────────────────┐
│       Customers       │       │       Orders          │
│ ┌───────────────┐     │       │ ┌───────────────┐     │
│ │ Customer_ID PK│─────┼──────▶│ │ Customer_ID FK│     │
│ └───────────────┘     │       │ └───────────────┘     │
└───────────────────────┘       └───────────────────────┘

Domain Integrity: Age must be between 0 and 120
User-Defined Integrity: Discount only if Customer is VIP

Diagram showing two tables with primary key and foreign key relationship, plus domain and user-defined integrity rules.

Key Facts

Primary Key → A column or set of columns that uniquely identifies each row in a table and cannot be null.

Foreign Key → A column in one table that refers to the primary key in another table to maintain relationships.

Domain Constraint → A rule that limits the type, format, or range of values allowed in a column.

Entity Integrity → The rule that primary key values must be unique and not null.

Referential Integrity → The rule that foreign key values must match existing primary key values or be null.

Code Example

DBMS Theory

CREATE TABLE Customers (
  CustomerID INT PRIMARY KEY,
  Name VARCHAR(100) NOT NULL,
  Age INT CHECK (Age BETWEEN 0 AND 120)
);

CREATE TABLE Orders (
  OrderID INT PRIMARY KEY,
  CustomerID INT,
  FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

-- User-defined integrity example: discount only for VIP customers
CREATE TABLE VIPCustomers (
  CustomerID INT PRIMARY KEY,
  DiscountRate DECIMAL(3,2) CHECK (DiscountRate <= 0.5)
);

-- This setup enforces entity, referential, domain, and user-defined constraints.

Common Confusions

Believing that foreign keys must always have a value.

Believing that foreign keys must always have a value. Foreign keys can be null if the relationship is optional; referential integrity only requires that non-null foreign keys match existing primary keys.

Thinking domain constraints only check data type.

Thinking domain constraints only check data type. Domain constraints also enforce valid ranges, formats, and sets of allowed values, not just data types.

Assuming user-defined integrity is automatically enforced by the database.

Assuming user-defined integrity is automatically enforced by the database. User-defined integrity often requires custom code or triggers to enforce rules beyond standard constraints.

Summary

Integrity constraints are rules that keep database data accurate and consistent.

Entity, referential, domain, and user-defined integrity cover unique identification, relationships, valid data, and custom rules.

These constraints prevent errors like duplicates, broken links, invalid data, and business rule violations.

Practice

1. Which of the following best describes the purpose of integrity constraints in a database?

easy

A. To ensure data accuracy and consistency

B. To speed up database queries

C. To store large amounts of data

D. To create user interfaces

Solution

1. Step 1: Understand the role of integrity constraints

   Integrity constraints are rules that keep data accurate and consistent in a database.

2. Step 2: Match the purpose with the options

   Only To ensure data accuracy and consistency mentions accuracy and consistency, which matches the purpose of integrity constraints.

3. Final Answer:

   To ensure data accuracy and consistency -> Option A

4. Quick Check:

   Integrity constraints = data accuracy and consistency [OK]

Hint: Integrity constraints keep data correct and reliable [OK]

Common Mistakes:

• Confusing constraints with performance features
• Thinking constraints create user interfaces
• Assuming constraints store data

2. Which of the following is the correct syntax to declare a NOT NULL constraint on a column named email in SQL?

easy

A. email VARCHAR(50) NOT NULL

B. email VARCHAR(50) NULL

C. email VARCHAR(50) UNIQUE

D. email VARCHAR(50) PRIMARY KEY

Solution

1. Step 1: Recall the syntax for NOT NULL constraint

   The NOT NULL constraint is added after the data type to ensure the column cannot have empty values.

2. Step 2: Identify the correct option

   email VARCHAR(50) NOT NULL correctly uses email VARCHAR(50) NOT NULL. Other options either allow NULL or use different constraints.

3. Final Answer:

   email VARCHAR(50) NOT NULL -> Option A

4. Quick Check:

   NOT NULL syntax = column datatype NOT NULL [OK]

Hint: NOT NULL means column must have a value, no blanks allowed [OK]

Common Mistakes:

• Using NULL instead of NOT NULL
• Confusing UNIQUE with NOT NULL
• Using PRIMARY KEY when only NOT NULL is needed

3. Consider this SQL table definition:
<pre>CREATE TABLE Students ( ID INT PRIMARY KEY, Name VARCHAR(100) NOT NULL, Age INT CHECK (Age >= 18) );
What will happen if you try to insert (1, 'Alice', 16) into this table?

medium

A. Insertion succeeds without error

B. Insertion fails due to CHECK constraint violation

C. Insertion fails due to PRIMARY KEY violation

D. Insertion fails due to NOT NULL violation

Solution

1. Step 1: Analyze the CHECK constraint on Age

   The CHECK constraint requires Age to be 18 or older.

2. Step 2: Compare the inserted Age value

   The inserted Age is 16, which violates the CHECK constraint.

3. Final Answer:

   Insertion fails due to CHECK constraint violation -> Option B

4. Quick Check:

   Age 16 violates CHECK (Age >= 18) [OK]

Hint: CHECK constraints validate data ranges or conditions [OK]

Common Mistakes:

• Ignoring CHECK constraints and assuming insert works
• Confusing PRIMARY KEY violation with CHECK violation
• Thinking NOT NULL is violated when it is not

4. You have a table with a UNIQUE constraint on the username column. Assume the table already contains a row with username = 'john_doe'. Which of the following SQL statements will cause an error?

medium

A. INSERT INTO Users (username) VALUES ('new_user');

B. INSERT INTO Users (username) VALUES ('jane_smith');

C. INSERT INTO Users (username) VALUES ('john_doe');

D. UPDATE Users SET username = 'jane_smith' WHERE id = 1;

Solution

1. Step 1: Understand UNIQUE constraint behavior

   The UNIQUE constraint prevents duplicate values in the username column.

2. Step 2: Identify duplicate insertion

   INSERT INTO Users (username) VALUES ('john_doe'); tries to insert 'john_doe' which already exists, causing a UNIQUE violation.

3. Final Answer:

   INSERT INTO Users (username) VALUES ('john_doe'); -> Option C

4. Quick Check:

   Duplicate username violates UNIQUE constraint [OK]

Hint: UNIQUE means no duplicates allowed in that column [OK]

Common Mistakes:

• Assuming UPDATE causes error without duplicate
• Thinking inserts of new usernames cause error
• Confusing UNIQUE with PRIMARY KEY

5. A database table has two columns: OrderID and CustomerID. You want to ensure that each order belongs to an existing customer and that no two orders have the same OrderID. Which combination of integrity constraints should you use?

hard

A. NOT NULL on both columns only

B. UNIQUE on CustomerID and NOT NULL on OrderID

C. CHECK constraint on OrderID and UNIQUE on CustomerID

D. PRIMARY KEY on OrderID and FOREIGN KEY on CustomerID referencing Customers table

Solution

1. Step 1: Ensure unique OrderID

   Use PRIMARY KEY on OrderID to guarantee uniqueness and not null.

2. Step 2: Ensure Order belongs to existing Customer

   Use FOREIGN KEY on CustomerID referencing the Customers table to enforce valid customer links.

3. Final Answer:

   PRIMARY KEY on OrderID and FOREIGN KEY on CustomerID referencing Customers table -> Option D

4. Quick Check:

   Unique orders + valid customers = PRIMARY + FOREIGN KEY [OK]

Hint: Use PRIMARY KEY for unique ID, FOREIGN KEY for valid references [OK]

Common Mistakes:

• Using UNIQUE instead of PRIMARY KEY for OrderID
• Missing FOREIGN KEY for CustomerID
• Assuming NOT NULL alone enforces relationships