SQL Syntax III: Altering Tables
Peter Komisar         ©           Conestoga College                 version 1.3

reference: MySQL Reference Manual. MySQL Structured Query
Language(SQL) ,D.Geller, R. Roselius, Danielle Waleri, DDC
Publishing, Core MySQL, Leon Atkinson, Prentice-Hall Publishing,

Data Type Review

We did a pretty extensive look at MySQL datatypes.
The SQL Class Companion overviews the ANSI 's
generalized data types followed by a list showing
some details of Oracle data types.

Treating it as a review we summarize the manual's
information here.

ANSI defines six general data type categories.
Different RDBMS vendors differ considerably on
how they implement the different categories of
data type.


ANSI Defined General DataTypes

• Character

  • character data of fixed or varying length

• Bit string

  • raw byte data of fixed or varying length

  • described as unstructured and uninterpreted

• Exact Numeric

  • numbers representing exact quantities

• Approximate Numeric

  • fractional or floating point numbers

• DateTime

  • represents date or time

  • or date time combinations

• Interval

  • related to the DateTime type

  • holds intervals between dates or times

The manual lists the following as auxiliary types
that are provided additionally to the standard types.


Additional Types

• Boolean

• Bit-field

• Money

• Serial

• Rowid

• Mlslabel

Table Showing Various Database Vendor's Types Classified By ANSI Generic Types

// real number decimal numbers which may have an infinite decimal notation

Modifying Tables

Different commands are available for creating
and modifying table definitions.

The CREATE Statement

The CREATE command is followed by the keyword
TABLE and the name the table will be given. Following
these keywords are the column names in parentheses
which must be unique, followed by their type.

Following is a formal description of the CREATE statement.

Form of the CREATE Statement 

CREATE TABLE  TableName
  (
  ColumnName1    DataType [DEFAULT Value]  [NOT NULL],
  ColumnName2    DataType [DEFAULT Value]  [NOT NULL],
  . . .
  ColumnNameN    DataType [DEFAULT Value]  [NOT NULL]
  );

Following is the example that was supplied in the
introductory note. Note here the column names are
InventoryNo, Make Model and Year. The datatypes
are INTEGER and CHAR(40). The option to make
the columns NOT NULL is used.

CREATE Example

CREATE TABLE  Cars
    (
    InventoryNo  INTEGER     NOT NULL,
    Make         CHAR(40)    NOT NULL,
    Model        CHAR(40)    NOT NULL,
    Year         CHAR(10)    NOT NULL
    );

// column names are followed by type

Column name and data types. Some data types
such as in the example above require a size value.
The CHAR type above has a 40 character  limit.

Following is another table example.

Example

CREATE TABLE customer_contractor
   (  id INTEGER,
      rate DECIMAL(2,2) DEFAULT .05,
      payment_terms VARCHAR(80)
    );


Execution

mysql> CREATE TABLE customer_contractor
    ->   (  id INTEGER,
    ->      rate DECIMAL(2,2) DEFAULT .05,
    ->      payment_terms VARCHAR(80)
    -> );
Query OK, 0 rows affected (0.17 sec)

The following example is an equivalent form
that would work in Oracle but not MySQL.

Example

CREATE TABLE customer_contractor
  (  id NUMBER,
     rate NUMBER(2,2) DEFAULT .05,
     payment_terms VARCHAR  (80)
  )

// doesn't work in MySQL as MySQL does not have
// the Number type

Column Order

The order that columns specified in the CREATE
statement is the order that the columns will take in
the TABLE that is created. In subsequent SELECT
and UPDATE statements, this is the order that will
be used.

T he order that columns are created doesn't impact
the performance of the database.

Specifying Initial Table Size

Some databases allow specifying the intitial size
of a database and the size by which the database
will grow if the initial size is exceeded. The increase
size is sometimes referred to as the 'next extent'.

Following is a table recreated from the classroom
companion manual for an Oracle database, showing
the command form used for controlling initial size and
the increment size of a table.

