Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

Is there any way to omit some records in mysql select statement and not deleting them? We can easily add a column for example deleted and set it to 1 for deleted ones and keep them but the problem is that we have to put where deleted = 1 in all queries. What is the best way to keep some records as an archive?

share|improve this question

1 Answer

up vote 2 down vote

You basically have two choices. You could use a view to do the filtering though this has some performance issues on MySQL, or you could use a trigger to copy deleted rows into an archive table. I suspect that on MySQL, the trigger solution will be much better (on other DB's the tradeoffs may go other ways.

share|improve this answer
Chris Travers: In View mode we will have 2 problems : first we have to make a view for each table and second is Inserts with duplicate key while you can not insert in to a view! and For Triggers , all your foreign keys will fail and again you have to make duplicate table for each table. – Abadis Feb 25 at 11:00
1  
Yes, these are problems. The other solution is just to add "and not deleted" to every where clause. There is no free lunch. – Chris Travers Feb 25 at 11:41
I think mysql and other db engines must do sth about this! – Abadis Feb 25 at 13:41
1  
You could probably repurpose Oracle's row-level security to do something like it.... – Chris Travers Feb 25 at 14:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.