Understanding the Basic Syntax

The basic syntax for creating tables is simple:

CREATE TABLE <table name>
   (<column name> <column data type> [<column constraints>]);

Deleting database tables is even simpler:

DROP TABLE <table name>;

Tables being linked by FOREIGN KEY constraints can't be dropped. The constraints need to be dropped before dropping the table. You'll learn more about FOREIGN KEY constraints later in this chapter, but for now it's enough to know they're the means to enforce table relationships. You need to be the owner of the table or have administrative rights in order to be allowed to drop the table.

In MySQL, dropping a table can't be undone. However, if a table is dropped inside a SQL Server, Oracle, or DB2 transaction, the table will be re-created if you roll back the transaction.

Using SQL Server, Oracle, or MySQL, you can also truncate a table instead of dropping it. This means that all the records in the table are deleted (the table itself isn't dropped), but the operation isn't logged in the transaction log. In other words, truncation works just like a fast DELETE that clears the entire table but can't be rolled back.

Because it isn't a logged operation, truncating a table also doesn't fire any triggers that would normally be executed when deleting rows from the table (you'll learn about triggers in the next chapter). When a table is truncated, the identity values used for auto-increment are also reset to their default values.

With MySQL, TRUNCATE drops the table and re-creates it again, thus deleting all its rows much faster than a DELETE command. Even though the internal procedure might be different for the other databases systems, truncating works just like dropping the table and creating it again.

The syntax of TRUNCATE is simple:

TRUNCATE TABLE <table name>;

Creating Simple Tables

If you connect to your favorite database, you can create a table called Friend using this syntax:

CREATE TABLE Friend (Name VARCHAR(50), PhoneNo VARCHAR(15));

By default, there are no constraints defined when a new table is created, and the columns accept NULL values. So, all of the following commands will execute successfully:

INSERT INTO Friend (Name, PhoneNo) VALUES ('John Doe', '555 2323');
INSERT INTO Friend (Name) VALUES ('John Doe');
INSERT INTO Friend (Name, PhoneNo) VALUES ('John Doe', NULL);

Executing these commands will populate the Friend table as shown in Table 12-1.

The second two INSERT statements are only allowed because NULLs are accepted by default, so NULLs are allowed for PhoneNo. They would also be allowed for Name, so you could have a statement like this:

INSERT INTO Friend (PhoneNo) VALUES ('123 4567');

and end up with a record like that shown in Table 12-2.

Disallowing NULLs

If you don't want to accept NULL values in a column, you should append NOT NULL to the definition. To test this, let's first drop the Friend table:

DROP TABLE Friend;

Now let's re-create the Friend table, but this time you won't allow NULL values for Name or PhoneNo:

CREATE TABLE Friend (Name VARCHAR(50) NOT NULL,
                     PhoneNo VARCHAR(15) NOT NULL);

After creating the new table, let's do the little test again:

INSERT INTO Friend (Name, PhoneNo)
       VALUES ('John Doe', '555 2323');
INSERT INTO Friend (Name) VALUES ('John Doe');
INSERT INTO Friend (Name, PhoneNo) VALUES ('John Doe', NULL);

If you execute these three statements, you'll probably be surprised to find out that your databases behave differently:

• The first statement executes just fine on all database systems.

• The second statement doesn't specify a value for PhoneNo. SQL Server, DB2, Access, and Oracle generate an error because a value must be specified for NOT NULL columns. MySQL, on the other hand, breaks the rules a little bit (even when running in ANSI mode) and executes the statement by adding an empty string for PhoneNo. In other words, MySQL tries to second-guess what you mean.

• The third statement is rejected by all databases because you specifically try to insert NULL for PhoneNo.

Specifying Default Values

Let's suppose the following: If no phone number is specified when adding new rows, you want the database to add the value 'Unknown phone' for that column. How can you do that? First, once again, drop the Friend table:

DROP TABLE Friend;

Second, re-create it with the following statement:

CREATE TABLE Friend (
   Name   VARCHAR(50) NOT NULL,
   PhoneNo VARCHAR(15) DEFAULT 'Unknown Phone' NOT NULL);

Now let's test again with your three friends:

INSERT INTO Friend (Name, PhoneNo) VALUES ('John Doe', '555 2323');
INSERT INTO Friend (Name) VALUES ('John Doe');
INSERT INTO Friend (Name, PhoneNo) VALUES ('John Doe', NULL);

The first statement succeeds, and the specified values are inserted in the table. The second also succeeds, and 'Unknown Phone' is supplied by default for PhoneNo because no value was explicitly provided. The third statement will fail because you tried to insert NULL into a column that doesn't support NULLs.

In SQL Server, Oracle, and DB2, you can use functions or system variables instead of fixed values to provide the default value for a column. The typical example is to use a function or variable that returns the current date and time, such as GETDATE() for SQL Server, CURRENT_DATE for DB2, or SYSDATE for Oracle. Newer versions of Oracle also support CURRENT_DATE, which performs the same as SYSDATE. For example:

-- Oracle
CREATE TABLE LibraryLoans (
   BookID       INT  NOT NULL,
   CustomerID   INT  NOT NULL,
   DateBorrowed DATE DEFAULT SYSDATE NOT NULL);
-- DB2
CREATE TABLE LibraryLoans (
   BookID       INT  NOT NULL,
   CustomerID   INT  NOT NULL,
   DateBorrowed DATE DEFAULT CURRENT_DATE NOT NULL);
-- SQL Server
CREATE TABLE LibraryLoans (
   BookID       INT      NOT NULL,
   CustomerID   INT      NOT NULL,
   DateBorrowed DATETIME DEFAULT GETDATE() NOT NULL);

Setting the Primary Key

So far, the Friend table has a significant disadvantage: It doesn't have a primary key. As you saw in Chapter 1, "Understanding SQL and Relational Databases," every table in a relational database should have a primary key so that each record can be uniquely identified by the value in one (or a group) of its columns. There are no exceptions to this rule in a relational database.

For the purposes of this exercise, you won't add an additional column (such as FriendID) as the primary key. Instead, you want each friend of yours to have unique names, so set Name as the primary key.

Remember that the primary key is a constraint that applies to table columns. The following command creates the Friend table and associates a PRIMARY KEY constraint named MyPrimaryKey to the Name field (remember to drop the Friend table before re-creating it):

CREATE TABLE Friend (
   Name    VARCHAR(50) NOT NULL,
   PhoneNo VARCHAR(15) DEFAULT 'Unknown Phone' NOT NULL,
   CONSTRAINT MyPrimaryKey PRIMARY KEY (Name));

If you aren't interested in supplying a name for the PRIMARY KEY constraint, you can use this syntax:

CREATE TABLE Friend (
   Name    VARCHAR(50) NOT NULL,
   PhoneNo VARCHAR(15) DEFAULT 'Unknown Phone' NOT NULL,
   PRIMARY KEY (Name));

Also, you can use this shorter form, which has the same effect:

CREATE TABLE Friend (
   Name    VARCHAR(50) PRIMARY KEY NOT NULL,
   PhoneNo VARCHAR(15) DEFAULT 'Unknown Phone' NOT NULL);

To test your newly created table, try to add a NULL for the name and then two identical names:

INSERT INTO Friend (PhoneNo) VALUES ('555 2323');
INSERT INTO Friend (Name, PhoneNo) VALUES ('John Doe', '12345678');
INSERT INTO Friend (Name, PhoneNo) VALUES ('John Doe', '87654321');

The first command fails on SQL Server, DB2, Access, and Oracle because you tried to insert NULL for the name; however, it works with MySQL, which breaks the rules (remember the earlier exercise) and automatically inserts an empty string instead of NULL.

The second statement works just fine, but the last one throws an error because you tried to insert a duplicate value on the primary key column. So, the database takes care of its integrity, just like you expected.

When a PRIMARY KEY constraint is defined, a unique index is automatically created for it. We'll discuss indexes later in this chapter.

Setting the UNIQUE Constraint

Apart from the primary key, you can have one or more unique columns. In this scenario, this can apply to the PhoneNo column; this, of course, isn't representative enough to be used as a primary key (and anyway you couldn't use it as a primary key because you can have only one primary key in the table, and that primary key is set on the Name column). However, you can mark it as unique to ensure you don't have more than one friend with the same phone number. Maybe this rule wouldn't be very good in a real-world example, but still it's an example of a rule that you can enforce within the database.

After dropping the current Friend table, create it like this:

CREATE TABLE Friend (
   Name    VARCHAR(50) PRIMARY KEY NOT NULL,
   PhoneNo VARCHAR(15) UNIQUE);

Now duplicate phone numbers will be rejected by the database because they violate the UNIQUE constraint. The UNIQUE constraint works fine when combined with DEFAULT values, but the default value can be added only once.

There's a difference in the way that NULL values are accepted by the different databases on columns having the UNIQUE constraint—a detail that may affect how you use it in real-world scenarios.

Oracle, MySQL, and Access allow you to have as many NULL values as you like in a unique column, as long as the column isn't set with NOT NULL. DB2 doesn't permit NULL values in a unique column at all.

SQL Server, on the other hand, permits only a single NULL value in a unique column. Although in SELECT queries a NULL value doesn't equal another NULL (as you learned in Chapter 1, "Understanding SQL and Relational Databases"), SQL Server regards two NULL values as identical with regard to the UNIQUE constraint.

Setting CHECK Constraints

CHECK constraints are the most flexible kind of constraints. They allow you to pose a wide range of restrictions on the rows you modify or insert into the database.

The main points to keep in mind about CHECK constraints are the following:

• The way you define a CHECK constraint is similar to the conditions you place in the WHERE clause, and they're evaluated at the time a new row is added to the table or an existing row is modified.

• CHECK constraints can contain in their definition a logical expression or a number of logical expressions combined with AND or OR, parentheses, and so on; the condition expressed by CHECK must evaluate to true or to false.

• A CHECK constraint can refer only to columns of the row being introduced or modified in a table—other tables can't be referenced in a CHECK constraint.

• You can place multiple CHECK constraints on a single column.

• You can place table-level CHECK constraints that can then be associated with more than one column.

• The CHECK constraint isn't enforced for columns that don't receive a value (in other words, for columns that are NULL).

The following command creates the Friend table with a field named Age and a constraint called AgeConstraint, which prevents the Age column having a value of less than 10 or more than 100:

CREATE TABLE Friend (
   Name    VARCHAR(50) PRIMARY KEY NOT NULL,
   PhoneNo VARCHAR(15) DEFAULT 'Unknown Phone',
   Age     INT,
   CONSTRAINT CheckAge CHECK (Age BETWEEN 10 and 100));

If you don't want to provide a name for the constraint, you can remove the CONSTRAINT keyword from CREATE TABLE and optionally remove the comma between the column definition and the CHECK keyword:

CREATE TABLE Friend (
   Name    VARCHAR(50) PRIMARY KEY NOT NULL,
   PhoneNo VARCHAR(15) DEFAULT 'Unknown Phone',
   Age     INT CHECK (Age BETWEEN 10 and 100));

The following CREATE TABLE statement specifies an SSN field with a CHECK constraint that ensures proper Social Security number formatting:

CREATE TABLE Friend (
   Name    VARCHAR(50) PRIMARY KEY NOT NULL,
   PhoneNo VARCHAR(15) DEFAULT 'Unknown Phone',
   SSN     VARCHAR(15) NOT NULL,
   CHECK (SSN LIKE '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]'));

You can use CHECK constraints with any expression that returns true or false. For example, you can use them to restrict the possible values of a column, with a condition such as CHECK (SEX IN ('Male', 'Female')), or compare the values of two columns such as in CHECK (ShipDate <= OrderDate).

You can use the CHECK constraint with both NULL and NOT NULL columns. However, if you insert NULL for a field that has a CHECK constraint on it, it really means that you're not inserting any value—so the CHECK constraint doesn't do anything in this case.