In SQL can one add a column to an existing table if the column doesn’t exist?
I have a database that is in use and I want to add a column to the table.
I don’t want to loose the contents of any of the current columns and rows.
I have to add this column in the next release of software… So how do you tackle these issues of database versions vs versions of your own software?
usually something like a version stored in the DB in a table
I check the version in the table & run each update until I’m at the one the version of the software uses
Each update is a simple list of sql (creates, drops, updates, alters etc) that move the schema to that version from the prior one
Merci Morman.
IF NOT EXISTS
(
SELECT NULL FROM IMFORMATION_SCHEMA.COLUMNS
WHERE table_schema = 'MyDatabaseName'
AND table_name = `MyTableName`
AND column_name = 'Version'
)
THEN
ALTER TABLE `MyTableName` ADD `Version` int(1) NOT NULL default 1;
END IF;
This is supposed to be implemented as a stored procedure. (Of which I know not.)
(I usually just embed the sql in my code and run it if necessary with mdb.SQLExec.)
but I assume that this is like a template and all I should have to do is call an sql procedure
AddTable(TheDatabaseName, TheTableName, TheColumnName)
so I guess you create some sql script store it on the server and execute it as you would an sql select statement.
The details elude me… because I can’t see how you would specify the columns data int(1) NOT NULL default 1;
To expand on Norman’s reply: You would have a column in a table that holds the current database schema version - I normally put this in a parameters table where I store other application specific data (like the name of the company that owns the software). Then during application start (App.open event) I check the database schema - Is it less than expected? Run the upgrade procedures - Is it more than expected? The application is too old alert the user then abort.
To add a column to a table you would use sqlexecute(“ALTER TABLE xxx ADD COLUMN yyy ;”).
NB you cannot add a column to a table with rows and specify NOT NULL so you would need to either set a default value or populate each row with a value then alter the table again.
HTH
For most SQL databases, you can just use the column adding code and it will create it if possible, or simply do nothing if the column already exists.
TheDB.SQLExecute “alter table yourTable Add columnName varchar”
You can call that as much as you want with no issue, so it might help to simply have an encompassing Update Table method that will add any possibly new columns to the table. Though this depends on where/when you might be updating the table/columns.
I’m decidedly agnostic 