Example // just for reference

// from MySQL Structured Query Language(SQL) ,D.Geller,
// R. Roselius, Danielle Waleri, DDC  Publishing,

TABLESPACE tbspace STORAGE
  ( [ INITIAL     n  [ K | M ]  ]
     [ NEXT        n  [ K | M ]  ]
     [ MINEXTENTS  n   ]
     [ MAXEXTENTS  n   ]
     [ PCTINCREASE n   ]
     [OPTIONAL [NULL]  n  [ K | M ]  ] // brace added ed.
   )

Column Modifiers

• NOT NULL

is a column modifier that stipulates that the value
stored in this column for any given row cannot be
NULL.

We saw earlier partial INSERTS and UPDATES
would add NULL values in locations that were not
being targeted by the inserts or updates.
 
Such statements would fail if columns were present
that were set to NOT NULL. The partial INSERTS or
UPDATES would attempt to set these column values
to NULL be would not be allowed to do so.

• DEFAULT

The DEFAULT modifier is used to specify a default
for values stored in a specific column. This default
is supplied in the event that an INSERT or UPDATE
command doesn't provide.

In the following example values are supplied for 'id'
and 'payment_terms' but not 'rate'.

Partial Insert Example With Default

mysql> INSERT INTO customer_contractor(id,payment_terms)
    -> VALUES(1001, "60 Days");
Query OK, 1 row affected (0.03 sec)

Here is the table creation statement again
showing the default value specified.

Example

CREATE TABLE customer_contractor
   (  id INTEGER,
      rate DECIMAL(2,2) DEFAULT .05,
      payment_terms VARCHAR(80)
    );

The following select shows that instead of
NULL the default rate value was provided.

Select Shows the Default Value Supplied

mysql> select * from customer_contractor;
+------+------+---------------+
| id   | rate | payment_terms |
+------+------+---------------+
| 1001 | 0.05 | 60 Days       |
+------+------+---------------+
1 row in set (0.01 sec)

Constraints

A constraint is similar to modifiers like NOT NULL
and DEFAULT as they add limitations to what values
may be stored in a column. Following are common
constraints.

• PRIMARY KEY

PRIMARY KEY is used to designate the column which
will hold the key identifier for rows. Each value in that
column will be unique. NULL values are not allowed in
a column holding primary keys. There can be only one
primary key defined for a table.

Primary Keys can be made of of one or a number of
rows. The general form is as follows.


Primary Key Form

PRIMARY KEY (columnName1 [, columnNameN . . . ] )

• UNIQUE

The UNIQUE constraint says that all values in a column
will be unique. The UNIQUE constraint allows NULL
values to be present.  Note here that NULL is not a
value in the normal sense and should be thought of
more as an absence of value.


UNIQUE Form

UNIQUE (columnName1 [, columnNameN . . . ] )

• FOREIGN KEY

Foreign keys are values stored in a table which are
the primary keys for rows in a related table.

Each value in a foreign key column must correspond
directly with values in a primary or unique key column(s)
of a related table.

The FOREIGN KEY Constraint has the following form.


FOREIGN KEY Form

FOREIGN KEY (columnName1 [, columnNameN . . . ] )
REFERENCES tableName (columnName1 [, columnNameN . . . ] )
[ ON DELETE CASCADE ]


Variations on Applying Constraints

Constraints can be applied to columns in the latter
part of the CREATE statement after all the columns
have been described.

To demonstrate these features we need an extra
table which we can call customer. In this table
we make id the primary key after the columns
are declared.


Example

CREATE TABLE customer
   (  id INTEGER,
      area_code CHAR(3),
      phone_number CHAR(7),
      PRIMARY KEY (id)
    );


Execution

mysql> CREATE TABLE customer
    ->    (  id INTEGER,
    ->       area_code CHAR(3),
    ->       phone_number CHAR(7),
    ->       PRIMARY KEY (id)
    ->    );
