Skip to content
Advertisement

Create CHECK constraint SQL

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])

enter image description here

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);

1 People found this is helpful
Advertisement