Altering Database Tables

Once a data table has been created, you can change it by adding, deleting, or modifying columns, constraints, and so on. The actual syntax for doing this differs slightly from database to database, so we won't cover all the details here.

The ALTER TABLE command, along with its many options, allows you to make structural changes to existing data tables. If you want to add a new column named Address to the Friend table, this query does the trick:

ALTER TABLE Friend ADD Address VARCHAR(50);

You can also add more columns with a single ALTER TABLE statement, which looks like this in Oracle and MySQL:

ALTER TABLE Friend ADD (EMail VARCHAR(25), ICQ VARCHAR(15));

For SQL Server and Access, you don't enclose the new columns in parentheses:

ALTER TABLE Friend ADD EMail varchar(25), ICQ varchar(15);

If you want to remove the PhoneNo column, use the following:

ALTER TABLE Friend DROP COLUMN PhoneNo;

You can use ALTER TABLE to add or remove not only columns but also primary keys, foreign keys, or other kinds of constraints.

The following code snippet demonstrates how you can drop a UNIQUE constraint and add a primary key to an already existing table. As this example shows, you also provide a name for the PRIMARY KEY constraint:

CREATE TABLE Friend (
   Name VARCHAR(50) NOT NULL,
   PhoneNo VARCHAR(15),
   CONSTRAINT unq_name UNIQUE(Name));

ALTER TABLE Friend DROP CONSTRAINT unq_name;
ALTER TABLE Friend ADD CONSTRAINT PK_FriendName PRIMARY KEY (Name);

You can only create a PRIMARY KEY constraint on a table if the key column doesn't contain any duplicate or NULL values (and, of course, if there isn't already a primary key on that table).

This will work with SQL Server, Oracle, DB2, and Access. However, the syntax for MySQL is a little different. You can only use the ADD/DROP CONSTRAINT syntax with foreign keys. To add or drop a primary key, you use the syntax ADD/DROP PRIMARY KEY, and to add or drop an index (including a UNIQUE index), you use ADD/DROP INDEX <column_name >. In both cases, if you're adding an index or primary key, you need to include the key column(s) in parentheses at the end of the statement. So, to drop the UNIQUE constraint and add a primary key in MySQL, you would use this:

ALTER TABLE Friend DROP INDEX Name;
ALTER TABLE Friend ADD PRIMARY KEY (Name);