Query OK, 0 rows affected (0.08 sec)


mysql> insert into customer values(
    -> 1001,"416","9583332");
Query OK, 1 row affected (0.03 sec)

mysql> select * from customer;
+------+-----------+--------------+
| id   | area_code | phone_number |
+------+-----------+--------------+
| 1001 | 416       | 9583332      |
+------+-----------+--------------+
1 row in set (0.00 sec)

 

We can expand our earlier table definition as follows.

Example

CREATE TABLE customer_contractor
   (  id INTEGER,
      rate DECIMAL(2,2) DEFAULT .05,
      payment_terms VARCHAR(80),
      PRIMARY KEY (id),
      FOREIGN KEY (id) REFERENCES
      customer (id)
      );

Execution

 mysql> CREATE TABLE customer_contractor
    ->    (  id INTEGER,
    ->       rate DECIMAL(2,2) DEFAULT .05,
    ->       payment_terms VARCHAR(80),
    ->       PRIMARY KEY (id),
    ->       FOREIGN KEY (id) REFERENCES
    ->       customer (id)
    ->       );
Query OK, 0 rows affected (0.09 sec)

Table Constraints

When constraints are applied after the columns are
declared as in the above example, they are sometimes
referred to as 'table constraints'. and the associated
syntax is callee Table Constraint Syntax.

Column Constraints

If constraints are applied immediately following the
column declaration, the style is referred to as
'column constraints'.

Following is an example showing the syntax where
columns have constraints applied to them directly
following each column declaration.

Note I have to drop the customer_contractor table
in order to rebuild it using the following syntax.

Dropping the Table // more on this command shortly

mysql> drop table customer_contractor;
Query OK, 0 rows affected (0.03 sec)

The earlier command is recreated using
column constraint syntax.

Command Using Column Constraints Syntax

CREATE TABLE customer_contractor
   (  id INTEGER,
      rate DECIMAL(2,2) DEFAULT .05,
      payment_terms VARCHAR(80),
      PRIMARY KEY (id),
      FOREIGN KEY (id) REFERENCES
      customer (id)
   );

Execution

mysql> CREATE TABLE customer_contractor
    ->    (  id INTEGER,
    ->       rate DECIMAL(2,2) DEFAULT .05,
    ->       payment_terms VARCHAR(80),
    ->       PRIMARY KEY (id),
    ->       FOREIGN KEY (id) REFERENCES
    ->       customer (id)
    ->       );
Query OK, 0 rows affected (0.09 sec)

Either technique, using table constraint syntax or
column constraint syntax is equivalent. Table constraints
have the added advantage of being applied to one or
several columns.

// note: we are deferring referential integrity and associated
// modifiers to a more complete treatment later. Manual P. 74


MySQL CREATE Statement

MySQL adds a couple features to what has been
covered so far which may or may not work with other
databases. Following is the CREATE TABLE form
described in MySQL.
 

The MySQL CREATE TABLE Command

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tableName
[ ( specifications, __) ]
[  option . . . ]
[[ IGNORE | REPLACE ] select ]

• TEMPORARY

The TEMPORARY flag is used to present a table visible
to the current connection which is deleted when the
connection ends. Might be useful to 'snapshot' information
from a busy database.  Actions to a temporary table
do not endanger the original data.

• IF NOT EXISTS

This qualifier suppresses error reports. It does not
overwrite an existent table. The following set of
commands show it had no effect on an existent
table.

Demonstrating the Effect of 'IF NOT EXISTS'

First an attempt to create the table is made when
it is already present in the database. An error is
reported saying the table already exists.

Example

mysql> CREATE TABLE customer_contractor
    ->    (  id INTEGER,
    ->       rate DECIMAL(2,2) DEFAULT .05,
    ->       payment_terms VARCHAR(80),
    ->       PRIMARY KEY (id),
    ->       FOREIGN KEY (id) REFERENCES
    ->       customer (id)
    ->       );
