Views can be considered as virtual tables. Generally speaking, a table has a set of definition that sets the structure of the table, and physically stores the data. A view also has a set of definitions, which is built on top of the table(s) or another view (s), and it does not physically store the data.
The syntax for creating a view is as follows:
CREATE VIEW "VIEW_NAME" AS "SQL Statement"
“SQL Statement” can be any of the SQL statements we have discussed in this tutorial.
Indexes help us retrieve data from tables quicker. Let’s use an example to illustrate this point: Say we are interested in reading about how to grow peppers in a gardening book. Instead of reading the book from the beginning until we find a section on peppers, it is much quicker for us to go to the index section at the end of the book, locate which pages contain information on peppers, and then go to these pages directly. Going to the index first saves us time and is by far a more efficient method for locating the information we need.
Once a table is created in the database, there are many occasions where one may wish to change the structure of the table. Typical cases include the following:
Add a column
Drop a column
Change a column name
Change the data type for a column
Please note that the above is not an exhaustive list. There are other instances where ALTER TABLE is used to change the table structure, such as changing the primary key specification.
A primary key is used to uniquely identify each row on a table. It can either be part of the actual record itself, or it can be an artificial field (something that has nothing to do with the actual record). A primary key can consist of one or more fields on a table. When multiple fields are used as a primary key, it is called a composite key.
Primary keys can be specified either when the table is created (using CREATE TABLE) or by changing the existing table structure (using ALTER TABLE).
A foreign key is a field (or fields) that points to the primary key of another table. The purpose of the foreign key is to ensure referential integrity of the data. In other words, only values that are supposed to appear in the database are permitted.
For example, say we have two tables, a CUSTOMER table that includes all customer data, and an ORDER table that includes all customer orders. The constraint here is that all orders must be associated with a customer that is already in the CUSTOMER table. In this case, we will place a foreign key on the ORDERS table and have it relate to the primary key of the CUSTOMER table. This way, we can ensure that all orders in the ORDERS table are related to a customer in the CUSTOMER table. In other words, the ORDERS table cannot contain information on a customer that is not in the CUSTOMER table.