SQL For Programmers – ALTERing the TABLE

misc_vol3_041 One of the main problems we seem to be stuck with at this point in programming history is, how do we version our databases?

You could put the whole database into version control, I guess.  But that could end up being a lot of data to create a delta for each time.

You could just version the structure.  But then you are left with importing data from your current system, which may or may not work.

So most shops either ignore the problem entirely (not a very good idea, if you ask me), or they require every structural change to be recorded in a SQL script so that by running a series of scripts, you can recreate the database to any point in time.

If you are working on a commercial project, or any other project that has to be deployed to multiple locations, you need to at least create delta SQL scripts so that the new location can get the new data.

And creating these delta scripts isn’t as hard as it seems, once you learn the basics.  But it would be nice if someone created a more elegant solution.

There are several things you might need to change in your database as time passes, but the one you will probably need to do the most is add, remove and modify fields/columns.  You do this with the ALTER TABLE command.

If all you need to do is to add a column to your table, the command is quite simple and looks a lot like the CREATE TABLE statement we looked at last week.

ALTER TABLE tableName
  ADD newField datatype(size,precision) etc...

To remove a field, you use the DROP statement

ALTER TABLE tableName
  DROP newField

To change a field definition

ALTER TABLE tableName
  ALTER COLUMN newField dataType(size,precision) etc...

As far as I know, there is no clean SQL way to change a column name.  You’d have to

  1. ADD a new field with the new name and type information
  2. Issue an UPDATE command to move the data from one field to the other
  3. DROP the old field.

 

Many times we’d like to ADD several fields, drop several fields and change the data type information all at one time.  This is where most of the documentation you can find online for the ALTER TABLE command falls short.

To Add multiple columns:

ALTER TABLE tableName
  ADD newField datatype(size,precision) etc... ,
  newField2 datatype(size,precision) etc...,  etc...

To drop multiple columns:

ALTER TABLE tableName
  DROP newField,
  newField2

So you’d think you could change multiple columns the same way, right?

WRONG!

To alter multiple columns, you will need to issue multiple ALTER TABLE statements.

You also cannot ADD and DROP in the same ALTER statement.  You will need one ALTER TABLE statement to ADD new columns and another ALTER TABLE statement to DROP all of your columns and yet another ALTER TABLE statement for each column you want to modify.

This isn’t the only reason you wonder what the SQL guys were smoking when they created this syntax.  But I’m sure there was a good reason for this at the time.  Any SQL guys want to enlighten us?

Related Post

One Response to “SQL For Programmers – ALTERing the TABLE”

Leave a Reply

Comment Policy:

  • You must verify your comment by responding to the automated email that is sent to your email address. Unverified comments will never show.Leave a good comment that adds to the conversation and I'll leave your link in.
  • Leave me pure spam and I'll delete it.
  • Leave a general comment and I'll remove the link but keep the comment.

Notify me of followup comments via e-mail

Bear