1

I have a project where I want to be able to manage several instances of the same database on several people's localhosts. I want each developer to be able to reset their DB back to the canonical origin no matter what state they get their DB into. To this end I maintain a standard database file. It holds the schema using CREATE TABLE table_name IF NOT EXISTS {

However, I want to be able to add to the starting table structures as needed as this project moves along. To do this, I would love to be able to do something like the following ALTER TABLE table_name ADD COLUMN IF NOT EXISTS column_name but that does not seem to exist. I did notice a stored procedure floating around the internet that solves this, but I wanted to know if something simpler is able to achieve the goal I have in mind. Thank you for the time and help.

usumoio
  • 3,500
  • 6
  • 31
  • 57
  • Possible duplicate of http://stackoverflow.com/questions/8219714/check-if-column-exists-before-alter-table-mysql?rq=1 – Todd Gibson Apr 25 '13 at 16:58
  • Duplicate of [check if column exists before ALTER TABLE](http://stackoverflow.com/q/8219714/1409082) – Jocelyn Apr 25 '13 at 16:59
  • I don't think this is a duplicate exactly. I am aware of the stored procedures that exist to solve this. I want to know if there is a way to do this without an SP – usumoio Apr 25 '13 at 17:02

2 Answers2

2

It won't be possible to do it with plain SQL. Stored procedure should work the best: read information_schema and check if the column is present. If not - execute the alter statement.

Max Dron
  • 81
  • 3
  • @Max_dron This seems to be the answer. I was hoping for a non SP solution, but you don't always get what you want. – usumoio Apr 26 '13 at 00:07
1

One option is to execute your ALTER statement without checking anything:

ALTER TABLE table_name ADD column_name VARCHAR(40);

(change VARCHAR(40) to whatever you need)

If the column didn't already exist, then the statement creates it.
If the column already existed, the statement does nothing and returns an error. Just ignore the error and continue.

Jocelyn
  • 11,209
  • 10
  • 43
  • 60
  • Trouble is, that syntax doesn't exist. For good reasons, too: picture yourself adding a column with type int, and the db ignores your statement because there already is a column with e.g. type date. :-) – Denis de Bernardy Apr 25 '13 at 17:15
  • 1
    @Denis Thanks for pointing my mistake. I knew the syntax was wrong but totally forgot to change it to a valid statement. I updated my answer. – Jocelyn Apr 25 '13 at 17:32
  • @Jocelyn I contemplated just ignoring the error, but I feel that is bad practice and I don't want to be in the habit of ignoring errors. I'm deploying this to allow people who are not DBAs to work with the DB as seamlessly as possible, and errors would break that flow. – usumoio Apr 25 '13 at 21:13
  • I usually don't like my queries to cause errors...but here it is a special case and I feel it is possible to make an exception. Or use the method described in the link I posted above the question. – Jocelyn Apr 25 '13 at 23:17