SQL Server ALTER TABLE – Statement Conflicted with CHECK Constraint

alter-tableconstraintddlsql serversql-server-2012

I need to check datecom is less than datelivr

create table Commande
(
Numcom int identity primary key,
Datecom date,
Datelivr date,
Codecli int foreign key references clients (Codecli) 
)

alter table Commande 
    add constraint date_check check(datediff(day,Datecom,Datelivr) > 0)

but I get the error message:

The ALTER TABLE statement conflicted with the CHECK constraint
"date_check". The conflict occurred in database "tp4", table
"dbo.Commande".

How can this happen?

Best Answer

The error message is self-explanatory: Your check constraint is trying to enforce that all values in Datecom are at least one day earlier than Datelivr, but you must have at least one row where this is not true - either Datecom is the same day, later, or one of the values is NULL. Find those rows using:

SELECT Numcom, Datecom, Datelivr
FROM dbo.Commade
WHERE datediff(day,Datecom,Datelivr) <= 0
  OR Datecom IS NULL
  OR Datelivr IS NULL;

Related Solutions

Sql-server – How to add a constraint after I’ve truncated a table

Use WITH NOCHECK instead of WITH CHECK to add a constraint that conflicts with existing data. https://stackoverflow.com/questions/529941/whats-the-difference-between-with-check-add-constraint-followed-by-check-constr

Perhaps you are getting the error because you "2. Trucate a table." and not all of them? Maybe you are removing the Authors or the Books and are leaving the records in AuthorBooks that references these other tables.

Sql-server – Allow self-referential foreign keys to be null from an alter-table

If there are no illegal values in C.ParentID, then I am not clear what the problem might be. Is this Microsoft SQL Server?

I created a test case as follows:

Create Table dbo.Hier
(Id  INT IDENTITY Primary KEy,
 ParID INT NULL,
 Other varchar(10));
GO
 INSERT INTO dbo.Hier (ParID, Other) VALUES (NULL, 'Able');
 INSERT INTO dbo.Hier (ParID, Other) VALUES (1, 'Baker');
 INSERT INTO dbo.Hier (ParID, Other) VALUES (NULL, 'Charlie');
GO
ALTER TABLE dbo.Hier ADD 
      CONSTRAINT FK_Parent
      FOREIGN    KEY (ParID)
      REFERENCES Hier(ID);
GO
INSERT INTO dbo.Hier (ParID, Other) VALUES (3, 'Delta');
SELECT * FROM dbo.Hier;
GO
DROP TABLE dbo.Hier;
GO

Seems to work fine. If you are on a different SQL Server it might have different behavior. Or fluentMigrator may have a problem.

Best Answer

Related Solutions

Sql-server – How to add a constraint after I’ve truncated a table

Sql-server – Allow self-referential foreign keys to be null from an alter-table

Related Question