Creating, Altering, and Deleting Tables
This section discusses the basics of creating a table using SQL. It shows you how to create a new table, how to modify existing tables, and finally, how to delete tables that you no longer need. After that, using what you’ve learned, you create the tables for the book’s example database. Creating a Table To create a table, use SQL’s CREATE TABLE statement. Creating a basic table involves naming the table and defining its columns and each column’s data type. More advanced table options and constraints are covered in Chapter 4. The following is the basic syntax for creating a table: CREATE TABLE name_of_table ( name_of_column column_datatype ) CREATE TABLE is the keyword telling the database system what you want to do—in this case, you want to create a new table. The unique name or identifier for the table follows the CREATE TABLE statement. Then in brackets comes the list defining each column in the table and what sort of data type it is. The syntax becomes clearer with an example.
The following SQL creates a table based on the earlier train timetable example: CREATE TABLE Train_Times ( start_location varchar(75), destination varchar(75), departs time, arrives time );
MS SQL Server doesn’t have the time data type, so you need to change the data type to datetime. For Oracle, you need to change the data type to date rather than time, because Oracle’s date data type stores both date and time. For this and all of the examples in this book, load up the tool that came with your RDBMS that allows SQL code to be written and run. You can find installation details in Appendix B at the end of the book. Examining the code line by line, first you specify that you want to create a table called Train_Times:
CREATE TABLE Train_Times
Then, inside brackets, you specify the four fields that make up each record. For each record, you need to identify the field name and data type: ( start_location varchar(75), destination varchar(75), departs time, arrives time )
A comma must separate each field definition. Notice the SQL’s neat layout, with the CREATE TABLE statement and each field definition on separate lines. Such layout is not compulsory. In fact, you could cram the whole lot on one line. However, laying out the code on separate lines makes the code a lot more readable and therefore easier to write and makes it easier for you to fix errors, or debug, if things go wrong. You can see that creating tables is pretty easy. There are more complexities with table creation than listed here, and these are examined in Chapter 4. The next issue is how to alter tables. Say you want to add a new field, delete an existing one, or perform other routine table maintenance. The good news is that SQL allows you to perform all of these functions with one statement: ALTER TABLE.
Altering an Existing Table
The key to changing an existing table is the ALTER TABLE statement. This one statement allows you to add and delete columns in an existing table. What the ANSI SQL standard ALTER TABLE statement doesn’t let you do, however, are things like changing the data type of an existing column. However, many RDBMSs have extended the ALTER TABLE statement and include their own way of changing column definitions.
To add a new column, use the basic syntax shown below: ALTER TABLE name_of_table ADD name_of_field data_type
ALTER TABLE is the keyword that tells the database system what to do. After the ALTER TABLE statement, you supply the name of the table being altered. Finally, the syntax above tells the database system that you want to add a new column and then supplies the name of the column and its data type—in much the same way that you define column name and data type when creating a table. To delete an existing column, the syntax is identical except you now tell the database system that you want to delete a column and supply that column’s name:
ALTER TABLE name_of_table DROP COLUMN name_of_field A couple of examples make this a bit clearer. In order to add a column called runs_at_weekend with the data type char(1) to the Train_Times table, use the following SQL: ALTER TABLE Train_Times ADD runs_at_weekend char(1); To delete the same column, you write the following: ALTER TABLE Train_Times DROP COLUMN runs_at_weekend; IBM DB2 doesn’t support the DROP COLUMN statement. Remember, as with dropping a table, dropping a column most likely permanently deletes the data in that column. Use the DROP COLUMN statement carefully! Finally, the next section discusses how to delete an existing table.
Deleting an Existing Table
By now you’re probably seeing a pattern emerge when it comes to deleting things, so yes, you guessed it, deleting a table involves using the DROP TABLE statement. The basic syntax is DROP TABLE name_of_table To delete the Train_Times table, you write DROP TABLE Train_Times This section only scratches the surface of adding and altering tables and columns. Chapter 4 identifies potential complications that may arise when dropping a table that contains data that another table relies upon.
You should now know enough fundamentals to begin creating usable databases and tables. The final section of this chapter walks you through creating the example database that you use throughout the book. First, though, you need to know how to use good database design techniques to create an effective database.
Click Here To Purchase This Book