DELETE and Views

You cannot delete from a view referencing more than one table.

An update can choose which columns to affect, but because a DELETE is actually removing an entire row, it affects all the columns in that row. To delete from a view on two tables, you would need to delete from both tables at the same time, which is not allowed.

However, you may delete from a view referencing a single table, whether or not that view references all columns in the table. Here is a before and after picture of my inventory table, after deleting a row through a view:

-- INPUT
create view inv_view
a s
select inv_id from inventory
g o
--OUTPUT
This command did not return data, and it did not return any rows
--INPUT
select * from inventory
--OUTPUT
inv_id      item_id     qty
----------- ----------- ---
1           45          12
2           46          1
--INPUT
select * from inv_view
--OUTPUT
inv_id
------------
1
2
--INPUT
delete inv_view
where  inv_id = 1
--OUTPUT
(1 row(s) affected)
--INPUT
select * from inventory
--OUTPUT
inv_id      item_id     qty
----------- ----------- ---
2           46          1

Even though the view I created does not reference the item_id or qty column, I am allowed to delete from this view. Deleting a row must, of course, delete all the data in all the columns for that row.

TRUNCATE TABLE, DROP TABLE, and DELETE

If you intend to delete all the rows in your table, an unqualified DELETE will do the job. But a faster and more elegant way to do this is to issue the TRUNCATE TABLE command:

truncate table inventory

TRUNCATE TABLE removes all rows from a table and leaves the table structure intact. The operation is not logged in the database’s transaction log, so it is very fast.

TRUNCATE TABLE Versus DELETE: Identity Columns

If there is an identity column in the table, a DELETE will remove rows, but the next row to be inserted will continue where the identity value left off. Deleting rows from a table with an identity value means that those identity values will never be reused. TRUNCATE TABLE resets the identity value to its original seed. (By default, the seed number is 1.)

Table 9.3 outlines the differences between TRUNCATE TABLE, DELETE, and DROP TABLE.

DELETE Syntax

Here is the syntax statement for the DELETE operation:

delete <table name | view_name>
[where <conditions>]

Declarative Referential Integrity

The whole idea of referential integrity (RI) is defined by a single rule.

What is referential integrity? Every foreign key value must have a matching primary key value.

For such a powerful concept, it has a remarkably simple set of rules.

To enforce referential integrity in your database, every time that data is modified, you need to make sure that the RI rule isn’t broken. Data modification happens whenever an INSERT, UPDATE, or DELETE occurs. Simply put, whenever a primary or foreign key is modified, you must ensure that after the operation is complete, all foreign keys still reference existing primary keys.

There are several ways to do this. First, you can do it yourself, without the server’s help. Control access to your data in an application program, and before inserting, updating, or deleting data, ensure that the RI rule won’t be violated. You can also control access to the server through stored procedures, by revoking permissions on the tables. Inside the stored procedures, you can write SQL code to ensure RI is maintained.

You can also enforce referential integrity with triggers. I’ll discuss this in great detail on Days 18 and 19.

A simpler way of enforcing referential integrity is to let the server do all the work. This is called Declarative Referential Integrity and is the topic of this section.

Declarative referential integrity (DRI) uses ANSI-SQL syntax to define, to the server, the primary key and foreign key relationships between tables. It has the effect of disallowing operations that would violate referential integrity.

What’s So Great About Referential Integrity, Anyway?

Referential integrity guarantees that the relationships between your tables remain meaningful. If you had an authors table and a titles table in a database, but none of the authors had written any of the books in the titles table, you wouldn’t have very meaningful data.

Declarative Referential Integrity ensures that key relationships between tables are enforced. This ensures that the data in those tables will be related in a meaningful way.

Understanding Referential Integrity

Whether the server evaluates your data modification actions, or you evaluate those actions, it is important to understand what referential integrity requires. When a data modification statement is performed on a key field, a verification must be made. I’ll go through each of the three operations and discuss what needs to be done.

---

Note:  A refresher on keys
A primary key uniquely identifies a row in a table. The authors table has a PK defined on au_id. The titles table has a PK defined on title_id.

A foreign key is a column (or columns) in a table that references a primary key, usually in another table. The titleauthor table has a foreign key on au_id, which refers to authors.au_id. The titleauthor table also has a foreign key on title_id referencing the titles table. The titles table has a foreign key on pub_id referencing publishers.pub_id.

---