-2

I want to add a new column called 'start_date' to a table called 'PROJECT', and I want this column to be mandatory.

I have tried this:

ALTER TABLE PROJECT
ADD start_date date NOT NULL;

This returns an ERROR:

ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'start_date' at row 1. My question is, how can I add a new column that is also mandatory without inserting unique values in?

alasdair hando
  • 1
  • The Date needs a default value since it can not be NULL. But the year 0000 does not exist. So add a valid default to your definition. – juergen d Apr 23 '23 at 04:11

1 Answers1

1

When you add a column to table that has existing data, that column is going to be added for every existing row. And since you've specified a 'not null' constraint, those rows now need some value. It doesn't have to be unique if you haven't specified a unique constraint, but it does require a NOT NULL value of the type specified.

Typically, when we do this kind of migration work, it goes through several steps of alterations to get to the final form. Maybe you add the column without the 'not null' constraint in one phase, then add the correct unique value, then apply the NOT NULL and UNIQUE constraints. It doesn't all have to happen in one step.

incongruentDoughnut
  • 187
  • 5