MySQL default value - YEAR(CURRENT_DATE)

I'm trying to create table with column like

model_year smallint(4) not null default YEAR(CURRENT_DATE)

But there is an error (Invalid default value). What may I do to set default value for the model_year current year?

From the MySQL doc pages (my bold):

The DEFAULT value clause in a data type specification indicates a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column.

I suspect you'll either have to specify the real value when you insert the row.

If you didn't want to do that, you could allow NULLs and then periodically sweep the database with another process doing:

update table mytable set model_year = year(current_date) where model_year is null

but that's a kludge, and rather dangerous to boot, since there will be a period of time where NULLs exist in the column.

That's something all your queries would have to take into account, leading to ugliness like:

select a, b, model_year from mytable where model_year is not null
union all
select a, b, year(current_date) from mytable where model_year is null

I'd go the non-default route myself.

You would need a stored procedure (sorry, I do not know the MySQL syntax). You can set up a stored procedure that triggers on INSERT or UPDATE that looks for a NULL in the model year column and replaces it with an identical INSERT or UPDATE that sets the model year.