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.
Sometimes we may decide that we need to get rid of a table in the database for some reason. In fact, it would be problematic if we cannot do so because this could create a maintenance nightmare for the DBAs. Fortunately, SQL allows us to do it, as we can use the DROP TABLE command. The syntax for DROP TABLE is
DROP TABLE "table_name"
So, if we wanted to drop the table called a customer that we created in the last section, we simply type
Sometimes we wish to get rid of all the data in a table. One way of doing this is with DROP TABLE, which we saw in the last section. But what if we wish to simply get rid of the data but not the table itself? For this, we can use TRUNCATE TABLE command. The syntax for TRUNCATE TABLE is
TRUNCATE TABLE "table_name"
So, if we wanted to truncate the table called a customer that we created in SQL CREATE, we simply type