EF Core - Code First - Add new non-null column to existing table with existing data
Hi there,
I would like to add a new non-null column to an existing table which contains existing data. This is a simple Created field, and I want to set it to DateTime.UtcNow for all existing rows.
Of course, if I just add the column and create the migration, this will not work well as the migration will fail with a constraint error.
The common solution I see for this is to add it as a nullable in one migration, populate the data using seeding and then make it non-nullable in another migration.
I don't really want to use seeding, I'd rather just have the migration add it as a nullable column, do some kind of update to set all the missing values then immediately make it non-nullable, all in one migration.
Is this possible? Thanks!
10 Replies
You can give it a default value in the config
.HasDefaultValueSql("CURRENT_TIMESTAMP") for exampleWould that not fill it with a default value for all newly created rows? If a value is missing (which should not be possible but life is life) i'd rather it be null and throw a database constraint error
Yeah, it'll default to the time of creation for this row
Which seems appropriate for a
CreatedAt columnThere is already infrastructure in place which does not use HasDefaultValueSql to manage setting of various dates and times based on context
My aim here is not to worry necessarily about what is being put in, because this answer could apply to any old column
Then yeah. One migration that accepts nulls, fill the column with whatever weird thing you have set up, another migration ton turn nullability off
Whats your thoughts on making two and merging them into one?
Two what, columns?
Or migrations?
Migrations
If the latter, then you'd need to somehow insert data mid-migration
Indeed, that's what I'll need to work out
Interestingly, my migration which was meant to accept nulls... doesn't
Achieved it by adding some SQL into the migration, thank you!