ERROR 1050 (42S01): Table 'customer_contractor' already exists


A row  is inserted into the table followed by a
SELECT statement showing the row's values.
(It uses the key that is used in the foreign key to
maintain referential integrity. We will cover this
later.)



Example

mysql> insert into customer_contractor values(
    -> 1001, .03,"90 days");
Query OK, 1 row affected (0.02 sec)

mysql> select * from customer_contractor
    -> ;
+------+------+---------------+
| id   | rate | payment_terms |
+------+------+---------------+
| 1001 | 0.03 | 90 days       |
+------+------+---------------+
1 row in set (0.00 sec)

The IF NOT EXISTS clause is added showing it executed
without error report despite the table already existing.


Example

mysql> CREATE TABLE IF NOT EXISTS customer_contractor
    ->    (  id INTEGER,
    ->       rate DECIMAL(2,2) DEFAULT .05,
    ->       payment_terms VARCHAR(80),
    ->       PRIMARY KEY (id),
    ->       FOREIGN KEY (id) REFERENCES
    ->       customer (id)
    ->       );
Query OK, 0 rows affected, 1 warning (0.00 sec)


What Action is Accomplished Using the IF NOT EXISTS
Clause with an Existing Table

The question is did it refrain from overwriting the
table or did it replace the existing table. The
following select statement shows the row value
still present. If the table had been overwritten it
would have been erased. This means that the
CREATE TABLE statement with an IF NOT EXISTS
clause only creates a table if such a table does not
already exist.


Example

mysql> select * from customer_contractor;
+------+------+---------------+
| id   | rate | payment_terms |
+------+------+---------------+
| 1001 | 0.03 | 90 days       |
+------+------+---------------+
1 row in set (0.00 sec)

• AUTO_INCREMENT

AUTO_INCREMENT uses sequential numbers
in a column. The type of a column with the
AUTO_INCREMENT modifier must be an
integer. There can only be one column with the
AUTO_INCREMENT modifier. Typically, the
AUTO_INCREMENT modifier is used on the
primary key column.


Example

CREATE TABLE counter
   (  KeyID INTEGER  PRIMARY KEY AUTO_INCREMENT,
      Name CHAR(40)
      );


Execution

mysql> CREATE TABLE counter
    ->    (  KeyID INTEGER  PRIMARY KEY AUTO_INCREMENT,
    ->       Name CHAR(40)
    ->       );
Query OK, 0 rows affected (0.08 sec)


The first insert initializes the auto_increment column.


Example

mysql> INSERT INTO counter VALUES(
    -> 1,"Norman Smith");
Query OK, 1 row affected (0.03 sec)


Subsequent entries explicitly leave out the primary
key which will then auto increment itself.


Example

mysql> INSERT INTO counter (Name) VALUES
    -> ('Billy Thorton');
Query OK, 1 rows affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT INTO counter (Name) VALUES
    -> ('Wally Zenko');
Query OK, 1 rows affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT INTO counter (Name) VALUES
    -> ('Hans Maid');
Query OK, 1 rows affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0


mysql> select * from counter;
+-------+---------------+
| KeyID | Name          |
+-------+---------------+
|     1 | Norman Smith  |
|     2 | Billy Thorton |
|     3 | Wally Zenko   |
|     4 | Hans Maid     |
+-------+---------------+
4 rows in set (0.00 sec)


Multiple INSERT Form

Another form allows multiple inserts with a single INSERT
statement reducing the redundancy found in the above
example.


Example

INSERT INTO counter (Name) VALUES 
    ('Betty Davis'),('Al Pacino'),
    ('George Bush'),('Bill Gates');
Execution

mysql> INSERT INTO counter (Name) VALUES
    ->     ('Betty Davis'),('Al Pacino'),
    ->     ('George Bush'),('Bill Gates');
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0


