Working with 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.
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.
Tip: Using DEFAULT Instead of NULL Values
Many database developers use DEFAULT values instead of NULL columns, especially in columns that will be used in calculations or data groupings.
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 (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.
Tip: Using Relational Rules to Prevent Accidental Deletion
Many DBMSs allow you to enforce rules that prevent the dropping of tables that are related to other tables. When these rules are enforced, if you issue a DROP TABLE statement against a table that is part of a relationship, the DBMS blocks the operation until the relationship was removed. It is a good idea to enable these options, if available, to prevent the accidental dropping of needed 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
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'