NAME

DBIx::Class::Manual::SQLHackers::DELETE - DBIx::Class for SQL Hackers - DELETE

Introduction
CREATE
INSERT
SELECT
UPDATE
DELETE
BEGIN, COMMIT

DELETEing data

Delete a single row based on the primary key

    DELETE FROM users
    WHERE id = 1;

The simplest form of delete removes a single row from a table using the primary key value. We find the row, then call the delete method on it. delete can be called on any result row object.

1. Create a Schema object representing the database you are working with:
        my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
2. Call the find method on the resultset for the ResultSource you wish to delete:
        my $fred_user = $schema->resultset('User')->find({ id => 1 });
3. Call delete on the row object:
        $fred_user->delete;

This can also be done as one statement, skipping the extra temporary variable, if it is not needed later:

    $schema->resultset('User')->find({ id => 1 })->delete;

In the first variant, the $fred_user row object will still contain the last known contents of Fred's data. A call to $fred_user->in_storage will return false (0), showing that the row object is no longer connected to a actual database row.

Delete one or more rows based on a WHERE clause

    DELETE FROM posts
    WHERE created_date <= '2000-01-01';

Use a ResultSet to define the WHERE clause using search, then call the delete method on it directly.

1. Create a Schema object representing the database you are working with:
        my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
2. Call the search method on the resultset for the ResultSource you wish to delete:
        my $old_posts = $schema->resultset('Post')->search({ 
          created_date => { '<=' => '2000-01-01' },
        });
3. Call delete on the row object:
        $old_posts->delete;

Unlike the single row deletion above, the contents of the rows to be deleted are never fetched from the database, so no record of them now remains.

NOTE: Calling delete on a ResultSet object will not run any overridden delete methods in your Result Classes or any loaded Components. To force these to run, call delete_all instead:

    $old_posts->delete_all();

This will also issue a separate delete statement for each row to be removed.

Cascading deletes

    DELETE FROM users
    WHERE id = 1;
    
    DELETE FROM comments
    WHERE user_id = 1;

Cascading deletes ensure the integrity of your data, if a User row is removed, then any items belonging to that user (for example comments created by the user), should also be removed.

NOTE: This is a rather drastic action, to prevent problems in your application, consider de-activating accounts instead of removing them!

For the time being DBIx::Class defaults to cascade deletion for the following types of relationships: has_many, has_one, might_have. That is, it will automatically issue the above statements. It is recommended not to rely on this implicit behavior, as it will be deprecated in a later version of DBIC. Instead declare proper cascading constraints in your RDBMS as described in "Table creation with references" in DBIx::Class::Manual::SQLHackers::CREATE.

If your database is already properly set up to cascade deletes for you, you can noop DBIx::Class' extra cascading statements:

    __PACKAGE__->has_many('posts', 
                          'MyDatabase::Schema::Result::Post', 
                          'user_id',
                          { cascade_delete => 0 });