Referential integrity enforcement in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is referential integrity in a database?

Referential integrity means that relationships between tables stay correct. If one table points to another, the data must match so no broken links happen.

beginner

How does a foreign key help enforce referential integrity?

A foreign key is a column that links to a primary key in another table. It makes sure you can only add values that exist in the linked table, preventing wrong or missing references.

intermediate

What happens if you try to delete a row that other rows reference via a foreign key?

The database will block the delete unless you specify rules like CASCADE to delete related rows too, or SET NULL to clear the reference. This keeps data consistent.

intermediate

Explain the difference between ON DELETE CASCADE and ON DELETE SET NULL.

ON DELETE CASCADE deletes all rows that reference the deleted row. ON DELETE SET NULL sets the foreign key to NULL instead, keeping the row but removing the link.

beginner

Why is referential integrity important in real-life databases?

It prevents errors like orphan records or broken links, which can cause wrong reports or app crashes. It keeps data trustworthy and easy to manage.

What does a foreign key in a table do?

A Links to a primary key in another table to enforce data consistency

B Creates a unique identifier for each row

C Stores large text data

D Indexes the table for faster search

A foreign key links to a primary key in another table to keep data consistent and enforce referential integrity.

Which SQL clause enforces referential integrity when deleting a referenced row?

A ON DELETE CASCADE

B GROUP BY

C ORDER BY

D WHERE

ON DELETE CASCADE automatically deletes rows that reference the deleted row, maintaining referential integrity.

If a foreign key constraint is violated, what will the database do?

A Delete all data in the table

B Automatically fix the data

C Ignore the violation

D Reject the operation to keep data consistent

The database rejects changes that break referential integrity to avoid inconsistent data.

What does ON DELETE SET NULL do?

A Prevents deletion of the referenced row

B Sets the foreign key to NULL when the referenced row is deleted

C Deletes the referencing row

D Updates the foreign key to a default value

ON DELETE SET NULL clears the foreign key value instead of deleting the row, keeping the row but removing the link.

Which of these is NOT a benefit of enforcing referential integrity?

A Preventing orphan records

B Ensuring data consistency

C Automatically backing up data

D Avoiding broken links between tables

Referential integrity does not handle backups; it ensures data consistency and valid links.

Describe what referential integrity means and how foreign keys help enforce it.

Think about how tables relate and how the database keeps those links correct.

Explain the difference between ON DELETE CASCADE and ON DELETE SET NULL in foreign key constraints.

Consider what happens to rows that reference a deleted row.