Adding a JSON CHECK CONSTRAINT
for a table field with
ALTER TABLE [dbo].[Data]
ADD CONSTRAINT [JsonData must be formatted as JSON]
CHECK (IsJson([JsonData]) > 0)
works fine, but I want to make it work for Code First.
I have tried the Reverse Engineering Code First
, but it does not help me with this problem. Executing a Sql Command with the same code (Seed() method)
works very well, but this is not one of the solutions I would like to use:
protected override void Seed(MyContext context)
{
context
.Database
.ExecuteSqlCommand(
"ALTER TABLE [dbo].[Data]
ADD CONSTRAINT [JsonData must be formatted as JSON]
CHECK (IsJson([JsonData]) > 0)");
}
Is there any other way I can add a JSON Check Constraint
from Code First?
The syntax for creating a check constraint in an ALTER TABLE statement in SQL Server (Transact-SQL) is: ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK (column_name condition); table_name. The name of the table that you wish to modify by adding a check constraint.
The following will enable all constraints on a table: ALTER TABLE [TableName] CHECK CONSTRAINT ALL; But wait! This command will only make sure new data is checked not the existing data!
To disable a check constraint for INSERT and UPDATE statements. In Object Explorer, expand the table with the constraint and then expand the Constraints folder. Right-click the constraint and select Modify. In the grid under Table Designer, click Enforce For INSERTs And UPDATEs and select No from the drop-down menu.
WITH NOCHECK does so without checking existing data. So the confusing syntax WITH NOCHECK CHECK CONSTRAINT enables a constraint without checking existing data. From the manual: Specifies whether the data in the table is or is not validated against a newly added or re-enabled FOREIGN KEY or CHECK constraint.
I think that EF don't support any kind of CHECK constraints. The only thing that you can use is migration. See example in: Is it possible to add CHECK constraint with fluent API in EF7?
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With