Tip #44 – Altering Table Structure

Tables can be altered using the ALTER TABLE statement. This is a very powerful SQL statement because it allows for changing so many things about the table. You can ADD, ALTER and DROP columns (fields) and constraints defined in the table. Another overlooked feature of this command is the ability to change the position of various fields in the table.

The following statement alters the original structure of the Department table pictured below:

 

ALTER TABLE Department
  ALTER COLUMN Name FullName char(50) CONSTRAINT NOT NULL
  ALTER COLUMN Budget Budget money CONSTRAINT NOT NULL CONSTRAINT MINIMUM '0'
  ALTER COLUMN Administrator Administrator integer POSITION 4

The altered table structure is shown below. Notice that the Name field is now FullName, the Budget field has a Minimum Value and NULL Valid is now No. Finally the Administrator and StartDate fields have changed positions.