Chapter 4
Creating a Database with SQL
IN THIS CHAPTER
Building tables
Setting constraints
Establishing relationships between tables
Altering table structure
Deleting tables
As I stated way back in Book 1, Chapter 5, SQL is functionally divided into three components: the Data Definition Language (DDL), the Data Manipulation Language (DML), and the Data Control Language (DCL). The DDL consists of three statements: CREATE, ALTER, and DROP. You can use these statements to create database objects (such as tables), change the structure of an existing object, or delete an object. After you have designed a database, the first step in bringing it into reality is to build a table with the help of the DDL. After you have built the tables, the next step is to fill them with data. That’s the job of the DML. As for the DCL, you call on it to help you preserve data integrity. In this chapter, I discuss the functions of the DDL. The aspects of the DML that were not covered in Book 1 — namely queries — will be discussed in Book 3. I discuss the DCL in Book 4.
First Things First: Planning Your Database
Before you can start constructing a database, you need to have a clear idea of the real-world or conceptual system that you are modeling. Some aspects of the system are of primary importance. Other aspects are subsidiary to the ones you have identified as primary. Additional aspects may not be important at all, depending on what you are using the database for. Based on these considerations, you’ll build an ER model of the system, with primary aspects identified as entities and subsidiary aspects identified as attributes of those entities. Unimportant aspects don’t appear in the model at all.
After you have finalized your ER model, you can translate it into a normalized relational model. The relational model is your guide for creating database tables and establishing the relationships between them.
Building Tables
The fundamental object in a relational database is the table. Tables correspond directly to the relations in a normalized relational model. Table creation can be simple or quite involved. In either case, it is accomplished with a CREATE TABLE statement.
In Chapter 3 of this minibook, I take you through the creation of a relational model for Honest Abe’s Fleet Auto Repair. Using that sample design, you can take it to the next level by creating database tables based on the model. Table 4-1 shows the tables (and their attributes) that correspond to the relational model I came up with for Ol’ Honest Abe.
TABLE 4-1 Tables for Honest Abe
Table |
Column |
CUSTOMER |
CustomerID CustomerName StreetAddr City State PostalCode ContactName ContactPhone ContactEmail |
MECHANIC |
EmployeeID FirstName LastName StreetAddr City State PostalCode JobTitle |
CERTIFICATION |
CertificationNo CertName Expires |
INVOICE |
InvoiceNo Date CustomerID EmployeeID Tax TotalCharge |
INVOICE_LINE |
Invoice_Line_No PartNo UnitPrice Quantity Extended Price LaborChargeCode |
LABOR |
LaborChargeCode TaskDescription StandardCharge |
PART |
PartNo Name Description CostBasis ListPrice QuantityInStock |
SUPPLIER |
SupplierID SupplierName StreetAddr City State PostalCode ContactName ContactPhone ContactEmail |
SUPPLIER_PART |
SupplierID PartNo |
You can construct the DDL statements required to build the database tables directly from the enumeration of tables and columns in Table 4-1, but first you should understand the important topic of keys, which I discuss in the next section.
Locating table rows with keys
Keys are the main tool used to locate specific rows within a table. Without a key — that handy item that guarantees that a row in a table is not a duplicate of any other row in the table — ambiguities can arise. The row you want to retrieve may be indistinguishable from one or more other rows in the table, meaning you wouldn’t be able to tell which one was the right one.
There are several different terms you may see in discussions of keys that you can use to uniquely identify rows in a table:
- Candidate key: Ideally, at least one column or combination of columns within a table contains a unique entry in every row. Any such column or combination of columns is a candidate key. Perhaps your table has more than one such candidate. If your table has multiple candidate keys, select one of them to be the table’s primary key.
- The primary key: A table’s primary key has the characteristic of being a unique identifier of all the rows in the table. It is specifically chosen from among the candidate keys to serve as the primary identifier of table rows.
- Composite key: Sometimes no single column uniquely identifies every row in a table, but a combination of two or more columns does. Together, those columns comprise a composite key, which can collectively serve as a table’s primary key.
Using the CREATE TABLE statement
Once you understand the function of keys (see the preceding bulleted list), you can create tables using the CREATE TABLE statement. Whatever database development environment you are using will have a facility that enables you to enter SQL code. This is an alternative to using the form-based tools that the environment also provides. In general, it is a lot easier to use the provided form-based tool, but using SQL gives you the finest control over what you are doing. The code examples that follow are written in ISO/IEC standard SQL. That means they should run without problems, regardless of the development environment you are using. However, because no implementation conforms to the standard 100 percent, you may have to consult your documentation if the tables are not created as you expect them to be.
CREATE TABLE CUSTOMER (
CustomerID INTEGER PRIMARY KEY,
CustomerName CHAR (30),
StreetAddr CHAR (30),
City CHAR (25),
State CHAR (2),
PostalCode CHAR (10),
ContactName CHAR (30),
ContactPhone CHAR (13),
ContactEmail CHAR (30) ) ;
CREATE TABLE MECHANIC (
EmployeeID INTEGER PRIMARY KEY,
FirstName CHAR (15),
LastName CHAR (20),
StreetAddr CHAR (30),
City CHAR (25),
State CHAR (2),
PostalCode CHAR (10),
JobTitle CHAR (30) ) ;
CREATE TABLE CERTIFICATION (
CertificationNo INTEGER PRIMARY KEY,
CertName CHAR (30),
Expires Date ) ;
CREATE TABLE INVOICE (
InvoiceNo INTEGER PRIMARY KEY,
Date DATE,
CustomerID INTEGER,
EmployeeID INTEGER,
Tax NUMERIC (9,2),
TotalCharge NUMERIC (9,2) ) ;
CREATE TABLE INVOICE_LINE (
Invoice_Line_No INTEGER PRIMARY KEY,
PartNo INTEGER,
UnitPrice NUMERIC (9,2),
Quantity INTEGER,
ExtendedPrice NUMERIC (9,2),
LaborChargeCode INTEGER ) ;
CREATE TABLE LABOR (
LaborChargeCode INTEGER PRIMARY KEY,
TaskDescription CHAR (40),
StandardCharge NUMERIC (9,2) ) ;
CREATE TABLE PART (
PartNo INTEGER PRIMARY KEY,
Name CHAR (30),
Description CHAR (40),
CostBasis NUMERIC (9,2),
ListPrice NUMERIC (9,2),
QuantityInStock INTEGER ) ;
CREATE TABLE SUPPLIER (
SupplierID INTEGER PRIMARY KEY,
SupplierName CHAR (30),
StreetAddr CHAR (30),
City CHAR (25),
State CHAR (2),
PostalCode CHAR (10),
ContactName CHAR (30),
ContactPhone CHAR (13),
ContactEmail CHAR (30) ) ;
CREATE TABLE SUPPLIER_PART (
SupplierID INTEGER,
PartNo INTEGER,
UNIQUE (SupplierID, PartNo) ) ;
All the tables except SUPPLIER_PART have a single attribute as their primary key. In the SUPPLIER_PART table, no single attribute uniquely identifies a row, so the table has a composite key made up of both SupplierID and PartNo. (That’s the UNIQUE (SupplierID, PartNo) business.) Those two attributes together do uniquely identify each row in the table. Not all suppliers supply all parts, but there is a row in SUPPLIER_PART for every case where a specific supplier supplies a specific part. The UNIQUE constraint guarantees that no two rows in SUPPLIER_PART are identical.
Setting Constraints
One way to protect the integrity of your data is to add constraints to your table definitions. There are several different kinds of constraints, including column constraints, table constraints, check constraints, and foreign key constraints. In this section, I cover column constraints and table constraints. Other types of constraints will pop up here and there in the book as I go along.
Column constraints
Column constraints determine what may or may not appear in a column of a table. For example, in the SUPPLIER_PART table, NOT NULL is a constraint on the SupplierID column. It guarantees that the SupplierID column must contain a value. It doesn’t say what that value must be, as long as it is some value.
Table constraints
A table constraint is not restricted to a particular column, but applies to an entire table. The PRIMARY KEY constraint is an example of a table constraint. A primary key may consist of one column, multiple columns, or even all the columns in the table — whatever it takes to uniquely identify every row in the table. Regardless of how many columns are included in the primary key, the primary key is a characteristic of the entire table.
Keys and Indexes
Because primary keys uniquely identify each row in a table, they are ideal for indexes. The purpose of an index is to point to a row or set of rows that satisfies a condition. Because a primary key identifies one and only one row in a table, an index on a table’s primary key provides the fastest, most direct access to the row it points to. Less selective indexes give access to multiple rows that all satisfy the selection condition. Thus, although CustomerID may take you directly to the record of the customer you want, you may not remember every customer’s CustomerID. A search on LastName might return several records, but you can probably determine pretty quickly which one is the one you want. In such a case, you may want to create an index on the LastName column as well as on CustomerID. Any column that you frequently use as a retrieval condition should probably be indexed. If a table’s primary key is a composite key, the index would be on the combination of all the columns that make up the key. Composite keys that are not a table’s primary key can also be indexed. (I talk about creating indexes in Chapter 3 of this minibook.)
Ensuring Data Validity with Domains
Although you, as a database creator, can’t guarantee that the data entry operator always enters the correct data, at least you can ensure that the data entered is valid — that it excludes values that cannot possibly be correct. Do this with a CREATE DOMAIN statement. For example, in the LABOR table definition given in the earlier “Using the CREATE TABLE statement” section, the StandardCharge field holds currency values of the NUMERIC type. Suppose you want to ensure that a negative value is never entered for a StandardCharge. You can do so by creating a domain, as in the following example:
CREATE DOMAIN CurrencyDom NUMERIC (9,2)
CHECK (VALUE >= 0);
You should now delete the old LABOR table and redefine it as shown below:
CREATE TABLE LABOR (
LaborChargeCode INTEGER PRIMARY KEY,
TaskDescription CHAR (40),
StandardCharge CurrencyDom ) ;
The data type of StandardCharge is replaced by the new domain. With a domain, you can constrain an attribute to assume only those values that are valid.
Establishing Relationships between Tables
After you have created tables for a database, the next step is to establish the relationships between the tables. A normalized relational database has multiple tables, perhaps hundreds of them. Most queries or reports require data from more than one table. To pull the correct data from the tables, you must have a way of relating the rows in one table to corresponding rows in another table. This is accomplished with links consisting of columns in one table that correspond to columns in a related table.
Earlier in this chapter, I talk about primary keys and composite keys (which can be primary keys). Another important kind of key is the foreign key. Unlike primary keys, foreign keys do not uniquely identify a row in a table. Instead, they serve as links to other tables.
Relational databases are characterized by having multiple tables that are related to each other. Those relationships are established by columns that are shared between two tables. In a one-to-one relationship, one row in the first table corresponds to one and only one row in the second table. For a given row, one or more columns in the first table match a corresponding column or set of columns in the second table. In a one-to-many relationship, one row in the first table matches multiple rows in the second table. Once again, the match is made by columns in the first table that correspond to columns in the second table.
Consider the Honest Abe sample database in the previous chapter. It has a one-to-many link between CUSTOMER and INVOICE, mediated by the shared CustomerID column, and also a one-to-many link between MECHANIC and INVOICE mediated by the EmployeeID column. To create these links, you have to add a little more SQL code to the definition of the INVOICE table. Here’s the new definition:
CREATE TABLE INVOICE (
InvoiceNo INTEGER PRIMARY KEY,
Date DATE,
CustomerID INTEGER,
EmployeeID INTEGER,
CONSTRAINT CustFK FOREIGN KEY (CustomerID)
REFERENCES CUSTOMER (CustomerID),
CONSTRAINT MechFK FOREIGN KEY (EmployeeID)
REFERENCES MECHANIC (EmployeeID)
) ;
Adding the foreign key constraints to the table on the many side of a one-to-many relationship creates the links. For a one-to-one relationship, it doesn’t matter which of the two tables you add the foreign key constraint to.
To tie the Honest Abe database together, add foreign key constraints to establish all the relationships. Here’s the result:
CREATE TABLE CUSTOMER (
CustomerID INTEGER PRIMARY KEY,
CustomerName CHAR (30),
StreetAddr CHAR (30),
City CHAR (25),
State CHAR (2),
PostalCode CHAR (10),
ContactName CHAR (30),
ContactPhone CHAR (13),
ContactEmail CHAR (30) ) ;
CREATE TABLE MECHANIC (
EmployeeID INTEGER PRIMARY KEY,
FirstName CHAR (15),
LastName CHAR (20),
StreetAddr CHAR (30),
City CHAR (25),
State CHAR (2),
PostalCode CHAR (10),
Specialty CHAR (30),
JobTitle CHAR (30) ) ;
CREATE TABLE CERTIFICATION (
CertificationNo INTEGER PRIMARY KEY,
CertName CHAR (30),
MechanicID INTEGER,
Expires Date,
CONSTRAINT CertMechFK FOREIGN KEY (MechanicID)
REFERENCES MECHANIC (EmployeeID)
) ;
CREATE TABLE INVOICE (
InvoiceNo INTEGER PRIMARY KEY,
Date DATE,
CustomerID INTEGER,
EmployeeID INTEGER,
Tax NUMERIC (9,2),
TotalCharge NUMERIC (9,2),
CONSTRAINT CustFK FOREIGN KEY (CustomerID)
REFERENCES CUSTOMER (CustomerID),
CONSTRAINT MechFK FOREIGN KEY (EmployeeID)
REFERENCES MECHANIC (EmployeeID)
) ;
CREATE TABLE INVOICE_LINE (
Invoice_Line_No INTEGER PRIMARY KEY,
InvoiceNo INTEGER,
LaborChargeCode INTEGER,
PartNo INTEGER,
UnitPrice NUMERIC (9,2),
Quantity INTEGER,
ExtendedPrice NUMERIC (9,2),
LaborChargeCode INTEGER,
CONSTRAINT InvFK FOREIGN KEY (InvoiceNo)
REFERENCES INVOICE (InvoiceNo),
CONSTRAINT LaborFK FOREIGN KEY (LaborChargeCode)
REFERENCES LABOR (LaborChargeCode),
CONSTRAINT PartFK FOREIGN KEY (PartNo)
REFERENCES PART (PartNo)
) ;
CREATE DOMAIN CurrencyDom NUMERIC (9,2)
CHECK (VALUE >= 0);
CREATE TABLE LABOR (
LaborChargeCode INTEGER PRIMARY KEY,
TaskDescription CHAR (40),
StandardCharge CurrencyDom ) ;
CREATE TABLE PART (
PartNo INTEGER PRIMARY KEY,
Name CHAR (30),
Description CHAR (40),
CostBasis NUMERIC (9,2),
ListPrice NUMERIC (9,2),
QuantityInStock INTEGER ) ;
CREATE TABLE SUPPLIER (
SupplierID INTEGER PRIMARY KEY,
SupplierName CHAR (30),
StreetAddr CHAR (30),
City CHAR (25),
State CHAR (2),
PostalCode CHAR (10),
ContactName CHAR (30),
ContactPhone CHAR (13),
ContactEmail CHAR (30) ) ;
CREATE TABLE SUPPLIER_PART (
SupplierID INTEGER NOT NULL,
PartNo INTEGER NOT NULL,
CONSTRAINT SuppFK FOREIGN KEY (SupplierID)
REFERENCES SUPPLIER (SupplierID),
CONSTRAINT PartSuppFK FOREIGN KEY (PartNo)
REFERENCES PART (PartNo)
) ;
Foreign key constraints need to be added to only one side of a relationship. In a one-to-many relationship, they are added to the many side.
Note that the CERTIFICATION table has a column named MechanicID, which corresponds to the column named EmployeeID in the MECHANIC table. This is to show that a foreign key need not have the same name as the corresponding column in the table that it links to. Note also that additional columns that serve as foreign keys have been added to some of the tables on the many sides of relationships. These are required in addition to the constraint clauses.
A database properly linked together using foreign keys is said to have referential integrity. The key to assuring referential integrity is to make sure that the ER diagram of the database is accurate and properly translated into a relational model, which is then converted into a relational database.
Altering Table Structure
In the real world, requirements tend to change. Sooner or later, this is bound to affect the databases that model some aspect of that world. SQL’s Data Definition Language provides a means to change the structure of a database that has already been created. Structural changes can involve adding a new column to a table or deleting an existing one. The SQL to perform these tasks is pretty straightforward. Here is an example of adding a column:
ALTER TABLE MECHANIC
ADD COLUMN Birthday DATE ;
Here’s an example of deleting a column:
ALTER TABLE MECHANIC
DROP COLUMN Birthday ;
I guess Honest Abe decided not to keep track of employee birthdays after all.
Deleting Tables
It’s just as easy to delete an entire table as it is to delete a column in a table. Here’s how:
DROP TABLE CUSTOMER ;
Uh-oh. Be really careful about dropping tables. When it’s gone, it’s gone, along with all its data. Because of this danger, sometimes a DBMS will not allow you to drop a table. If this happens, check to see whether a referential integrity constraint is preventing the drop operation. When two tables are linked with a primary key/foreign key relationship, you may be prevented from deleting the table on the primary key side, unless you first break that link by deleting the table on the foreign key side.