SQL server 建表时候 如何建立两列之间的约束关系

Question

我想建立一个“学生参加项目的表”，学生参与项目（学号，项目号）。 其中学号包括研究生学号和本科生学号，本科生学号以数字【1】开头，研究生学号以字母【S】开头。一个本科生只能参加一个项目，一个研究生可以参加多个项目。
我想在表中体现区分本科生和研究生参加项目数量的不同来限制输入，该如何做到？ 例如 学号以1开头的学生只能有一条参与项目记录，多输入就报错。学号以S开头的学生就可以在表中建立多条数据。

Answer 1

建立测试表
if not object_id(N'T') is null
drop table T
Go
Create table T([学号] nvarchar(22),[项目号] int)
Go建立触发器：
CREATE TRIGGER dbo.T_tri
ON [dbo].T
INSTEAD OF INSERT
AS
    BEGIN
        DECLARE
            @sid   NVARCHAR(22),
            @pid int; 
        DECLARE cur CURSOR FOR
            SELECT * FROM Inserted;
        OPEN cur; --打开游标
        FETCH NEXT FROM cur
        INTO
            @sid,
            @pid; --取数据
        WHILE (@@fetch_status = 0) --判断是否还有数据
            BEGIN
                IF (@sid LIKE '1%' AND (SELECT COUNT(1) FROM T WHERE 学号=@sid)>0) OR (@sid LIKE 's%' AND (SELECT COUNT(1) FROM T WHERE 学号=@sid)>4)
BEGIN
     RAISERROR('不允许插入数据！',18,1)  
  END  
ELSE
BEGIN
INSERT into T(学号,项目号)VALUES(@sid,@pid)
END
                FETCH NEXT FROM cur
        INTO
            @sid,
            @pid; --取数据
            END;
        CLOSE cur; --关闭游标
        DEALLOCATE cur;
    END;
GO
第二次插入
INSERT INTO dbo.T
    (
        学号,
        项目号
    )
VALUES
    (
        N'121', -- 学号 - nvarchar(22)
        1    -- 项目号 - int
    )

Answer 2

USE tempdb
GO
IF OBJECT_ID('trig_project_stu_mid_I') IS NOT NULL DROP TRIGGER trig_project_stu_mid_I
IF OBJECT_ID('stu') IS NOT NULL DROP TABLE stu
IF OBJECT_ID('project') IS NOT NULL DROP TABLE project
IF OBJECT_ID('project_stu_mid') IS NOT NULL DROP TABLE project_stu_mid
GO
CREATE TABLE stu(
sno VARCHAR(10) PRIMARY KEY,
sname NVARCHAR(10) NOT NULL
)
CREATE TABLE project(
pno VARCHAR(50) PRIMARY KEY,
pname NVARCHAR(50) NOT NULL 
)
GO
CREATE TABLE project_stu_mid(
id INT IDENTITY(1,1) PRIMARY KEY,
sno VARCHAR(10),
pno VARCHAR(50)
)
GO
SET NOCOUNT ON;
INSERT INTO stu VALUES ('101','本科生小王')
INSERT INTO stu VALUES ('102','本科生小张')
INSERT INTO stu VALUES ('S01','研究生小刘')
INSERT INTO project VALUES ('001','长江大桥')
INSERT INTO project VALUES ('002','三峡小桥')
INSERT INTO project_stu_mid(sno,pno) VALUES ('101','001')
INSERT INTO project_stu_mid(sno,pno) VALUES ('S01','001')
GO
-- =============================================
-- Author: yenange
-- Create date: 2018-11-12
-- Description: 如果本科生在 项目表 有多条记录，报错
-- =============================================
CREATE TRIGGER dbo.trig_project_stu_mid_I 
   ON  dbo.project_stu_mid 
   AFTER INSERT
AS 
BEGIN
SET NOCOUNT ON;
IF EXISTS(SELECT * FROM project_stu_mid AS a INNER JOIN INSERTED AS b 
ON a.sno=b.sno AND a.pno!=b.pno
AND b.sno LIKE '1%'
)
BEGIN
RAISERROR('本科生只能参与一个项目',16,1);
ROLLBACK;
END
END
GO
INSERT INTO project_stu_mid(sno,pno) VALUES ('102','001')
INSERT INTO project_stu_mid(sno,pno) VALUES ('S01','002')
INSERT INTO project_stu_mid(sno,pno) VALUES ('101','002')
/*
消息 50000，级别 16，状态 1，过程 trig_project_stu_mid_I，行 17 [批起始行 52]
本科生只能参与一个项目
消息 3609，级别 16，状态 1，第 55 行
事务在触发器中结束。批处理已中止。
 */
GO
SELECT s.*,p.pname
  FROM project_stu_mid AS m INNER JOIN stu AS s ON m.sno=s.sno
INNER JOIN project AS p ON m.pno=p.pno
ORDER BY s.sno