Understanding the ALTER TABLE Statement

No amount of prior planning completely eliminates the need for changing the structure of tables over the course of time. This is not to say that you should spend little time designing tables because they are going to change anyway. Quite the opposite—the more time you spend on design and deciding exactly what data goes in what tables, the less time you will spend later changing your tables and trying to input several months' worth of information that you initially omitted from the database.

That being said, changes to database tables, like changes in life, are inevitable. Perhaps you created your customer table before everyone had an e-mail address (yes, that was not too many years ago). So, now you need to add not only an e-mail address but also a Web (home page) address to the customer record. Or, maybe your company no longer accepts back orders. As such, your inventory table now needs a "minimum_stock_level" column so the DBMS can alert you to order parts from your supplier before you run out and can't take a customer's order. Finally, suppose that your company expands its product line and now makes purchases from multiple vendors. You may need to add a foreign key linking a new "vendor" column in the item master table to a newly created vendors table. None of these changes are necessitated by poor design. The database tables must change because the entities they represent (the business, business rules, and relationships) do not remain constant.

Fortunately, the SQL ALTER TABLE statement allows you to:

• Add a column to a table

• Drop a column from a table

• Change or drop a column's default value

• Add or drop individual column constraints

• Change the data type of a column

• Add or drop a table's primary key

• Add or drop foreign keys

• Add or drop table check constraints

The syntax of the ALTER TABLE statement is:

 ALTER TABLE 
 {ADD }
 {[WITH CHECK | WITH NO CHECK] ADD }
 {ALTER COLUMN  
    [(precision,scale)][NULL | NOT NULL]}
 {DROP COLUMN 
    [,...,}
 {CHECK | NO CHECK CONSTRAINT [ALL |
    
    [,...,]}
 {ENABLE | DISABLE TRIGGER [ALL |
    
    [,...,]}

 is defined as:
   
  [IDENTITY [(seed,increment)]|[NOT NULL]
  [DEFAULT ]][UNIQUE]
  []

 is defined as:
  CHECK ()

 is defined as:
  [CONSTRAINT ]
       
     | 
     | DEFAULT 
         FOR 
     | CHECK ()

 is defined as:
  PRIMARY KEY ( [, ])

 is defined as:
  FOREIGN KEY () REFERENCES 
 
 
 

 
Note 
 
 
Do not include the ellipses ({}) in your ALTER TABLE  statement. The ellipses delineate the different forms of the ALTER TABLE  statement, and you must select one (and only one) of the forms per statement.  Thus,
 
 ALTER TABLE  ADD 
is one valid choice, as is this
 
 ALTER COLUMN  
 [(precision,scale)][NULL | NOT NULL]
 
and so on. 
Also, do not put the pipe ( I ) symbol in your ALTER TABLE  statement. The pipe symbol indicates "or," meaning you must select one clause or  another. As such, when specifying a table constraint, either define a primary  key, a foreign key, a default, or a check constrain, but not all four.
 
SQL ALTER TABLE statement clauses tend to be very DBMS-specific.  All products allow you to add columns. However, not all products let you drop  columns. (Using the ALTER STATEMENT to drop a column is not part of the SQL-92  specification.) Some products allow you to add and drop individual column  constraints on existing columns; others, including MS-SQL Server, do not.
 
In short, each DBMS vendor adds what it considers to be important  features to the ALTER TABLE statement and removes those clauses more easily  implemented with other vendor-specific constructs. MS-SQL Server, for example,  does not let you use the ALTER TABLE statement to change defaults on or add  defaults to existing columns, even though other DBMS vendors do. Instead, MS-SQL  Server, through Transact-SQL and stored procedures, provides CREATE DEFAULT,  sp_bindefault, sp_unbindefault, and DROP DEFAULT to manage column default apart  from the ALTER TABLE statement.
 
We will discuss many of the ALTER TABLE clauses later in this  book. The important thing to know is that every DBMS has an ALTER TABLE  statement you can use to change a table's structure. Check your DBMS  documentation for the specific clauses available to the ALTER TABLE statement in  your DBMS product.