Creating a Table withJDBC and SQL - Java Database Programming Bible - page 173

Modify a column that already exists

This is the syntax for the ALTER TABLE command:

ALTER TABLE tableName ADD columnName dataType;

For example, to add a phone number field to the CONTACT_INFO table, use this command:

ALTER TABLE CONTACT_INFO ADD PHONE VARCHAR(16);

You can use MODIFY to change a column constraint from NOT NULL to NULL using this command:

ALTER TABLE tableName MODIFY columnName dataType NULL;>

In much the same way, you can use MODIFY as follows to change the width of a column using:

ALTER TABLE tableName MODIFY columnName dataType;>

Caution

You can always increase the width of a column, but you can't reduce the width below
that of the widest value anywhere in the column. Similarly, you can only change a
column's constraints from NOT NULL to NULL if there are no NULL values in the
column.

Note

Implementations of the MODIFY clause tend to be specific to a database management
system. Some allow the use of the MODIFY clause; others do not.

These are the SQL statements that are required to insert phone number and e-mail address columns:

ALTER TABLE CONTACT_INFO ADD PHONE VARCHAR(16);

ALTER TABLE CONTACT_INFO ADD EMAIL VARCHAR(50) NOT NULL;

Just as you can create a table using JDBC, you can also alter a table using JDBC. As you can see
from the example of Listing 5-2 , the code to alter a table looks very much like the TableMaker.java
example, with the exception that a new method, execute(String[] SQLCommand) has been
added. This method loops through an array of SQL commands to execute each of the ALTER TABLE
commands.

Listing 5-2: Altering a table using JDBC

IMPORT JAVA.SQL.*;

IMPORT SUN.JDBC.ODBC.JDBCODBCDRIVER;

PUBLIC CLASS TABLEMODIFIER{

STATIC STRING JDBCDRIVER = "SUN.JDBC.ODBC.JDBCODBCDRIVER";

STATIC STRING DBNAME = "CONTACTS";

STATIC STRING URL = "JDBC:ODBC:CONTACTS";