8

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?

1 Answer 1

11

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;
2
  • On the contrary; if the error message was self-explanatory, there wouldn't be a question about it! It says the conflict is with the statement itself instead of the data in the table, which is phrased poorly. Your explanation is much clearer.
    – James
    Commented Sep 21, 2023 at 14:25
  • @James Not interested in semantic bingo. Commented Sep 26, 2023 at 1:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.