1

I have an existing SQL Server database with tables created via EF Update-Database migration. I have a couple of models that looks like the following:

[Table("ETable")]
public class ETable
{
    [Key]
    public int Id { get; set; }

    // Other properties go here

    public DateTime EDate { get; set; }
}

Later on I decided to use DateTime2 globally but learned that I have to drop constraint objects like 'DF_ETable_EDate__######' which are automatically created in order to get rid of error similar to what this post is about when trying to update the database.

The question is, are there any implications with dropping these constraint objects? If it is beneficial to keep them, how do I add them back?

Patee Gutee
  • 292
  • 2
  • 19
  • 1
    Constraints are an important part of the database. There are different types of constraints, such as a default constraint, a foreign key constraint and a unique constraint, all serves a different purpose. Removing a constraint will directly effect the database capability of keeping data integrity, or generating default values to columns. in SQL Server, you can't alter a constraint, but you can drop it and create a new one instead. – Zohar Peled Oct 10 '19 at 15:15
  • @ZoharPeled In my case I have constraint for the field EDate 'DF_ETable_EDate__######' automatically added for some reason I don't know. I have just dropped it. Now how do I add it back? – Patee Gutee Oct 10 '19 at 15:18
  • 1
    That `DF` one will be created for a column default – Martin Smith Oct 10 '19 at 15:20
  • 1
    Based on the `DF` at the start of the name I'm guessing it's a default constraint, you add it by using an alter table statement. For instance, this will add a default value of the current datetime2 for each new row: `alter table ETable add constraint df.... default(sysdatetime()) for (edate)` – Zohar Peled Oct 10 '19 at 15:22
  • @ZoharPeled how do I do it via database migration ie: `AlterColumn("dbo.ETable", "EDate", c => c.DateTime(nullable: false, precision: 0, storeType: "datetime2"));`. Can you post it as answer? So I can at least upvote it? – Patee Gutee Oct 10 '19 at 15:29
  • I have no idea how to do that using entity framework. I'm not using that ORM. – Zohar Peled Oct 10 '19 at 15:31

1 Answers1

1

Like the others have commented, you're mainly dealing with default constraints on your DateTime fields.

Depends on your business rules, you can safely remove the constraint if not required. Otherwise, you can set it to a default value:

To set a default to an arbitrary date:
AlterColumn("dbo.ETable", "EDate", c => c.DateTime(nullable: false, defaultValueSql: "CONVERT(datetime, '2019-01-01')"));

To set default to current date:
AlterColumn("dbo.ETable", "EDate", c => c.DateTime(nullable: false, defaultValueSql: "GETDATE()"));
corix010
  • 551
  • 8
  • 25