SQL Server 笔记：Constraint

Create table S (sno char(4) primary key) /*--主键：实体完整性*/

select * from s
insert into s values('s001')
insert into s values('s002') /*插入相同值会出错*/

drop table s

Create table S (
sno char(4) constraint pk_s primary key /*--主键约束：约束名：pk_s*/
)

Create table S (sno char(4) not null) /*非空属性*/

alter table s add constraint pk_s primary key(sno) /*增加一个主键约束*/

create table test
(
a int identity(2,10) /*实体完整性：设置一个标识列进行*/
)

drop table s
－－ ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
Create table S (
sno char(4) primary key,
age smallint default 20 /*--域完整性:默认值定义*/
)

select * from s
insert into s(sno) values('s001')
insert into s(sno) values('s002') /*插入相同值会出错*/

－－ ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
Create table S (
sno char(4) primary key,
age smallint not null check(age>=18 and age<=35) /*--检查约束*/
)

insert into s(sno,age) values('s001',18)

－－ ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
drop table s

Create table S (
sno char(4) primary key,
sname char(30) constraint uq_sname unique /*--唯一约束*/
)

insert into s values('s001','张三')
insert into s values('s002','李四')
insert into s values('s003','张三')

alter table s drop constraint uq_sname /*删除约束*/

－－ ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
create table K(
kno char(3) primary key,
kname char(50) /*--检查约束*/
)

insert into k(kno,kname) values('k01','SQL Server 数据库设计')
insert into k(kno,kname) values('k02','Windows2000系统管理和安全')

select * from k

create table X(
sno char(4),
kno char(3) foreign key references K(kno) /*--外键约束, foreigh key 可以不用声明*/
)

select * from X
insert into X values('s001','k01')
insert into X values('s002','k02')

delete from k where kno='k01' /*引用完整性*/

－－ ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
select * from s

alter table s add sname varchar(20) /*增加字段*/

alter table s alter column sname char(20) /*修改字段类型*/

alter table s drop column sname /*删除字段*/