/********************************************************************************         *主题： SQL Server—  存在检测、建库、 建表、约束、外键、级联删除        *说明：本文是个人学习的一些笔记和个人愚见         *      有很多地方你可能觉得有异议，欢迎一起讨论         *作者：Stephenzhou(阿蒙)         *日期: 2012.08.5        *Mail:szstephenzhou@163.com         *另外：转载请著名出处。        **********************************************************************************/

--打开库masteruse mastergo--判断删除已存在的数据库 stuDBif exists(select * from sysdatabases where name = 'stuDB')drop database stuDB--新建数据库 stuDBcreate database stuDBon primary(name='stuDBdata',filename='d:\sql\stuDBdata.mdf',size=5mb,maxsize=unlimited,filegrowth=10%)log on(name='stuDBlog',filename='d:\sql\stuDBdata.ldf',size=1mb,maxsize=unlimited,filegrowth=1mb)go--打开use stuDBgo--判断是否已存在要创建的表if exists(select * from sysobjects where name = 'stuinfo')drop table stuInfo--新建表create table stuInfo(stuName varchar(20) not null,stuNo varchar(20) not null,stuSex varchar(20) not null,stuAge int not null,stuSeat int identity(1,1) not null,stuAddress text not null)goexec sp_help stuInfo --查看表属性--为表添加约束alter table stuInfo-- with nocheck  --在创建约束时忽略已存在的坏数据addconstraint pk_stuNo primary key (stuNo),constraint ck_stuNo check(stuNo like 's253__'),--constraint ck_stuNo check(stuNo like 's253[0-9][0-9]'),这样会全面些。constraint ck_stuSex check(stuSex in ('男','女')),constraint ck_stuAge check(stuAge between 15 and 40),constraint ck_stuSeat check(stuSeat between 1 and 30),constraint df_stuAddress default '地址不祥' for stuAddressgoexec sp_helpconstraint stuInfo  --查看约束-- nocheck -临时禁用现有约束alter table stuInfonocheckconstraint pk_stuNo --pk_stuNo改为ALL表示禁用所有约束go-- check -恢复现有约束alter table stuInfocheckconstraint pk_stuNogo--添加数据insert into stuInfo values('小强','s25301','男',18,'北京海淀')insert into stuInfo values('旺财','s25303','女',22,'河南洛阳')insert into stuInfo values('梅超风','s25302','男',31,default)insert into stuInfo values('欧阳俊雄','s25304','男',28,'新疆威武哈')--查看表中数据select * from stuinfo--打开stuDB数据库use stuDBgo--检查stuMarks表是否存在if exists(select * from sysobjects where name='stuMarks')drop table stuMarksgo--新建stuMarks表create table stuMarks(marksId int identity(101,1) not null,stuNo varchar(20) not null,writtenExam float not null,labExam float not null)go--查看表属性exec sp_help stuMarks--为stuMarks建立约束alter table stuMarksaddconstraint pk_marksId primary key(marksId),constraint fk_stuNo foreign key(stuNo)references stuInfo(stuNo)on update no action --级联更新（无操作）on delete cascade,--级联删除constraint ck_writtenExam check(writtenExam between 0 and 100),constraint ck_labExam check(labExam between 0 and 100)go--查看表约束exec sp_helpconstraint stuMarksinsert into stuMarksvalues('s25301',80,65)insert into stuMarksvalues('s25302',87,95)insert into stuMarksvalues('s25303',83,69)insert into stuMarksvalues('s25304',70,74)--查看表中是否存在约束名为 ck%select * from sysobjects where name like 'ck%'--存在检测if exists(select * from sysobjects where name = 'ck_stuAge')alter table stuInfodrop constraint ck_stuAgego--创建stuAge约束alter table stuinfoaddconstraint ck_stuAge check (stuAge between 15 and 40)go