What are referential actions in SQL?

markdown

## Understanding Referential Actions in SQL

Referential actions are crucial for maintaining referential integrity in relational databases. This document outlines the different types of referential actions that can be applied to foreign key constraints.

### Database Schema Overview

#### Users Table

-+--+ | id | username | +-+--+

#### Purchases Table

-- purchases +--+ | id | user_id | item | +--+ | 20 | 1 | Keyboard | | 21 | 1 | Headphones | | 22 | 2 | Tablet | +--+

### SQL Query Example

The following SQL query retrieves the usernames along with the items they purchased:

SELECT u.username, p.item FROM users u JOIN purchases p ON u.id = p.user_id ORDER BY u.username;

### Types of Referential Actions

#### RESTRICT

If at least one row in `purchases` references a row in `users`, we will not be able to delete it. The only way to delete such a record is to first remove the data from `purchases`, and only then will **referential integrity** allow the deletion of the user.

#### CASCADE

This is the most dangerous rule, as it means that when a row in the `users` table is deleted, all rows in `purchases` that reference the user will also be deleted.

#### SET NULL

When a user is deleted from the `users` table, all `purchases` that reference them will have **NULL** written in the `foreign key (user_id)`.

### Conclusion

The same rules apply for **UPDATE operations**, but since this is used quite rarely, you will be asked specifically about **DELETE** in interviews.

Short Answer

Interview ready

Premium

A concise answer to help you respond confidently on this topic during an interview.

Finished reading?

Practice Problems