I hope you can help me.
I would like to create a check constraint that ensure the column INKOOPBEDRAG
has to be higher than PRIJS_PER_D
.
In the table INKOOPOVEREENKOMST.[BARCODE]
it is a FK to ARTIKEL.[BARCODE]
.
I have created the following CHECK
constraint, but I get an error:
CHECK CK_inkoopbedraghogerdprijs CHECK (INKOOPOVEREENKOMST.[INKOOPBEDRAG] > [artikel][d_prijs])
Advertisement
Answer
I solved this using the following:
GO CREATE FUNCTION dbo.fnCHK_InkoophogerAlsDPrijs (@barcode INT, @INKOOPBEDRAG SMALLMONEY) RETURNS BIT --1 or 0 AS BEGIN RETURN CASE WHEN EXISTS( SELECT * FROM Artikel WHERE barcode = @barcode AND [Prijs_per_d] < @INKOOPBEDRAG) THEN 1 ELSE 0 END END; GO
Then I added a Constraint
as follows:
ALTER TABLE [Inkoopovereenkomst] ADD CONSTRAINT CHK_InkoophogerAlsDPrijs CHECK (dbo.fnCHK_InkoophogerAlsDPrijs(Barcode, Inkoopbedrag) = 1);