利用T-SQL语句为Result表和Student表创建一个外键约束

假设Result表中有一个名为student_id的列，它是Student表中id列的外键。

下面是创建外键约束的T-SQL语句：

ALTER TABLE Result
ADD CONSTRAINT FK_Result_Student
FOREIGN KEY (student_id)
REFERENCES Student(id);

这条语句将在Result表中创建一个名为FK_Result_Student的外键约束，它将student_id列与Student表中的id列进行关联。如果Student表中的id列中没有与Result表中的student_id列匹配的值，则插入或更新操作将失败。

相关问题

以下所有内问题请分别用SQL语句的两种方式实现并通过相关操作进行验证。 (1)在SC表的Sno字段创建外键约束，引用Student表的Sno字段，要求实现级联删除; (2）在SC表的Cno字段创建外键约束，引用Course表的Cno字段，要求被同学选修的课程不能删除; (3）在SC表的Tno字段创建外键约束，引用Teacher表的教工号字段，要求实现级联更新和删除; (4）在Teacher表上创建Unique约束Unique_TNP，不允许出现姓名和联系电话完全一样的教师记录; (5）在Student表中创建Default约束Df_Ssex，当用户插入学生记录时，如果没有提供性别字段Ssex的值则用“男”填充; (6）在学生表Student中创建CHECK约束CK_Sbirth，以保证用户输入的出生日期字段Sbirth 的值不超过当前日期（可使用系统函数 getdate()获取系统当前日期)。

(1) 第一种方式：

ALTER TABLE SC 
ADD CONSTRAINT FK_SC_Student
FOREIGN KEY (Sno)
REFERENCES Student(Sno)
ON DELETE CASCADE;

第二种方式：

CREATE INDEX IX_SC_Sno ON SC(Sno);
ALTER TABLE SC 
ADD CONSTRAINT FK_SC_Student
FOREIGN KEY (Sno)
REFERENCES Student(Sno)
ON DELETE CASCADE;

验证：

删除Student表中的一条记录，发现SC表中该学生选修的课程记录也被级联删除了。

(2) 第一种方式：

ALTER TABLE SC 
ADD CONSTRAINT FK_SC_Course
FOREIGN KEY (Cno)
REFERENCES Course(Cno)
ON DELETE NO ACTION
ON UPDATE NO ACTION;

ALTER TABLE SC 
ADD CONSTRAINT CK_SC_Course
CHECK (NOT EXISTS (SELECT * FROM SC WHERE Cno = Course.Cno));

第二种方式：

CREATE INDEX IX_SC_Cno ON SC(Cno);
ALTER TABLE SC 
ADD CONSTRAINT FK_SC_Course
FOREIGN KEY (Cno)
REFERENCES Course(Cno)
ON DELETE NO ACTION
ON UPDATE NO ACTION;

ALTER TABLE SC 
ADD CONSTRAINT CK_SC_Course
CHECK (NOT EXISTS (SELECT * FROM SC WHERE Cno = Course.Cno));

验证：

尝试删除Course表中被选修的一门课程，发现删除失败并提示有约束关系。

(3) 第一种方式：

ALTER TABLE SC 
ADD CONSTRAINT FK_SC_Teacher
FOREIGN KEY (Tno)
REFERENCES Teacher(Tno)
ON DELETE CASCADE
ON UPDATE CASCADE;

第二种方式：

CREATE INDEX IX_SC_Tno ON SC(Tno);
ALTER TABLE SC 
ADD CONSTRAINT FK_SC_Teacher
FOREIGN KEY (Tno)
REFERENCES Teacher(Tno)
ON DELETE CASCADE
ON UPDATE CASCADE;

验证：

修改Teacher表中的一条记录的教工号，发现SC表中所有该教师参与的选课记录的Tno字段也被更新了。

(4) 第一种方式：

ALTER TABLE Teacher 
ADD CONSTRAINT Unique_TNP 
UNIQUE (Tname, Tphone);

第二种方式：

CREATE UNIQUE INDEX IX_Teacher_TNP ON Teacher(Tname, Tphone);

验证：

尝试插入一条姓名和联系电话完全相同的记录，发现插入失败并提示违反唯一性约束。

(5) 第一种方式：

ALTER TABLE Student 
ADD CONSTRAINT Df_Ssex 
DEFAULT '男' FOR Ssex;

第二种方式：

ALTER TABLE Student 
ADD CONSTRAINT Df_Ssex 
DEFAULT '男' 
WITH VALUES;

验证：

插入一条没有提供Ssex字段值的记录，发现Ssex字段被自动填充为“男”。

(6) 第一种方式：

ALTER TABLE Student 
ADD CONSTRAINT CK_Sbirth 
CHECK (Sbirth <= GETDATE());

第二种方式：

CREATE FUNCTION Check_Sbirth (@Sbirth DATE)
RETURNS BIT
AS 
BEGIN
    DECLARE @Result BIT = 1;
    IF (@Sbirth > GETDATE())
        SET @Result = 0;
    RETURN @Result;
END;

ALTER TABLE Student 
ADD CONSTRAINT CK_Sbirth 
CHECK (dbo.Check_Sbirth(Sbirth) = 1);

验证：

尝试插入一条出生日期在当前日期之后的记录，发现插入失败并提示违反CHECK约束。

(1) “学生”表Student由学号(Sno)、姓名(Sname)、性别(Ssex)、年龄(Sage)、所在系(Sdept)五个属性组成，可记为： Student(Sno,Sname,Ssex,Sage,Sdept) Sno 为主关键字。 (2) “课程”表Course由课程号(Cno)、课程名(Cname)、先修课号(Cpno)、学分(Ccredit)四个属性组成，可记为： Course(Cno,Cname,Cpno,Ccredit) Cno为关键字。 (3) “学生选课”表SC由学号(Sno)、课程号(Cno)、成绩(Grade)三个属性组成，可记为： SC(Sno,Cno,Grade) (SNO, CNO) 为关键字。 参照学生-课程数据库，两人一组（队友出题，你给出SQL语句）完成下列任务： (1)利用系统内置函数完成（例：查询平均成绩大于等于80分的课程名称和平均成绩。）。 (2)创建并调用一个标量函数（例：求N!。）。 (3)创建并调用一个内嵌表值函数。（创建一个函数，用于查询所有选修某门课程的学生的学号、课程号和成绩，并返回一个表格，然后调用该函数。）(4)创建并调用一个多语句表值函数。 (5)删除一个用户自定义函数。

(1) 查询平均成绩大于等于80分的课程名称和平均成绩。

SELECT Cname, AVG(Grade) 
FROM SC, Course 
WHERE SC.Cno = Course.Cno 
GROUP BY SC.Cno, Cname 
HAVING AVG(Grade) >= 80;

(2) 创建并调用一个标量函数（求N!）。

-- 创建函数
CREATE FUNCTION factorial(@n INT)
RETURNS INT
AS
BEGIN
    DECLARE @result INT = 1;
    WHILE @n > 0
        BEGIN
            SET @result = @result * @n;
            SET @n = @n - 1;
        END
    RETURN @result;
END

-- 调用函数
SELECT dbo.factorial(5); -- 输出 120

(3) 创建并调用一个内嵌表值函数。（查询所有选修某门课程的学生的学号、课程号和成绩，并返回一个表格）

-- 创建函数
CREATE FUNCTION fn_GetCourseStudents (@Cno CHAR(4))
RETURNS TABLE
AS
RETURN 
    SELECT Sno, Cno, Grade
    FROM SC
    WHERE Cno = @Cno;

-- 调用函数
SELECT * FROM dbo.fn_GetCourseStudents('C001');

(4) 创建并调用一个多语句表值函数。

-- 创建函数
CREATE FUNCTION fn_GetCourseAvgGrade (@Cno CHAR(4))
RETURNS @AvgGradeTable TABLE
    (Cno CHAR(4), Cname VARCHAR(20), AvgGrade DECIMAL(5,2))
AS
BEGIN
    DECLARE @Cname VARCHAR(20);
    DECLARE @AvgGrade DECIMAL(5,2);
    SELECT @Cname = Cname, @AvgGrade = AVG(Grade)
    FROM SC, Course
    WHERE SC.Cno = Course.Cno AND SC.Cno = @Cno
    GROUP BY SC.Cno, Cname;
    INSERT INTO @AvgGradeTable (Cno, Cname, AvgGrade)
    VALUES (@Cno, @Cname, @AvgGrade);
    RETURN;
END

-- 调用函数
SELECT * FROM dbo.fn_GetCourseAvgGrade('C001');

(5) 删除一个用户自定义函数。

DROP FUNCTION dbo.fn_GetCourseStudents;