Haugerns Development Escapades

An absolute rule for your database insert scripts and stored procedures should be to qualify all field names.

An example could be the following schema:

Where you would have a script saying:

INSERT INTO FAMILY VALUES (‘Bob’,‘Dolly’,‘Bill’)

Then, you could see the disaster inserting a new field named Girlfriend in between You and your Mom (in more than one way :-)).

Then you have a schema like this:

Suddenly after your next insert your Girlfriend is your Mom, and your Dad is your Mom!

Well, the one solution to rectify this mess is of course guiding your values into the correct fields like this:

INSERT INTO FAMILY (You,Girlfriend,Mom,Dad)  

VALUES (‘Bob’,‘Julia’,‘Dolly’,‘Bill’)

The rationale is then as follows:

• This will assure that if you have changed the schema ie. moved or inserted new fields, the script will not fail or most importantly it will not insert data in the wrong fields.
• It will fail if you have removed or replaced fields which assures that you will not get corrupted data from these changes either.