Example
mysql> SELECT * FROM counter WHERE KeyID > 4;
+-------+-------------+
| KeyID | Name        |
+-------+-------------+
|     5 | Betty Davis |
|     6 | Al Pacino   |
|     7 | George Bush |
|     8 | Bill Gates  |
+-------+-------------+
4 rows in set (0.02 sec)

// CHECK is described as being supported for compatibility 
// with other databases, but does nothing in MySQL. 

DROP TABLE 

The opposite of creating a table is to drop it. Sometimes
a table is removed because it is no longer needed. At 
other times a table is dropped in order to recreate it 
differently. 
You must have appropriate privileges to drop tables. 
The form of the DROP statement is as follows.


ANSI DROP TABLE Form

DROP TABLE tableName ( CASCADE |  RESTRICT )
The MySQL form for this command adds the use 
of the IF EXISTS clause and the optional use of 
TEMPORARY. 

MySQL DROP TABLE Form

DROP [TEMPORARY] TABLE [IF EXISTS]
    tbl_name [, tbl_name] ...
    [RESTRICT | CASCADE]

Dropping a table is radical. All the rows, indexes and
granted privileges are removed. The table is also 
removed from the overall database schema. If the table 
has stated FOREIGN KEY constraints applied then 
they must be removed.

• TEMPORARY

Using TEMPORARY will cause the DROP statement
to be applied only to temporary tables visible to the
client. This may be used in practice to avoid deleting
permanent tables. 

Options

• RESTRICT // typically the default case

The RESTRICT option will cause the statement to fail
if there are any dependencies on the table. This is the
typical default condition in most database systems.

• CASCADE

CASCADE will cause the constraint from a dependent
table to be dropped. The dependent table itself is not
dropped.


No Turning Back!

There is no ROLLBACK for a dropped table!


MySQL Ignores RESTRICT & CASCADE

MySQL just not implement these options. If they are
present in a statement they are ignored. Quoting the
MySQL 5.0.3 manual, "RESTRICT and CASCADE,  
if given, are parsed and ignored." 


Altering Tables

Often a table will need to be altered.  This is accomplished
with the ALTER TABLE command. The command takes
the following form. 


ALTER TABLE Form

ALTER TABLE tableName action
In the above form action is one of the following:

ALTER TABLE Actions

• ADD columnName column_definition

  • adds a column definition

• DROP columnName column_definition

  • drops a column definition

• ADD table_Constraint_Definition

  • adds a table constraint definition

• DROP CONSTRAINT constraintName

  • [ RESTRICT | CASCADE ]

    • drops a constraint

• ALTER columnName SET DEFAULT value

  • change a default value

• ALTER  columnName DROP DEFAULT

  • removes a default column value

// Oracle allow the use of MODIFY in actions to change
// certain column characteristics such as column data type,
// length of a character column, precision of a numeric column,
// the DEFAULT value and the NOT NULL constraint


Adding and Dropping Columns Using ALTER TABLE
 
In the following example a table is created with a
single column. Then two columns are subsequently
added.

Example

