RE: Changing an SQL table (Best practices)

["Walden H. Leverich" <WaldenL@xxxxxxxxxxxxxxx>]

> What are your "best practices" for storing SQL DDL source and modifying
it 
> in an environment without a change management package? 

Rob,

We've found that you canNOT just update the create table once you cross
over into the land of declarative referential integrity. If file B has a
foreign-key reference to file A you can not drop file A to replace it
with a new file A, unless you also drop file B (of course, to delete B
you'd need to delete C & D and to delete D you'd have to delete E, F and
G, etc). You have to deal with the ALTER TABLE statements are new
artifacts in the change management process.

We've dealt with this two ways, effectively variations on each other.
One is to treat the ATLER TABLE as it's own file and promote it. The
alter will effect file A, but that's fine, it's an alter, not a
drop/create. (this is our preference) 

The other is to check out the CREATE TABLE file, but append the alter to
the bottom of the file with an IF NOT EXISTS check around the create.
That is you have:

IF NOT EXISTS TABLE1
        CREATE TABLE1 COLUMNS A, B, C
ENDIF
ALTER TABLE1 ADD COLUMNS D, E, F

Of course, that gets more complicated as time moves on. Next time you
have:

IF NOT EXISTS TABLE1
        CREATE TABLE1 COLUMNS A, B, C
ENDIF

IF NOT EXISTS COLUMN D on TABLE1
        ALTER TABLE1 ADD COLUMNS D, E, F
ENDIF

ALTER TABLE1 ADD COLUMNS G, H, I

... And so on. 

Which approach to take depends on what you want to be able to do later.
If you want a single file to (re)run at a later date that will start
from nothing and create your current table structure then the second
approach is better. If you're just looking to keep track of changes then
the first approach works best.

-Walden