Hi everyone!
Bit of a weird question here, I’m not struggling with any code…but I don’t understand the point of Views in mySQL. I can understand that they are helpful for protecting the data in the original tables, but in the lecture section on them, Mosh carries out a pretty simple and useful query where he updates the amount of an invoice that has been paid…and mySQL just deletes the whole row of information in response! How is that useful?
Then Mosh goes on to explain that WITH CHECK OPTION prevents this from happening so I thought Oh great so you can do useful queries on Views tables…but actually it just means you can’t carry out the query at all! I thought it would mean you could do it without deleting your data.
Can anyone explain why mySQL does this? And why you would use a View if you can’t carry out simple queries on it?
There’s nothing deleted. The view is called invoices_with_balance and should give us invoices with a balance (or “open items”). That’s why we applied a WHERE clause to it’s SELECT statement achieving exactly that.
If we now update the payment_total to equal the invoice_total the invoice is fully paid and no longer an open item so it disappears from the invoices_with_balance view. But of course the row is still there in the invoices table and not deleted.
If we had another view invoices_history with a WHERE clause invoice_total - payment_total = 0 the invoice that disappeared from the invoices_with_balance view after the UPDATE would automagically appear there. So from the perspective of a view user the invoice is moved from one view to another while the underlying row in the invoices table stays where it is.
Since real world database designs can be quite complex, views can e.g. provide easy to grasp data sources for non-tech people to quickly calculate the figures they need with a spread sheet or BI tool.