mysql> create table versions(
    -> version1 decimal(3,2)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> alter table versions
    -> add version2 decimal(3,2);
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table versions
    -> add version3 decimal(3,2);
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

Values are inserted into the three columns
which are then displayed using a SELECT 
statement.


Example

mysql> insert into versions values
    -> (1.00,1.01,1.03);
Query OK, 1 row affected (0.01 sec)
mysql> select * from versions;
+----------+----------+----------+
| version1 | version2 | version3 |
+----------+----------+----------+
|     1.00 |     1.01 |     1.03 |
+----------+----------+----------+
1 row in set (0.00 sec)

The middle column is dropped and the
altered table and values are displayed.

Example

mysql> alter table versions
    -> drop version2;
Query OK, 1 row affected (0.14 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from versions;
+----------+----------+
| version1 | version3 |
+----------+----------+
|     1.00 |     1.03 |
+----------+----------+
1 row in set (0.00 sec)


FIRST and AFTER Modifiers 

In the following statements the FIRST and AFTER 
modifiers are used to control placement of some 
added columns.


Example 

mysql> alter table versions
    -> add version2 decimal(3,2) after version1,
    -> add keyID int NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;
Query OK, 1 row affected (0.13 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from versions;
+-------+----------+----------+----------+
| keyID | version1 | version2 | version3 |
+-------+----------+----------+----------+
|     1 |     1.00 |     NULL |     1.03 |
+-------+----------+----------+----------+
1 row in set (0.00 sec)


Constraints and default values can be added, 
removed or changed in a similar way. In the 
following example.  First we will delete everything 
from our one rowed table.

Example 
 

mysql> delete from versions;

Query OK, 1 row affected (0.03 sec)

// not good to use even here without conditions!

We supply defaults for older version columns. 


Example
 
mysql> alter table versions
    -> alter version1 SET DEFAULT 0.00;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table versions
    -> alter version2 SET DEFAULT 0.00;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

Now we insert a single value in the version3
column and allow the defaults to be used in
other columns.


Example

mysql> insert into versions(version3)
    -> values(1.23);
Query OK, 1 row affected (0.03 sec)
mysql> select * from versions;
+-------+----------+----------+----------+
| keyID | version1 | version2 | version3 |
+-------+----------+----------+----------+
|     4 |     0.00 |     0.00 |     1.23 |
+-------+----------+----------+----------+
1 row in set (0.00 sec)


Notice that the keyID is set to 4. This reflects the fact
that three rows have in fact been deleted from this
table. This is an example of the rule that primary keys
are not reused in database tables. 


The DESCRIBE Command

MySQL has a DESCRIBE Command which is useful
for displaying 'meta-data' or information about a table.
When altering a table, this command can be used to
validate changes that have been made to a table. 


Output of the DESCRIBE Statement on the 'store' Table
mysql> describe store;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| STORE_NUMBER | int(11)      | YES  |     | NULL    |       |
| MANAGER_ID   | int(11)      | YES  |     | NULL    |       |
| STREET       | varchar(45)  | YES  |     | NULL    |       |
| CITY         | varchar(25)  | YES  |     | NULL    |       |
| PROVINCE     | char(2)      | YES  |     | NULL    |       |
| POSTAL_CODE  | char(6)      | YES  |     | NULL    |       |
| AREA_CODE    | char(3)      | YES  |     | NULL    |       |
| PHONE_NUMBER | char(7)      | YES  |     | NULL    |       |
| GST_TAX_RATE | decimal(2,1) | YES  |     | NULL    |       |
| PST_TAX_RATE | decimal(2,1) | YES  |     | NULL    |       |
| HST_TAX_RATE | decimal(3,1) | YES  |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
11 rows in set (0.09 sec)

Assignment

 
Q.1

Using the ALTER TABLE command, insert the HST
column into the store table to accommodate provinces
that have a combined GST / PST tax. Make sure the
column's data type will accommodate three digits.
( for example 13.0 ).  Also use the DEFAULT modifier
to set the default value to 0.0.

Use the DESCRIBE command, ( or the equivalent
in the database you are using) to show the altered
table structure.  Screenshot the commands output
and submit.

Everywhere where applicable update the value in the
HST column, for the rows for provinces that use the HST
tax.

Do a SELECT statement

Do a SELECT statement (on the store number,
city and province columns) with a where clause
that tests whether HST is not equal to 0.0. This will
display the provinces that have a HST tax.
Screenshot and submit this output.

Q.2

Create a table called Numbers with a single column
called One of Integer type or Number type. Use the
ALTER TABLE command with the ADD action to
add one at a time the column names 'two', 'three'
and 'four' all of the same type as the 'one' column.

Insert the values 1,2,3 and 4 into each of the columns.

Do a 'Select all' and screenshot the output for submission.

Use the ALTER command to drop columns 2 and 3.

Reselect all, and screenshot the output for submission.