1.修改教师表teachers(该表的结构见教材附录B),为protitle列添加非空约束。
ALTER TABLE teachers
MODIFY COLUMN protitle NOT NULL;
2.修改系部表departments(该表的结构见教材附录B),为dptcode列添加主键约束,为dptname列添加唯一性约束,约束名为UN_departments_dptcode,然后查看约束信息。
ALTER TABLE departments
ADD CONSTRAINT pk_dptcode PRIMARY KEY (dptcode),
ADD CONSTRAINT UN_departments_dptcode UNIQUE (dptname);
SELECT constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name = 'departments';
3.删除系部表departments的主键约束。
ALTER TABLE departments
DROP CONSTRAINT pk_dptcode;
4.修改学生表students,为stdid列添加自增约束,为gender列添加默认约束,默认值设为“男”,为gender列添加检查约束,取值只能为‘男’和‘女’,
ALTER TABLE students
MODIFY COLUMN stdid INT AUTO_INCREMENT,
MODIFY COLUMN gender VARCHAR(10) DEFAULT 'male' CHECK (gender IN ('male', 'female'));
5.修改学生表students,删除stdid列上的自增约束。
ALTER TABLE students
MODIFY COLUMN stdid INT;
6.修改选修表studying,为stdid列添加外键约束,该列的取值要参考students表中的stdid列,如果删除或更新了students表中的一条记录,则studying表相关的记录也相应删除或更新。
ALTER TABLE studying
ADD CONSTRAINT fk_stdid_students
FOREIGN KEY (stdid)
REFERENCES students(stdid)
ON DELETE CASCADE
ON UPDATE CASCADE;
7.修改选修表studying,删除约束名为studying_ibfk_1的外键约束。
ALTER TABLE studying
DROP FOREIGN KEY studying_ibfk_1;