Welcome again to the SQL basics series. This article takes a step back to cover the Database Definition Language (DDL) commands for creating databases and tables and altering a table’s structure.
Please be cautious when you use these commands—it’s entirely too easy to remove major constructs in your database and lose data, so be sure you know what’s in the database before you start altering it.
The sample queries in this article adhere to the SQL92 ISO standard. Not all database manufacturers follow this standard, and many have made enhancements that can yield unexpected results. If you’re uncertain about support for these concepts in your database, please refer to your manufacturer’s documentation.
Create a database
In order to create tables, you must first create a database to house them. The basic SQL syntax to create a database is:
CREATE DATABASE dbname;
Your database user must have the proper permissions set up to create a new database. If the user you connect with for information is not able to issue the command to create a new database, either ask your database administrator to create the database for you or log in as the admin user to create the database and set up permissions.
For more on database permissions
Database permissions are beyond the scope of this article. For more information, refer to the database manual from your vendor, or please let us know if this is a topic you’re interested in seeing covered in the SQL basics series. And please keep those suggestions and comments coming!
For an example database, use the CREATE command to make a database for an application that displays a catalog:
CREATE DATABASE Catalog;
This gives you a named tablespace for issuing queries against related tables. The next step is to create the tables to put in it.
Create a table
As you know, a table is made up of several columns. You can define the columns and assign field attributes when you create the table. After a table has been set up, it can be modified using the ALTER table command, which I’ll get to a little later.
To create a table, use the following command with the new table’s name, column names, and data type for each column.
CREATE TABLE table_name
(column1 data_type, column2 data_type, column3 data_type);
A column’s data type defines the way data will be treated and stored. This is a part of the SQL standard in which different manufacturers vary wildly. Your documentation will have a section detailing what each type does and what parameters it accepts. For general purposes, I’ve listed a few frequently used data types in Table A below.
|Char||Char(8)||It contains a string of a fixed length—the value is always the length specified.|
|Varchar||Varchar(128)||It contains a string of a variable length—any size up to the length specified.|
|Int||Int(32)||This is an integer up to the specified length—also referred to as Number or Integer.|
|Decimal||Decimal(12,2)||This is a decimal number with the maximum number of total digits and the number of digits after the decimal point—also called Numeric, or Number.|
|Binary||Binary||It stores a binary object, whose contents are generally not parsed to display within the database—also called Raw or Blob.|
|Boolean||Boolean||It stores a true or false indicator—also called Bit or Byte.|
For the catalog example, create a table that stores the description of products in our inventory. The columns and data types to use are as follows in Table B:
For this example, I used three of the basic data types; however, in an actual deployment, I might have used tinyint, text, and mediumtext data types, depending on what my database supported.
To create the table, issue the following command:
CREATE TABLE Products
(prod_id INT(16), prod_color VARCHAR(20), prod_descr VARCHAR(255), prod_size DECIMAL(8,2));
Assuming the command completes successfully, you can now insert information into the table normally. For more information on inserting information, refer to the article "SQL basics I: Data queries."
In addition to data type, you can also define auto-incremented fields, keys, indexes, and unique value constraints when you create a table. These parameters are passed along with the data type in the table definition. If I want to make prod_id an auto-incrementing unique value when I create the Product table, the command would look like this:
CREATE TABLE Products
(prod_id INT(16)AUTO_INCREMENT, prod_color VARCHAR(20), prod_descr VARCHAR(255), prod_size DECIMAL(8,2), UNIQUE (`prod_id`));
If I want to make prod_id an indexed field, I can use CREATE INDEX:
CREATE INDEX ProdIndex ON Product (prod_id);
It’s worth restating here that database manufacturers vary on their handling of these keywords, so please refer to your vendor’s documentation for specific details.
More on indexing
Indexing is an in-depth topic in its own right. To introduce the theory behind keys and indexes, Builder.com contributor Eric Roland has written a couple of great articles that you can read to learn more.
Once you’ve begun work on a table, you may realize that you need to modify its structure, field types, and so on. Above, I highly recommended that you avoid doing this in a production environment. Since possible options include adding, removing, and modifying columns, you run the risk of deleting or corrupting data stored in affected fields.
With that word of warning, let’s look at altering tables. First add a column to the Product table. You can specify where you want the column inserted in relation to other fields, or allow it to default to the end of the table:
ALTER TABLE Product ADD prod_name VARCHAR(20) AFTER prod_id;
Use similar syntax to remove a column:
ALTER TABLE Product DROP prod_size;
Finally, change a column to a new type:
ALTER TABLE Product CHANGE prod_color prod_color_id INT(20);
Your table now should resemble the following in Table C:
Note that some databases don’t support the DROP keyword. Also, if you change the type of an existing column, most databases will make some attempt to convert the data, if it exists. However, when changing to an incompatible type, data may be lost. For example, if I change a field containing people’s names from Varchar to Int, the field may revert to the new Integer type’s default value.
Removing tables and databases
I’ve saved removing tables and databases for last so you can clean up the mess you left in your test install. Don’t do this unless you’re sure it’s safe to lose all data entered in the Product table. If you remove the database, all tables and their content will be wiped out. You can also remove just one table. Its content will be lost, but content in other tables will remain intact.
You must be confident of an entire database’s structure before removing tables or even dropping columns. If you go into an existing database and remove the wrong element, you could affect triggers, stored procedures, and views. Some databases allow the RESTRICT and CASCADE keywords to prevent loss of dependency by removing a table. RESTRICT is generally set by default, preventing the table from being dropped, and CASCADE will remove all dependent entities along with the table.
The Product table created above is most assuredly safe, however, so let’s remove it:
DROP TABLE Product;
Short, simple, and dangerous. Next, remove the database itself:
DROP DATABASE Catalog;
Most vendors support the DROP DATABASE command, but it’s defined under the SQL99 standard, not SQL92.
If you want to wipe content from a table but preserve the table’s architecture, some databases support the FLUSH command:
FLUSH TABLE Product;
As you can see, it’s incredibly easy to remove major constructs within your database and lose all the data stored there, so please use these commands with caution, and never when you’re unsure of the database’s contents.
In previous articles, you learned how to issue basic queries on existing data in one or more tables. Now you know how to put the structure you’re querying against in place. You can create, alter, and destroy tables and databases, actions necessary for building a database-driven application.
What SQL topics do you want covered?
Post to the discussion below or send an e-mail to the editors to request more information about a specific aspect of SQL.