SQL constraints, views and transactions

13 Oct 2019

Notes based on Head First SQL

Check constraints

A CHECK constraint restricts what values you can insert into a column. It uses the same conditionals as a WHERE clause. Constraints are added when we create a table. Some of the constraints you've already seen include NOT NULL, PRIMARY KEY, FOREIGN KEY and UNIQUE.

There is another column constraint called CHECK.


CREATE TABLE piggy_bank
(
  id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
  coin CHAR(1) CHECK (coin IN ('P', 'N', 'D', 'Q'))
)

In the query above, we are restricting the values that we can enter into our piggy bank to 'P' for pennies, 'N' for nickles, 'D' for dimes and 'Q' to stand for quarters.

If you forget to add constraints, you can use an ALTER statement to add them in later:


ALTER TABLE my_contacts
ADD CONSTRAINT CHECK gender IN ('M', 'F');

You can use all the conditions AND, OR, IN, NOT, BETWEEN and others just like you can when you use WHERE clauses.

Views

Views are a way for us to save queries that we use regularly inside of our database. We could save them into a text file and copy and past them, but these can be deleted or overwritten accidentally.

A view is basically a table that only exists when you use the view in a query. It's considered a virtual table because it acts like a tabel, and the same operations that can be performed on a table that can be performed on a view.

However, the virtual table doesn't stay in the database. It gets created when we use the view ant then deleted. The advantage of this is that each time new rows are inserted into the database, when you use the view then it will show you all of the new information.

To create a view, we can add a CREATE VIEW statement to our query.


CREATE VIEW web_designers AS
SELECT mc.first_name, mc.last_name, mc.phone, mc.email
FROM my_contacts mc
NATURAL JOIN job_desired jd
WHERE jd.title = 'Web Designer';

To see your views, we can treat it as though it was a table and see it's results.

SELECT * FROM web_designers;

This query is acting like a subquery, we are actually saying 'select everything from the subquery that returns the first name, last name, phone, and email of all the people from my_contacts who are looking for a job as a web designer.

Benefits of views

When you are finished with your view, you can delete it with the following DROP command: DROP VIEW web_designers;

Transactions

Imagine you are at an ATM and you want to transfer $1000 to your savings account. The database deletes the value from your checking account table, and just as it's about to insert your money into the savings table, the power cuts out. When you turn it on again, it has forgotten all about the transaction. It deleted your money, and you can't get it back.

A different scenario. Imagine you share a bank account with someone. You are both on a night out, and there is $400 in the account. At exactly the same time, you withdraw $350. The database first checks to see if that amount is available to be withdrawn. As you are both taking it out at the same time, in both cases it says there is enough money. Then it gives you both $350 dollars each. After the transactions are completed, the account is now overdrawn by $300. Not good.

SQL transactions are a way around this problem. During a transaction, if ALL the steps can't be completed without interference, then NONE of them should be completed.

ACID test

There are four characteristics that have to be true before we can call a set of SQL statements a transaction:

Transaction statements in SQL

It's best to save transactions for when you really need to be able to undo what you're doing to avoid wasting space and making your database have to work harder than necessary to keep track of what you have done.

Before you can use a transaction with MySQL, you need to use the correct storage engine, which is a behind the scenes structure that stores all your database data and structures. Some allow transactions, some do not.

When you type in SHOW CREATE TABLE table_name;, you will be able to see the type of engine you are using. It will look something like this: ENGINE=MyISAM DEFAULT CHARSET=latin1. You need to make sure that your storage engine is either BDB or InnoDB, which are the two choices that support transactions.

To change your engine, you can use the following syntax:

ALTER TABLE your_table TYPE = InnoDB;

Here is an example of a transaction in practice:


START TRANSACTION;
UPDATE piggy_bank set coin = "Q' where coin = "N"
AND coin_year > 1950;
ROLLBACK;