Working with Tables

Creating Tables

SQL statements can be used to also create tables tables in addition to manipulating the table data.

There are generally two ways to create database tables:

• Most DBMSs come with an administration tool that can be used to create and manage database tables interactively.
• Tables may also be manipulated directly with SQL statements.

The CREATE TABLE is used to create a table with SQL statements.

Basic Table Creation To create a table using CREATE TABLE, you must specify the following information:

• The name of the new table specified after the keywords CREATE TABLE.
• The name and definition of the table columns separated by commas.
• Some DBMSs require that you also specify the table location.

The following SQL statement creates the Products table used throughout this book:

CREATE TABLE Products
(
    prod_id      CHAR(10)        NOT NULL,
    vend_id      CHAR(10)        NOT NULL,
    prod_name    CHAR(254)       NOT NULL,
    prod_price   DECIMAL(8,2)    NOT NULL,
    prod_desc    VARCHAR(1000)   NULL
);

As you can see in the above statement, the table name is specified immediately following the CREATE TABLE keywords. The actual table definition (all the columns) is enclosed within parentheses. The columns themselves are separated by commas. This particular table is made up of five columns. Each column definition starts with the column name (which must be unique within the table), followed by the column’s datatype.

Working with NULL Values

To require a value in a column we use the NOT NULL keyword. This means a a value is required when an data is inserted into the table. The default value is NULL when no keyword is specified for a table column.

Specifying Default Values

SQL enables you to specify default values to be used if no value is specified when a row is inserted. Default values are specified using the DEFAULT keyword in the column definitions in the CREATE TABLE statement. Look at the following example:

CREATE TABLE OrderItems
(
    order_num     INTEGER         NOT NULL,
    order_item    INTEGER         NOT NULL,
    prod_id       CHAR(10)        NOT NULL,
    quantity      INTEGER         NOT NULL    DEFAULT 1,
    item_price    DECIMAL(8,2)    NOT NULL
);

You can also use a function to generate the default value, e.g. using the current date when a row is inserted, you would use DEFAULT GETDATE() to automatically have the current date as the default value.

Updating Tables

The ALTER TABLE statement is used to alter the schema of the tables. You should avoid altering the schema of the table after its been created. Try to anticipate how the table will be used and plan accordingly.

Adding a column

ALTER TABLE Vendors
ADD vend_phone CHAR(20);

This statement adds a column named vend_phone to the Vendors table. The datatype must be specified.

Dropping a column

We can also remove the entire column from a table as in the following statement :

ALTER TABLE Vendors
DROP COLUMN vend_phone

Deleting Tables

Deleting tables (actually removing the entire table, not just the contents) is very easy—arguably too easy. Tables are deleted using the DROP TABLE statement:

DROP TABLE CustCopy;

This statement deletes the CustCopy table.

Renaming Tables

You can rename tables in Microsoft SQL databases using a store procedure called sp_rename. The stored procedure changes the name of a user-created object in the current database. This object can be a table, index, column, alias data type, or Microsoft .NET Framework common language runtime (CLR) user-defined type

Syntax

sp_rename [ @objname = ] 'object_name' , [ @newname = ] 'new_name'
    [ , [ @objtype = ] 'object_type' ]

Renaming a table

To rename a table called new_orders to old_orders we can use the following statement :

sp_rename 'new_orders', 'old_orders', 'object'