Deleting Records with DELETE
Remove rows safely with DELETE, compare DELETE with TRUNCATE, understand foreign-key cascade rules, and inspect deleted rows using PostgreSQL RETURNING.
In this lesson, you will learn
- ✓Explain how DELETE removes rows from a table
- ✓Delete specific records safely using WHERE criteria
- ✓Understand the consequences of DELETE without WHERE
- ✓Use DELETE with comparison, logical, and NULL conditions
- ✓Inspect deleted rows using PostgreSQL RETURNING
- ✓Compare DELETE, TRUNCATE, and DROP TABLE
- ✓Understand foreign-key restrictions and ON DELETE actions
- ✓Use transactions to review or roll back deletions
- ✓Understand how indexes and triggers affect deletion operations
- ✓Use parameterized queries for safe deletions in applications
1. Introduction to DELETE
The SQL DELETE statement removes rows from a table.
Imagine an employees table:
| employee_id | employee_name | department | salary |
|---:|---|---|---:|
| 1 | Asha | Engineering | 50000 |
| 2 | Rahul | Sales | 40000 |
| 3 | Neha | Support | 35000 |
Suppose Rahul leaves the company and his employee record must be removed. You can use DELETE.
### Basic syntax
```sql
DELETE FROM table_name
WHERE condition;
```
- DELETE FROM specifies the table.
- WHERE identifies the rows to remove.
- The condition determines which rows qualify for deletion.
### Delete one employee
```sql
DELETE FROM employees
WHERE employee_id = 2;
```
The row with employee_id 2 is targeted.
### Verify the result
```sql
SELECT *
FROM employees
ORDER BY employee_id;
```
Assuming the original sample data, Rahul's row is no longer present.
**Important:** DELETE removes data, not the table definition. The table can still accept new rows afterward, provided its constraints and other rules are satisfied.
2. Create a Practice Table
Use this table to practice the deletion statements in this lesson.
### Create the table
```sql
CREATE TABLE employees_delete_demo (
employee_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
employee_name VARCHAR(100) NOT NULL,
department VARCHAR(60) NOT NULL,
salary NUMERIC(10, 2) NOT NULL CHECK (salary >= 0),
city VARCHAR(80),
active BOOLEAN NOT NULL DEFAULT TRUE
);
```
### Insert sample rows
```sql
INSERT INTO employees_delete_demo
(employee_name, department, salary, city, active)
VALUES
('Asha', 'Engineering', 50000, 'Lucknow', TRUE),
('Rahul', 'Sales', 40000, 'Noida', TRUE),
('Neha', 'Engineering', 60000, 'Delhi', TRUE),
('Kabir', 'Support', 35000, NULL, TRUE),
('Meera', 'Sales', 45000, 'Lucknow', FALSE);
```
### Inspect the table
```sql
SELECT employee_id, employee_name, department, salary, city, active
FROM employees_delete_demo
ORDER BY employee_id;
```
The identity column generates IDs automatically. The examples assume a fresh practice table populated with these five rows.
The `_delete_demo` suffix helps keep exercises separate from any real employees table.
3. Delete Specific Rows with WHERE
The safest common pattern is to delete using a unique identifier.
### Delete one employee
```sql
DELETE FROM employees_delete_demo
WHERE employee_id = 2;
```
Only the row with ID 2 is targeted.
### Delete by another condition
```sql
DELETE FROM employees_delete_demo
WHERE department = 'Support';
```
Every row whose department is Support is targeted.
This condition may match more than one row in a larger database.
### Preview the target rows first
```sql
SELECT employee_id, employee_name, department
FROM employees_delete_demo
WHERE department = 'Support';
```
Review the result before running the DELETE.
### Why IDs are often preferable
Consider:
```sql
DELETE FROM employees_delete_demo
WHERE employee_name = 'Rahul';
```
If multiple employees have the same name, this can delete multiple rows.
By contrast, a primary-key condition identifies at most one row:
```sql
DELETE FROM employees_delete_demo
WHERE employee_id = 2;
```
The ID must still refer to the intended employee. A primary key makes the target unique, not automatically correct.
Delete and return the removed row
This output assumes the sample table is fresh and ID 2 belongs to Rahul. If no row matches, RETURNING produces no rows.
4. What Happens Without WHERE?
Consider this statement:
```sql
DELETE FROM employees_delete_demo;
```
There is no WHERE clause. Therefore, every row in the table is targeted for deletion.
The table itself remains. Its columns, constraints, and indexes are not dropped simply because its rows were deleted.
### Why this can be dangerous
An application might intend to delete one user's record but accidentally omit the condition. The statement could remove every row.
### Safer workflow
**Step 1: Preview the intended rows.**
```sql
SELECT employee_id, employee_name
FROM employees_delete_demo
WHERE employee_id = 2;
```
**Step 2: Delete using the same condition.**
```sql
DELETE FROM employees_delete_demo
WHERE employee_id = 2;
```
**Step 3: Verify the result.**
```sql
SELECT *
FROM employees_delete_demo
WHERE employee_id = 2;
```
If no row with that ID remains, the SELECT returns no rows.
### Important limitation
A preview and a later DELETE are separate statements. In a concurrent database, data can change between them. For important operations, consider transactions, suitable isolation or locking, and conditions that identify the intended records.
Never run an unrestricted DELETE merely because it is shorter.
5. Delete Rows Using Conditions
DELETE supports the same kinds of filtering conditions you use with SELECT and UPDATE.
### Comparison operator
```sql
DELETE FROM employees_delete_demo
WHERE salary < 36000;
```
This targets rows whose salary is below 36000.
### AND: both conditions must match
```sql
DELETE FROM employees_delete_demo
WHERE department = 'Sales'
AND active = FALSE;
```
This targets inactive employees in Sales.
### OR: either condition can match
```sql
DELETE FROM employees_delete_demo
WHERE department = 'Support'
OR salary < 36000;
```
A row qualifies if either condition is TRUE.
### IN: match a list of values
```sql
DELETE FROM employees_delete_demo
WHERE department IN ('Support', 'Temporary');
```
This targets rows in either department.
### IS NULL: target missing values
```sql
DELETE FROM employees_delete_demo
WHERE city IS NULL;
```
This targets rows whose city is NULL.
Do not use city = NULL. Ordinary comparisons with NULL evaluate to UNKNOWN, which does not qualify a row in WHERE.
### Parentheses make conditions clearer
```sql
DELETE FROM employees_delete_demo
WHERE department = 'Sales'
AND (active = FALSE OR salary < 40000);
```
The parentheses make it clear that either inactivity or a low salary must accompany the Sales condition.
6. RETURNING: Inspect Deleted Rows
PostgreSQL supports RETURNING with DELETE. It returns values from the rows deleted by the statement.
### Return all deleted columns
```sql
DELETE FROM employees_delete_demo
WHERE employee_id = 2
RETURNING *;
```
The returned result shows the deleted row's values.
### Return selected columns
```sql
DELETE FROM employees_delete_demo
WHERE department = 'Support'
RETURNING employee_id, employee_name;
```
This returns the ID and name of each row deleted by the statement.
### Why RETURNING is useful
- Confirms which rows were affected.
- Provides deleted values without a separate SELECT.
- Helps applications build responses or audit workflows.
- Makes it easier to detect unexpected deletion counts.
### Important details
- If no rows match, RETURNING returns no rows.
- RETURNING does not commit an explicit transaction.
- RETURNING * includes every column, so avoid it when the table contains sensitive fields that do not need to be exposed.
- A statement can fail because of constraints or other database errors; handle those errors in application code.
Delete a row and inspect its previous values
The expected result assumes ID 3 belongs to Neha and that the row has not already been deleted. RETURNING exposes values from the deleted row.
7. DELETE vs TRUNCATE vs DROP TABLE
These three commands have different purposes.
| Command | What it removes | WHERE supported? | Table structure remains? |
|---|---|---|---|
| DELETE | Selected rows or all rows | Yes | Yes |
| TRUNCATE | All rows in specified tables | No | Yes |
| DROP TABLE | The table itself | No | No |
### DELETE: remove selected rows
```sql
DELETE FROM employees_delete_demo
WHERE department = 'Support';
```
DELETE supports row filtering and fires DELETE triggers for affected rows.
### TRUNCATE: remove all rows efficiently
```sql
TRUNCATE TABLE employees_delete_demo;
```
TRUNCATE removes all rows without scanning and deleting them one at a time. It commonly has lower overhead for clearing an entire table.
In PostgreSQL, TRUNCATE takes an ACCESS EXCLUSIVE lock and has different trigger behavior from DELETE. It fires TRUNCATE triggers, not row-level DELETE triggers.
### DROP TABLE: remove the table
```sql
DROP TABLE employees_delete_demo;
```
This removes the table definition as well as its data.
### Important PostgreSQL behavior
TRUNCATE is transaction-safe in PostgreSQL: it can be rolled back if executed in a transaction that is rolled back. Its locking and foreign-key behavior still require care.
TRUNCATE also supports identity restart options. For example:
```sql
TRUNCATE TABLE employees_delete_demo RESTART IDENTITY;
```
This resets owned identity sequences associated with the truncated table.
Without RESTART IDENTITY, the identity sequence is not automatically reset by TRUNCATE.
**Remember:** Use DELETE when you need to select rows. Use TRUNCATE when clearing all rows is intended and its locking and foreign-key implications are acceptable. Use DROP TABLE only when the table itself should be removed.
8. Foreign Keys and ON DELETE Actions
A foreign key protects relationships between tables.
Imagine these tables:
- customers stores customer information.
- orders stores orders and references a customer.
### Example schema
```sql
CREATE TABLE customers_demo (
customer_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_name VARCHAR(100) NOT NULL
);
CREATE TABLE orders_demo (
order_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id INTEGER NOT NULL
REFERENCES customers_demo(customer_id),
total NUMERIC(10, 2) NOT NULL CHECK (total >= 0)
);
```
The foreign key prevents an order from referencing a customer that does not exist.
### What happens when you delete a customer?
```sql
DELETE FROM customers_demo
WHERE customer_id = 1;
```
If an order still references customer 1, PostgreSQL normally rejects the deletion under the default NO ACTION behavior.
The order must be handled according to the application's business rules.
### ON DELETE CASCADE
A foreign key can be defined with:
```sql
customer_id INTEGER NOT NULL
REFERENCES customers_demo(customer_id)
ON DELETE CASCADE
```
With this rule, deleting a referenced customer also deletes referencing orders.
### Other common actions
- NO ACTION: normally rejects the delete if referencing rows remain when the constraint is checked.
- RESTRICT: prevents the deletion while referencing rows exist.
- CASCADE: deletes referencing child rows.
- SET NULL: sets referencing columns to NULL, if allowed by the schema.
- SET DEFAULT: assigns the declared default, which must still satisfy the foreign-key constraint.
### Critical warning
CASCADE can remove many related rows. It is not a general cleanup shortcut. Use it only when deleting child records is the intended business rule.
If the relationship is important for history, invoices, or compliance, consider retaining the parent and using a status or soft-delete design instead.
9. Transactions: Review Before Committing
A transaction allows you to group a deletion with other database work.
### Example: Delete inside a transaction
```sql
BEGIN;
DELETE FROM employees_delete_demo
WHERE employee_id = 2
RETURNING employee_id, employee_name;
-- Review the returned rows.
-- If the deletion is correct:
COMMIT;
```
If you discover a problem before committing, execute:
```sql
ROLLBACK;
```
ROLLBACK discards the transaction's uncommitted changes.
### Try a rollback safely
```sql
BEGIN;
DELETE FROM employees_delete_demo
WHERE employee_id = 1
RETURNING employee_id, employee_name;
ROLLBACK;
SELECT *
FROM employees_delete_demo
WHERE employee_id = 1;
```
Assuming the transaction and statements succeed, the final SELECT can still find the row because the deletion was rolled back.
### Important distinctions
- COMMIT makes the transaction's changes permanent within the database's normal transaction model.
- ROLLBACK undoes uncommitted database changes in the transaction.
- ROLLBACK cannot undo an email already sent or an external API call already made.
- A rollback is not a substitute for verifying the target condition.
Once a transaction has been committed, recovery usually requires a separate corrective operation or a database recovery mechanism.
10. Soft Delete vs Hard Delete
Not every application should permanently remove a record when a user clicks Delete.
### Hard delete
A hard delete removes the row:
```sql
DELETE FROM employees_delete_demo
WHERE employee_id = 2;
```
The row is removed from the table, subject to transaction and database behavior.
### Soft delete
A soft delete marks a record as deleted instead of removing it.
For example, a table might contain:
```sql
ALTER TABLE employees_delete_demo
ADD COLUMN deleted_at TIMESTAMPTZ;
```
Mark a row as deleted:
```sql
UPDATE employees_delete_demo
SET deleted_at = CURRENT_TIMESTAMP
WHERE employee_id = 2;
```
Retrieve records that have not been soft-deleted:
```sql
SELECT employee_id, employee_name
FROM employees_delete_demo
WHERE deleted_at IS NULL;
```
### Advantages of soft deletion
- Supports recovery from accidental user actions.
- Can preserve business history.
- Helps maintain references for reports or workflows.
- Can support audit requirements when designed correctly.
### Trade-offs
- Every relevant query must account for deleted rows.
- Unique constraints may need special handling.
- Storage continues to be used.
- Soft deletion does not automatically meet legal retention or audit requirements.
Use the approach that matches the application's requirements. A soft delete is not automatically safer or more compliant in every situation.
11. Performance and Operational Considerations
Deletion performance depends on the amount of data, indexes, constraints, triggers, foreign keys, row visibility, and concurrent activity.
### Indexes can help find target rows
Consider:
```sql
DELETE FROM employees_delete_demo
WHERE employee_id = 2;
```
Because employee_id is a primary key, PostgreSQL creates a unique index for it. The index can help locate the target row efficiently.
### What about deleting many rows?
```sql
DELETE FROM employees_delete_demo
WHERE active = FALSE;
```
If many rows match, PostgreSQL must process the qualifying rows and maintain relevant indexes and constraints. The cost depends on the table and workload.
### Why DELETE and TRUNCATE differ
DELETE supports WHERE and row-level DELETE triggers. TRUNCATE clears whole tables and uses different locking and trigger behavior.
TRUNCATE is not always appropriate, particularly when concurrent access, foreign keys, or row-level business logic matter.
### Inspect the plan carefully
You can inspect the plan for the SELECT condition that identifies rows:
```sql
EXPLAIN
SELECT employee_id
FROM employees_delete_demo
WHERE department = 'Sales';
```
This helps you understand how PostgreSQL locates qualifying rows. It does not execute the deletion.
For a DELETE statement, EXPLAIN can show the planned operation. EXPLAIN ANALYZE executes the statement, so using it with a destructive command can actually delete data. Use a safe test database or a carefully managed transaction when examining destructive statements.
### Additional considerations
- Foreign-key checks can affect deletion cost.
- Cascading deletes can affect many child rows.
- Deleting many rows can generate substantial write-ahead log activity.
- Deleted row space is not necessarily returned to the operating system immediately; PostgreSQL's vacuuming and storage management affect space reuse.
- Appropriate indexes help some filters but add maintenance work to writes.
12. Guided Practice
Use employees_delete_demo. Reset the practice data between exercises if needed.
### Exercise 1: Delete one employee
Delete employee ID 2.
### Exercise 2: Delete by department
Delete employees in the Support department.
### Exercise 3: Delete using two conditions
Delete inactive employees in Sales.
### Exercise 4: Delete NULL values
Delete rows where city is NULL.
### Exercise 5: Use RETURNING
Delete employee ID 3 and return the deleted ID, name, and salary.
### Exercise 6: Explain missing WHERE
Explain what happens when DELETE FROM employees_delete_demo runs without WHERE.
### Exercise 7: Compare commands
Explain the difference between DELETE, TRUNCATE, and DROP TABLE.
### Exercise 8: Foreign keys
Explain why deleting a customer can fail when orders still reference that customer.
### Exercise 9: Transaction rollback
Delete employee ID 1 inside a transaction and roll back the transaction. What should a later SELECT show?
### Exercise 10: Soft delete
Add a deleted_at column and mark employee ID 2 as deleted without physically removing the row.
### Exercise 11: Parameterized deletion
Write a Node.js PostgreSQL query that deletes an employee using employeeId as a parameter and returns the deleted ID.
### Exercise 12: Choose the correct command
You need to remove all rows from a disposable staging table while preserving its structure. Which command could be appropriate, assuming its locking and foreign-key behavior are acceptable?
13. Practice Solutions
### Solution 1: Delete one employee
```sql
DELETE FROM employees_delete_demo
WHERE employee_id = 2;
```
### Solution 2: Delete by department
```sql
DELETE FROM employees_delete_demo
WHERE department = 'Support';
```
### Solution 3: Delete using two conditions
```sql
DELETE FROM employees_delete_demo
WHERE department = 'Sales'
AND active = FALSE;
```
### Solution 4: Delete NULL values
```sql
DELETE FROM employees_delete_demo
WHERE city IS NULL;
```
### Solution 5: Use RETURNING
```sql
DELETE FROM employees_delete_demo
WHERE employee_id = 3
RETURNING employee_id, employee_name, salary;
```
### Solution 6: Explain missing WHERE
Every row in employees_delete_demo is targeted. The table structure remains.
### Solution 7: Compare commands
- DELETE removes selected rows or all rows.
- TRUNCATE removes all rows while retaining the table structure.
- DROP TABLE removes the table structure and its data.
### Solution 8: Foreign keys
The referenced customer cannot normally be deleted while referencing orders remain under the default foreign-key behavior. The application must handle the child rows or use a suitable ON DELETE rule.
### Solution 9: Transaction rollback
```sql
BEGIN;
DELETE FROM employees_delete_demo
WHERE employee_id = 1;
ROLLBACK;
```
Assuming the transaction succeeds and remains uncommitted until ROLLBACK, the deletion is undone.
### Solution 10: Soft delete
```sql
ALTER TABLE employees_delete_demo
ADD COLUMN deleted_at TIMESTAMPTZ;
UPDATE employees_delete_demo
SET deleted_at = CURRENT_TIMESTAMP
WHERE employee_id = 2;
```
Run the ALTER TABLE only once for this practice table. If deleted_at already exists, the ALTER statement will fail unless you adapt it.
### Solution 11: Parameterized deletion
```javascript
const result = await pool.query(
"DELETE FROM employees_delete_demo WHERE employee_id = $1 RETURNING employee_id",
[employeeId]
);
```
This example assumes pool is a configured PostgreSQL client pool.
### Solution 12: Choose the correct command
```sql
TRUNCATE TABLE staging_table;
```
TRUNCATE is appropriate when all rows should be removed and its locking, foreign-key, and trigger behavior is acceptable.
14. Knowledge Check: MCQs
Choose the best answer for each question.
15. SQL Interview Questions and Answers
### 1. What is DELETE in SQL?
DELETE is a DML statement used to remove existing rows from a table.
### 2. What is the purpose of WHERE in DELETE?
WHERE identifies the rows that qualify for deletion. Without WHERE, every row is targeted.
### 3. Does DELETE remove the table structure?
No. DELETE removes rows but retains the table definition.
### 4. What is the difference between DELETE and TRUNCATE?
DELETE supports row filtering and row-level DELETE triggers. TRUNCATE clears whole tables and uses different locking and trigger behavior.
### 5. What is the difference between TRUNCATE and DROP TABLE?
TRUNCATE removes rows but preserves the table definition. DROP TABLE removes the table itself.
### 6. What does RETURNING do in PostgreSQL?
It returns values from rows affected by the DELETE statement.
### 7. Can DELETE be rolled back?
Yes, if the deletion is part of an uncommitted transaction that is rolled back.
### 8. What happens if a foreign key references the row you want to delete?
The deletion may be rejected, or it may affect referencing rows according to the foreign key's ON DELETE action.
### 9. What is ON DELETE CASCADE?
It automatically deletes referencing child rows when the referenced parent row is deleted.
### 10. What does ON DELETE SET NULL do?
It sets referencing foreign-key columns to NULL, provided the schema allows NULL and the resulting row satisfies other constraints.
### 11. Why should you preview rows before deleting?
A preview helps verify that the WHERE condition selects the intended records.
### 12. Does TRUNCATE fire row-level DELETE triggers in PostgreSQL?
No. TRUNCATE fires TRUNCATE triggers rather than row-level DELETE triggers.
### 13. Is TRUNCATE transactional in PostgreSQL?
Yes. A TRUNCATE executed inside a transaction can be rolled back if that transaction is rolled back.
### 14. What is a soft delete?
A soft delete marks a row as deleted, often with a timestamp or status column, instead of physically removing it.
### 15. What is a hard delete?
A hard delete removes the row from the table, subject to the transaction and database behavior.
### 16. Can DELETE use AND and OR?
Yes. These operators combine conditions in the WHERE clause.
### 17. What happens if no rows match the DELETE condition?
No rows are deleted. With RETURNING, no rows are returned.
### 18. Can DELETE affect related tables?
Yes. Foreign-key actions such as CASCADE can affect referencing rows.
### 19. Why can deleting many rows be expensive?
The database must process the rows, maintain indexes and constraints, run applicable triggers, and perform associated write and logging work.
### 20. Why are parameterized DELETE queries important?
They help prevent SQL injection by separating values from the SQL statement's syntax.
### 21. Is DELETE FROM table_name WHERE id = NULL correct?
No. Use IS NULL when checking for NULL. A comparison using = NULL does not evaluate to TRUE.
### 22. Does TRUNCATE always reset identity values?
No. In PostgreSQL, use RESTART IDENTITY when you want owned identity sequences reset as part of TRUNCATE.
### 23. Can TRUNCATE be used on a table referenced by foreign keys?
PostgreSQL can reject TRUNCATE when other tables reference the target table, unless the relevant tables are also included or CASCADE is used. CASCADE should be used cautiously because it can truncate additional tables.
### 24. Is a SELECT preview enough to guarantee a later DELETE affects the same rows?
Not necessarily. Concurrent changes can occur between statements. Important operations may require a transaction, appropriate locking or isolation, and a well-designed condition.
16. Summary and Final Checklist
## Summary
- DELETE removes rows from a table.
- WHERE identifies the rows to remove.
- Without WHERE, every row in the target table is targeted.
- RETURNING shows values from deleted rows in PostgreSQL.
- TRUNCATE clears whole tables while retaining their definitions.
- DROP TABLE removes the table definition.
- Foreign keys can restrict or propagate deletions.
- CASCADE can delete related rows and must be used carefully.
- Transactions support rollback before commit.
- Soft deletion marks a row instead of physically removing it.
- Indexes, triggers, constraints, and foreign keys affect performance and behavior.
- Parameterized queries help protect application code from SQL injection.
## Final checklist
- [ ] Write DELETE with a WHERE condition.
- [ ] Explain the consequences of omitting WHERE.
- [ ] Preview the intended rows using SELECT.
- [ ] Delete using a primary key.
- [ ] Use AND, OR, IN, and IS NULL correctly.
- [ ] Use RETURNING to inspect deleted values.
- [ ] Compare DELETE, TRUNCATE, and DROP TABLE.
- [ ] Explain ON DELETE CASCADE and SET NULL.
- [ ] Use BEGIN and ROLLBACK in a practice database.
- [ ] Explain soft deletion.
- [ ] Recognize the impact of indexes, triggers, and foreign keys.
- [ ] Use parameterized queries in applications.
**Next:** Continue to the next lesson in the Modifying Data chapter.
17. Common Mistakes
Avoid these errors when deleting records.
Common pitfalls to avoid
Real debugging lessons and exact syntax contrasts to avoid runtime bugs
#1 Forgetting WHERE
Why it happens: The author assumes DELETE automatically targets one record.
Key takeaway: Without WHERE, every row in the target table is targeted.
#2 Using equality to check for NULL
Why it happens: NULL is treated as if it were an ordinary value.
Key takeaway: Use IS NULL to identify missing values.
#3 Confusing DELETE with TRUNCATE
Why it happens: The commands are assumed to have identical filtering and trigger behavior.
Key takeaway: Use DELETE for selected rows and TRUNCATE when clearing the entire table is intended.
Ready for hands-on challenges?
Test your skills with shuffled coding challenges in the Practice Arena.
Key takeaways
- DELETE removes existing rows while leaving the table structure in place.
- WHERE identifies which rows to delete; without it, all rows in the target table are targeted.
- PostgreSQL RETURNING can return the values of deleted rows.
- TRUNCATE removes all rows from a table without using a row-by-row DELETE operation.
- DROP TABLE removes the table itself, including its structure.
- Foreign keys may prevent a deletion or propagate it according to their ON DELETE action.
- A transaction can roll back an uncommitted DELETE.
- TRUNCATE has different locking, trigger, and foreign-key behavior from DELETE.
- Indexes, constraints, triggers, and foreign-key checks can affect deletion performance.
- Parameterized queries help protect application code from SQL injection.