写一个约束来强制一个值至少出现一次

Question

我正试图写一个检查约束来强制在一个表中至少出现一个值:

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

我得到一个错误:

必须声明标量变量"@Instances"

Answer 1

你不能用变量来解决这个问题你需要一个函数来解决

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<此处小提琴>