Designing and managing your tables is just as important as querying it. Design and management of your tables include creating, editing and deleting (dropping) tables. Table design controls much of your database performance and determines data integrity, stability and scalability.

Creating a Table

Most table changes include creating a new table for your site. Tables are considered the entities for your site. For instance, an ecommerce site has products, customers, reviews, orders, and several other useful parts. Each of these parts can be made into a table. Tables organize data into these parts, so you can search for them when you need them. Part of a table naming scheme includes making names useful for coders to understand which tables contain which data. For instance, you wouldn't think to find customer address information in a table named Products. For this reason, naming your tables is an important part of database design.

We've used a Customer, Order, and Comment table for examples. What if you decide one day that you want to add a Product table that contains your products. The first part of the design process is determining what data you want to store and what data you already store. For instance, you already store order information, so you wouldn't want to store more order information in the Product table. However, you would want to store the OrderId in the Product table to link orders with products.

Once you write down each field you need to store, you can start creating your table. Let's create a sample Product table.

Product

ProductId	OrderId	ProductPrice	ProductName
1	3	10	Red Widget
2	2	40	Purple Widget
3	1	20	Green Widget

The first column is a unique, primary key. When you design your table, you probably want to use the auto-increment function to create the value stored in the first column. Next, you have the OrderId column. This column contains the orders that link to the products. When a customer orders a product, you now store the products along with the product's price and name. Because a customer can order multiple products, you wouldn't store the product information in the Order table. Doing so would create multiple records in the Order table with the same information, which goes against a table design rule called "normalization." Normalization is a more advanced concept, but know that table design should avoid duplicating data just for the sake of creating a new record.

Now look at the following CREATE SQL statement.

CREATE TABLE Product

(

ProductId INT PRIMARY KEY NOT NULL AUTO_INCREMENT,

OrderId INT NOT NULL,

ProductPrice decimal,

ProductName varchar(255)

)

The most detailed part of the CREATE statement is the first column, which is your primary key. Note that the above statement is written in MSSQL, but other database engines differ slightly in their syntax for the CREATE statement. The above statement uses the NOT NULL, PRIMARY KEY and AUTO_INCREMENT statements to create specific parameters for the primary key column, which is required for a primary key. The next three columns specified have the column name, the column data type, and the NOT NULL specification is used on the OrderID. You use the NOT NULL data requirement when you don't want to create unlinked records. In the case of the Product table, the only way a record can be inserted is if it has a corresponding Order record.

After you create your table, you are free to add records and start using SELECT queries on the table columns. All of the previous chapter concepts work on your table once it's been created.

Changing or ALTERing Tables

After you create a table, you might decide to change a column name, add a column, delete a column or change the table in some way.

Changes to tables use the ALTER keyword. The following example adds a "CreateDate" to the Product table.

ALTER TABLE Product

ADD CreateDate datetime

Notice that there is no "NOT NULL" specification in the above statement. Let's say you have records already inserted into the Product table. If you attempt to add a column and don't allow nulls, your ALTER statement will fail since the database doesn't have any data to add as a default to the new column in existing records.

You can also change the data type for a column. For instance, suppose we run the ALTER statement above, and then we realize that the CreateDate column needs a more precise date. You can change the data type to a datetime2 data type using the ALTER statement with the ALTER COLUMN phrase. The following SQL statement shows you how to modify your column data types.

ALTER TABLE Product

ALTER COLUMN CreateDate datetime2

The above statement was created in the MSSQL language. MySQL and Oracle use the MODIFY COLUMN phrase.

One issue with altering tables is any new or existing data. When you add a column, SQL will insert NULL into existing records. When you alter columns, you must consider the existing data. For instance, if you change a column data type from varchar to int, do all columns support the change? If any records have invalid integer values, the ALTER statement will fail.  These considerations make it important to test your changes before you run them, especially when altering tables.

The final step in managing tables is deleting a column. Just like any other changes to your table, deleting columns is equally as messy if you make mistakes. Deleting a column, however, is probably more critical than any other statement, because your data and column are removed. Any reports or queries that rely on this data will no longer function, so it's important that you test changes to your tables before removing columns.

Deleting a column is called "dropping" in the SQL language. When you remove a structure such as a table column, you "drop" the column. The following SQL statement shows you how to remove a column from your table.

ALTER TABLE Product

DROP COLUMN CreateDate

SQL gives you no warnings, no confirmation screen, nothing. Once you run a DROP statement, the data and column is removed and you need to recover with a backup of your database.

DELETE or DROP a Table

Just like deleting a column, deleting a table should be done with care. Deleting a table is also called "dropping" the table, and SQL uses the DROP phrase to remove a table from your database.

The following SQL command removes a table from your database.

DROP Table Product

The above SQL statement deletes the Product table we just created. With this deletion, there are no dependencies. Dependencies are objects (tables, columns, stored procedures, functions) that rely on data from other objects. For instance, suppose you decide to delete the Order table instead of the Product table. Our ecommerce database is set up to link the Order and Product table. The Product table relies on the Order table for data. The OrderId links the Order table to the Product table where the SQL engine finds details about the Order. If you delete the Order table and leave the Product table, your products are orphaned. Therefore, the Product table is dependent on the Order table. The Product table is a dependency of the Order table, so the Order table should not be deleted.

The issue with dependencies is that SQL will drop the table regardless. If you have a stored procedure that uses the Product table, this stored procedure will now throw an error. Be very careful when deleting columns and tables.

Renaming a Table

SQL lets you rename your tables. A good trick for testing a deletion is to first rename the table. With the table renamed, you can identify dependencies that would otherwise cause bugs. You don't delete your table like you would when you use the DROP statement, but the table becomes unavailable to any previous dependencies. If any errors occur, you can revert the name change and rethink the table deletion.

The following SQL statement renames the Product table to Product2.

sp_rename Product, Product2

The above statement is different than the other SQL statements you've used. The sp_rename stored procedure is an internal, system stored procedure. This procedure is specific to MSSQL, but MySQL and Oracle have the same standards. The difference between this statement and the others you've seen is that this is a procedure and not a SQL keyword. The stored procedure is similar to calling a function. The function takes the old table name (first parameter) and uses internal system processes to change the table name to the alternative (second parameter).

Managing your tables is a critical part of database administration because you have no room for error and these structures contain data as old as the lifetime of your database. Make sure you thoroughly bug-check, verify and test any changes you make to table structure.