Making a row inactive in MySQL

Question

Is it possible to make a row in MySQL inactive? So this row isn't used in the results of queries anymore? My client wants to keep the deleted members exists in the database, but I don't want to edit all the queries to check if the member is deleted or not.

Or is there an easy way to move the entire row data into another "inactive" table?

Answer 1

You could rename the current table, create the 'deleted' column in it, and then create a view with the same name as the current table, selecting all where deleted=0. That way you don't have to change all your queries. The view will be updateable provided you supply a default for the delete column. _

CREATE TABLE my_new_table (col1    INTEGER,
                           col2    INTEGER,
                           col3    INTEGER,
                           deleted INTEGER NOT NULL DEFAULT 0);

INSERT INTO my_new_table (col1, col2, col3)
    SELECT (col1, col2, col3)
        FROM my_table;

DROP TABLE my_table;

CREATE VIEW my_table (col1, col2, col3)
    AS SELECT (col1, col2, col3)
           FROM my_new_table
           WHERE deleted = 0;

Answer 2

What you describe is usually called a soft delete.

Moving rows between different tables is rarely a good idea in relational databases. In general, you shouldn't be moving records around simply because some attribute about them has changed (and "inactivity" is just an attribute in this case).

I would add an inactive field in the table, setting it to 0 if the row is active, and to 1 if inactive. However you would have to filter out inactive rows from all your queries by adding WHERE inactive = 0 in your WHERE clauses. An alternative to this would be to use a view, as @Brian suggested in the other answer, which I recommend.