Introduction:
We will now discuss some more advanced features of SQL that simplify management and prevent loss or corruption of your data. We will discuss some PostgreSQL extensions aswell.
Some examples from this chapter can also be found in advanced.sql in the tutorial directory. This file also contains some sample data to load.
Views:
Suppose the combined listing of weather records and city location
is of particular interest to your application, but you do not want to type the query each time you need
it. You can create a view over the query, which gives a name to the query that you can refer to like an
ordinary table.
Views allow you to encapsulate the details of the structure of your tables, Making liberal use of views is a key aspect of good SQL database design.
Views can be used in almost any place a real table can be used. Building views upon other views is not uncommon.
Foreign Keys:
You want to make sure that no one can insert rows in the weather table that do not have a matching entry in
the cities table. This is called maintaining the referential integrity of your data. In simplistic database
systems this would be implemented (if at all) by first looking at the cities table to check if a matching
record exists, and then inserting or rejecting the new weather records.
Transactions:
It is a fundamental concept of all database systems. The essential point of a transaction is that it bundles multiple steps into a single, all-or-nothing operation. The intermediate states between the steps
are not visible to other concurrent transactions, and if some failure occurs that prevents the transaction
from completing, then none of the steps affect the database at all.
When the transaction is done and acknowledged by the database system, it has been permanently recorded and not being lost if ever crash in system. Another important property of transactional databases is closely related to the notion of atomic updates. when multiple transactions are running concurrently, each one should not be able to see the incomplete changes made by others.
In PostgreSQL, a transaction is set up by surrounding the SQL commands of the transaction with BEGIN and COMMIT commands.
If partway through the transaction, we decide we do not want to commit we can issue the command ROLLBACK instead of COMMIT, and all our updates so far will be canceled.
Top comments (0)