check constraint sql server

Question

I have tables as you can see in the picture.

so what I want to do...? I want to make a constraint that checks of the ID that can be just repetition two time and not more than that in the same table like when he does an exam 2018-01-01 he can repeat the exam after 15 days ...? it means that have a chance to repeat the exam two times in the month, not more.

I make one

ALTER TABLE [dbo].[ESAMI]
ADD CONSTRAINT CHK_ColumnD_ESEMI CHECK > 2

but not working.

so how can I do that? thanks in advance.

Answer 1

I find the solution thanks to Mr: denis.

you have first to create a function like this:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION DBO.ESAMI_CHECK
(
    -- Add the parameters for the function here
    @IdStudente INT
)
RETURNS INT

AS
BEGIN
    RETURN (
        SELECT COUNT(*) 
        FROM ESAMI WHERE IdStudente=@IdStudente
        GROUP BY IdStudente 
    )   
END
GO

after that you have to make the constraint like this:

ALTER TABLE [dbo].[ESAMI]
ADD CONSTRAINT CHK_ColumnD_ESEMI
CHECK (DBO.ESAMI_CHECK(IdStudente) <= 2)

that's all and it will work perfectly. thanks to all and have a good day.

Answer 2

You should write:

ALTER TABLE [dbo].[ESAMI]
ADD CONSTRAINT CHK_ColumnD_ESEMI UNIQUE(IdEsami)