----一般给表中字段设定约束都是在创建表时设置
----在下面的代码中演示如何在修改表（表已经创建完毕）时给字段设置约束;需要使用alter table语句(作用：修改表的结构)
create table student
(
stuid int,
stuname varchar(20),
stuage int,
stuaddress varchar(50),
stucardid varchar(20)
)
create table marks
(
examno varchar(20),
stuid int,
writtenExam float,
labExam float
)
--表已经存在，如何在表中添加新字段和删除字段
--添加字段stuphone
alter table student add stuphone varchar(20)
--删除字段stuphone
alter table student drop column stuphone
--给字段stuid添加非空约束
alter table student alter column stuid int not null
--给字段stuid添加主键约束
alter table student add constraint pk_stuid primary key(stuid)
--给字段stuname添加非空约束
alter table student alter column stuname varchar(20) not null
--给字段stuage添加检查约束
alter table student add constraint chk_stuage check(stuage between 0 and 100)
--给字段stuaddress添加默认约束
alter table student add constraint df_stuaddress default('地址不详') for stuaddress
--给字段stucardid添加唯一约束
alter table student add constraint uq_stucardid unique(stucardid)
--给marks表examno添加非空约束
alter table marks alter column examno varchar(20) not null
--给marks表examno添加主键约束
alter table marks add constraint pk_examno primary key(examno)
--给marks表stuid添加外键约束
alter table marks add constraint fk_stuid foreign key(stuid) references student(stuid)
--给marks表writtenExam添加检查约束
alter table marks add constraint chk_writtenexam check(writtenexam>=0 and writtenexam<=100)
--给marks表labexam添加检查约束
alter table marks add constraint chk_labexam check(labexam>=0 and labexam<=100)
--查看所有的系统表
select name from sys.all_objects where type = 'S' order by name
