Make a row inactive in MySQL

Question

Is it possible to make a row in MySQL inactive? So, this line is no longer used in query results? My client wants to keep deleted items in the database, but I don’t want to edit all the queries to check if the item was deleted or not.

Or is there an easy way to move all row data to another "inactive" table?

Answer 1

You can rename the current table, create a “deleted” column in it, and then create a view with the same name as the current table, selecting everything where deleted = 0. Thus, you do not have to change all your queries. The view will be updatable if you specify a default value 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 soft deletion .

Moving rows between different tables is rarely a good idea in relational databases. In general, you should not move records simply because some attribute about them has changed (and "inaction" is just an attribute in this case).

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

Answer 3

You could, as you suggest, copy them to a new table with the same structure when you delete

Answer 4

Turning on the start 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 named users_archive.

hope this helps.