writing a constraint to enforce a minimum 1 occurence af a value-CodePudding

I am trying to write a check constraint to enforce at least 1 occurence of a value in a a table:

DECLARE @Instances INT
SELECT @Instances = COUNT (instructor) FROM Prof_Teach_Courses
SET @Instances =  (SELECT COUNT(instructor) FROM Prof_Teach_Courses)
GO
ALTER TABLE Prof_Teach_Courses
ADD CONSTRAINT count_1_instance CHECK (@Instances >= 1)
GO

i am getting an error :

Must declare the scalar variable "@Instances".

CodePudding user response：

you can't solve this with variables you need a function for that

CREATE TABLE Prof_Teach_Courses (col1 int, instructor int);

GO

CREATE FUNCTION CheckInstructor()  
RETURNS int  
AS   
BEGIN  
   DECLARE @Instances int  
   SELECT @Instances = COUNT(instructor) FROM Prof_Teach_Courses 
   RETURN @Instances  
END;

GO

ALTER TABLE Prof_Teach_Courses  
ADD CONSTRAINT count_1_instance CHECK (dbo.CheckInstructor() >= 1 );  
 
GO

db<>fiddle here