添加Result表约束
USE MySchool
GO
ALTER TABLE Result --主键约束(学号、科目号、日期)
ADD CONSTRAINT PK_Result PRIMARY KEY
(StudentNo, SubjectNo, ExamDate)
ALTER TABLE Result --默认约束(日期为系统当前日期)
ADD CONSTRAINT CK_ExamDate DEFAULT (getdate()) FOR ExamDate
ALTER TABLE Result --检查约束(分数不能大于100,小于0)
ADD CONSTRAINT CK_StudentResult CHECK
(StudentResult BETWEEN 0 AND 100)
ALTER TABLE Result --外键约束(主表Student和从表Result建立关系)
ADD CONSTRAINT FK_StudentNo
FOREIGN KEY (StudentNo) REFERENCES Student (StudentNo)
ALTER TABLE Result --外键约束(主表Subject和从表Result建立关系)
ADD CONSTRAINT FK_SubjectNo
FOREIGN KEY (SubjectNo) REFERENCES Subject (SubjectNo)
GO
使用联机帮助SQL
USE MySchool
GO
ALTER TABLE Result -- 删除检查约束
DROP CONSTRAINT ck_studentResult
ALTER TABLE Result -- 修改成绩表中成绩列的数据类型
ALTER COLUMN StudentResult DECIMAL (5, 2)
ALTER TABLE Result -- 添加检查约束
ADD Constraint ck_studentResult CHECK
(studentresult BETWEEN 0 AND 100)
GO