8

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?

Daniel Vassallo
  • 337,827
  • 72
  • 505
  • 443
Arjen
  • 163
  • 2
  • 6

4 Answers4

11

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;
Brian Hooper
  • 21,544
  • 24
  • 88
  • 139
  • +1 for the view. Could you put a link to the create view syntax, or a sample of how to do this so the OP knows where to go from here? – Konerak Jun 22 '10 at 09:39
  • But when I want to update some data in the member table, I can't use the view table to update this data, right? So I have to change all update query's to update the my_new_table (for this example)? – Arjen Jun 22 '10 at 14:22
  • 1
    Views defined like this should be updatable...try it! That is to say, INSERT INTO my_table (col1,col2,col3) values (1,2,3); and UPDATE my_table SET col1 = 1 WHERE col2 = 5; should all work when my_table is defined as a view like that. – Brian Hooper Jun 22 '10 at 15:47
6

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.

Community
  • 1
  • 1
Daniel Vassallo
  • 337,827
  • 72
  • 505
  • 443
  • "but I don't want to edit all query's[sic] to check if the member is deleted or not" – Ignacio Vazquez-Abrams Jun 22 '10 at 08:06
  • How did I miss that? :) ... (Updated my answer) – Daniel Vassallo Jun 22 '10 at 08:07
  • How did you update your answer so he no longer has to edit all queries? In revision 3, you still mention 'you must filter out inactive rows from all your queries...'. – Konerak Jun 22 '10 at 08:59
  • 1
    An option to not change the queries would be to work with a VIEW that has the WHERE clause in its definition. – Konerak Jun 22 '10 at 08:59
  • @Konerak: Yes, I agree with the view method. In fact I +1ed Brian's answer... As for the answer update, my first version simply suggested to add an `inactive` field. I totally didn't notice that the OP was aware of this. (this happened before the 5 minutes grace period, so it does not appear in the revision history of the answer). – Daniel Vassallo Jun 22 '10 at 09:15
  • Using a view would still require editing all the queries though, wouldn't it? But yes, a view would be the way to go I'd say :) – Svish Jun 22 '10 at 09:21
  • @Svish: I think using a view the way [@Brian suggested](http://stackoverflow.com/questions/3091293/making-a-row-inactive-in-mysql/3091536#3091536) would avoid the OP having to change all the queries. That is, renaming the table, creating a view with the name of the table, and supplying a default to the "inactive" field. – Daniel Vassallo Jun 22 '10 at 09:33
  • 1
    @Daniel: Aah, smart. Didn't think of that :) – Svish Jun 22 '10 at 10:32
2

You could as you suggest yourself copy them to a new table with the same structure on deletion

Redlab
  • 3,110
  • 19
  • 17
0

An On delete trigger is what you need.

Usage example :

CREATE TRIGGER Users_archiver
AFTER delete ON users
FOR EACH ROW
BEGIN
insert into users_archive values(old.id,old.username,old.first_name,
old.last_name,old.password);
 END$$
delimiter ;

This will save you a copy of the deleted row in a second table called users_archive.

hope this helps.

Youssef
  • 1,310
  • 1
  • 14
  • 24