《检查约束sql》由会员分享，可在线阅读，更多相关《检查约束sql（7页珍藏版）》请在金锄头文库上搜索。
1、检查约束检查约束 sqlsqluse mydbgo/*第六种重要的约束：check 检查约束一旦表中某列设置了检查约束，则在向表中添加数据时，会使用这个约束对输入的数据按照设置的逻辑进行检查。*/if exists(select name from sysobjects where name=item and xtype=u)drop table itemgocreate table item(itemId int identity(1,1) primary key,itemname nvarchar(10) not null unique,price money not null,quant
2、ity int not null constraint ch_quantity check(quantity0),)go/*第一种情况：将具体的检查逻辑写在括号中。1、商品数量至少要大于 0 quantity0*/insert into item(itemname,price,quantity) values(香烟,5.5,100)goinsert into item(itemname,price,quantity) values(火柴,1.0,-10)goinsert into item(itemname,price,quantity) values(火腿,3.5,0)goselect *
3、from itemgo-删除检查约束，再向表中填充包括负数的记录alter table item drop constraint ch_quantitygoupdate item set quantity = 10 where itemid=1goalter table item add constraint check_qty check(quantity0)goupdate item set quantity = abs(quantity) where quantity0 and quantity = = (!= 0 or quantity0 and sage120),constraint
4、 ch_sex check(ssex in(男,女)godelete from student where sage=170go/*第三种：在表中的某列中通过检查约束，让其有某些固定的值。*/if exists(select name from sysobjects where name=student and xtype=u)drop table studentgocreate table student(sid char(8) primary key,sname nvarchar(6) not null,sage tinyint not null,ssex nchar(1) not nul
5、l,)goalter table student addcheck(sid like bd0-90-90-90-90-90-9)goinsert into student values(bd080405,xx,18,男)goinsert into student values(bd112456,yy,17,女)go-在年龄列添加默认约束，为 17-在性别列，添加默认约束，为男alter table student add default 17 for sage,default 男 for ssexgoinsert into student(sid,sname) values(bd123456,
6、李四)goselect * from studentgo-使用循环向表中添加 15 条记录，男生，17 岁，改名为学生while (select count(*) from student)1800begindeclare i intdeclare name nvarchar(6)set i = 1set name = 学生+ cast(i as varchar)declare id varchar(30)set id = bd+cast(floor(rand()*10) as varchar)+cast(floor(rand()*10) as varchar)+cast(floor(rand()*10) as varchar)+cast(floor(rand()*10) as varchar)+cast(floor(rand()*10) as varchar)+cast(floor(rand()*10) as varchar)insert into student(sid,sname)values(id,name)endgo
