MySQL 5 - Alter table structure conditional adding columns if they dont already exist
Discuss Alter table structure conditional adding columns if they dont already exist in the Database Help forum on Codewalkers. Alter table structure conditional adding columns if they dont already exist General SQL help. Help with MySQL, MSSQL, Oracle, mSQL, PostgreSQL, etc.
ASP Free and Iron Speed Designer are giving away $5,500+ in FREE licenses. Iron Speed's RAD CASE toolset can save up to 80% of your coding time. One free license per week, one perpetual license per month!
Receive the tools necessary to be the rock star of your field. Our 12-month program teaches you the evolving world of multi-channel marketing as well as the complex issues and opportunities found in the industry.
The ASP Free website provides in-depth information on the latest developer tools available from Microsoft. Our cadre of writers, highly experienced industry experts, reveals the best ways to use established technologies as well as new and emerging technologies. Our coverage of Microsoft's development and administration technologies is among the most respected in the IT industry today. .
Posts: 35
Time spent in forums: 7 h 3 m 4 sec
Reputation Power: 6
MySQL 5 - Alter table structure conditional adding columns if they dont already exist
Hi,
A bit of background first.
I have a laptop that I use for development. This has php, mysql and apache installed and working. I have a separate production box that has the same installation.
When I make changes to the database structure, I have to do it twice. Once on the development box and again later on the production box when the code is sent across.
At the table level, I can do a conditional creation. The sql looks like
CREATE TABLE IF NOT EXISTS `item_master` (
`our_pn` varchar(20) default '',
`description` varchar(100) default '',
etc etc
If the table already exists and of course is full of data, I only want to add a new column. then I can do that. The SQL looks like:-
ALTER TABLE `item_master`
ADD `sell_price` VARCHAR( 10 ) NULL DEFAULT '0' AFTER `description` ;
THE QUESTION IS
What I would like to do is write some sql to only add the column if it does not exist.
Something like this
ALTER TABLE `item_master`
if not exists 'sell_price' then
ADD `sell_price` VARCHAR( 10 ) NULL DEFAULT '0' AFTER `supply` ;
end if
But of course it is not as simple as this or I would not be asking he question here.
Posts: 57
Time spent in forums: 1 Day 14 h 40 m 15 sec
Reputation Power: 6
it really won't matter. if you run an ALTER TABLE statement and try to add a column that already exists in the table you will get an error, specifically error number 1060. so where is the problem?
as a side note, how would this situation ever arise? presumedly you have essentially identical versions of your project on both machines with the only difference being the actual data in the database. assuming that your workflow is not bi-directional (which it definitely should not be) there should never be a case that the production server sees a change before the development server.
Posts: 35
Time spent in forums: 7 h 3 m 4 sec
Reputation Power: 6
Clarifications
Quote:
Originally Posted by jcarouth
it really won't matter. if you run an ALTER TABLE statement and try to add a column that already exists in the table you will get an error, specifically error number 1060. so where is the problem?
Errors are dangerous things. The danger is that if I allow errors then I may get used to seeing errors and mis an important error.
Quote:
Originally Posted by jcarouth
as a side note, how would this situation ever arise? presumedly you have essentially identical versions of your project on both machines with the only difference being the actual data in the database. assuming that your workflow is not bi-directional (which it definitely should not be) there should never be a case that the production server sees a change before the development server.
As I make changes to scripts on the development box, I would like to build a SQL file that I can run on the production box when I am ready to do the migration. On the development box this sql script would be run many times as fields were added.
For example, lets say I decide to add a field. so I add a line to the sql script and run it on the development box.
The I decide to add a second field. I add this to the script. The sql script now as two add field commands in it. Just what I need for the production box but it will generate an error when I run it on the development box because the first added field already exists in the mysql table on the development box.
Posts: 354
Time spent in forums: 1 Week 3 Days 7 h 8 m 8 sec
Reputation Power: 6
There are a couple of ways of getting a list of columns and then searching the results.
One would be a SHOW COLUMNS query. Another would be the php mysql_fetch_field() statement following a SELECT * query.
If you are adding columns frequently, either you are not spending enough time during the planning and definition phase or you are doing something like storing same information in columns that should in fact be stored in rows in its own table.
Posts: 57
Time spent in forums: 1 Day 14 h 40 m 15 sec
Reputation Power: 6
first of all i agree with what cwf said about you not spending enough time or though processes if you are continually making schema modifications.
i disagree with your thoughts about errors because the error messages with their corresponding error codes can be very useful in working with a database. for example if i have a requirement that every email is only allowed to be used one time i will add a unique index on that column. then when inserting a record, if there is an error and that error corresponds to error code 1062, i know there was a conflict with a duplicated value in a contrained column. this is an added level of data integrity.
so while it is important that you don't simply dismiss errors in general it is equally important that you learn to handle errors gracefully and leverage errors to make the most of your applications. the simple truth is errors are going to happen.
in regards to your development method is sounds like you need to work on your patching processing. a patch should only contain changes to code/schema that are relevant to the patch. so previous patch changes should not be included in the current patch's changes.
Posts: 1
Time spent in forums: 14 m 10 sec
Reputation Power: 0
Conditionally add columns to a table only if they don't already exist
Here's your answer. Enjoy.
Code:
delimiter '//'
CREATE PROCEDURE addcol() BEGIN
IF NOT EXISTS(
SELECT * FROM information_schema.COLUMNS
WHERE COLUMN_NAME='new_column' AND TABLE_NAME='the_table' AND TABLE_SCHEMA='the_schema'
)
THEN
ALTER TABLE `the_schema`.`the_table`
ADD COLUMN `new_column` bigint(20) unsigned NOT NULL default 1;
END IF;
END;
//
delimiter ';'
CALL addcol();
DROP PROCEDURE addcol;
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
