KB Issue

When you add a new column to an existing table, it is not yet added to the Constraint. This will cause the error.

Issue Details

When you add a new record to your table without the new column being added to the Constraint for that table, it will cause the following error.

No row was updated.
The data in row 530 was not committed.
Error Source: Framework Microsoft SqlClient Data Provider.
Error Message: The INSERT statement conflicted with the CHECK constraint "CK_YourTable_OneRecord". The conflict occurred in database "YourDatabase", table "dbo.YourTable".
The statement has been terminated.

Correct the errors and retry or press ESC to cancel the changes.

Recreate Issue

When you add a new column to a table and try to add a value to it, but the table has a Constraint

Copy

Search Site

Search Google

connected to all columns, except the new one.

The existing Constraint may look something like this.

[SQK Server - Existing Constraint]

CFFCS | CarrzSynEdit: | SQL Script

((((
case when [FirstID] IS NULL then (0) else (1) end
+case when [SecondID] IS NULL then (0) else (1) end)
+case when [ThirdID] IS NULL then (0) else (1) end)
+case when [FourthID] IS NULL then (0) else (1) end)
=(1))

Pay Attention
When you edit the Constraint, make sure you pay attention to the number of parentheses. In this example, we have 4 openings and 4 closings on each line, except the first line, which has its closing at the end.

Resolve IssueFollow the instructions to resolve this issue.

Open SSMS (SQL Server Management Studio)
Expand your Server/Instance
Expand the Database
Expand the Table
Test the record on the new column first to get the true error.
Right-click the table and choose [Edit All Rows]
Once the Table opens, go to the bottom of the list.
Enter a single record, and you should get the warning above.
Press [OK] then the Esc

Copy

Search Site

Search Google
key to get out of the edit.
Edit the Constraint
Expand Constraints
Right-click the CK_YourTable_OneRecord

Copy

Search Site

Search Google
and choose [Modify]
When the dialog opens, on the right side, click the ellipse button on [Expression]
Add in the new column name, making it look similar to the existing lines.
The best way to do it is to copy the text out and place it in Notepad or another editor (I use Notepad++)
And split the lines, and duplicate one line (Notepad++ press Ctrl+D to duplicate a line)
Here is my example.
(I added in the FifthID)
[SQK Server - Updated Constraint]
CFFCS | CarrzSynEdit: | SQL Script
```
(((((case when [FirstID] IS NULL then (0) else (1) end
+case when [SecondID] IS NULL then (0) else (1) end)
+case when [ThirdID] IS NULL then (0) else (1) end)
+case when [FourthID] IS NULL then (0) else (1) end)
+case when [FifthID] IS NULL then (0) else (1) end)
=(1))
```
Now, since we added another row, make sure you add the opening parenthesis.
Once you have added the new column, and you have made sure you added the opening parenthesis, click [OK]
If you get the following warning,
Error validating constraint
Microsoft SQL Server Management Studio
Error validating constraint 'CK_modified_time_OneRecord'.
Do you want to edit the Constraint?
[Yes][No][Help]
Choose [Yes], verify that it is correct, and try again.
Once you successfully click [OK], if the table is open in [Design], make sure you save it.
Then execute the table, and you can now add records to the new column.