CREATE TABLE auto append default columns?

Question

In MySQL, is it possible to append default columns after creation or create them automatically? A brief overview is this:

All tables must have 5 fields that are standardized across our databases (created_on, created_by, row_status etc). Its sometimes hard for developers to remember to do this and/or not done uniformly. Going forward we'd like to automate the task some how. Does anyone know if its possible to create some sort of internal mysql script that will automatically append a set of columns to a table?

After reading through some responses, I think i'd rephrase the question bit, rather than making it an autoamtic task (i.e EVERY Table), make it function that can be user-triggered to go through and check for said columns and if not add them. I'm pretty confident this is out of SQL's scope and would require a scripting language to do, not a huge issue but it had been preferable to keep things encapsulated within SQL.

Answer 1

i would say first - don't do that. make an audit table seperately - and link with triggers.

otherwise, you will need to feed your table construction through a procedure or other application that will create what you want.

Answer 2

I'd first defer to Randy's answer - this info is probably better extracted elsewhere.

That said, if you're set on adding the columns, ALTER TABLE is probably what you're looking for. You might consider also including some extra logic to determine which columns are missing for each table.

Answer 3

I'm not very aware of MySQL specific data modeling tools, but there's no infrastructure to add columns to every table ever created in a database. Making this an automatic behavior would get messy too, when you think about situations where someone added the columns but there were typos. Or what if you have tables that are allowed to go against business practice (the columns you listed would typically be worthless on code tables)...

Development environments are difficult to control, but the best means of controlling this is by delegating the responsibility & permissions to as few people as possible. IE: There may be 5 developers, but only one of them can apply scripts to TEST/PROD/etc so it's their responsibility to review the table scripts for correctness.