SQL Server— 存在检测、建库、 建表、约束、外键、级联删除

/********************************************************************************        
 *主题： SQL Server—  存在检测、建库、 建表、约束、外键、级联删除       
 *说明：本文是个人学习的一些笔记和个人愚见        
 *      有很多地方你可能觉得有异议，欢迎一起讨论        

 *作者：Stephenzhou(阿蒙)        
 *日期: 2012.08.5       
 *Mail:szstephenzhou@163.com        
 *另外：转载请著名出处。        
**********************************************************************************/  

--打开库master
use master
go
--判断删除已存在的数据库 stuDB
if exists(select * from sysdatabases where name = 'stuDB')
drop database stuDB
--新建数据库 stuDB
create database stuDB
on primary(
name='stuDBdata',
filename='d:sqlstuDBdata.mdf',
size=5mb,
maxsize=unlimited,
filegrowth=10%
)
log on
(
name='stuDBlog',
filename='d:sqlstuDBdata.ldf',
size=1mb,
maxsize=unlimited,
filegrowth=1mb
)
go
--打开
use stuDB
go
--判断是否已存在要创建的表
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
)
go
exec sp_help stuInfo --查看表属性
--为表添加约束
alter table stuInfo
-- with nocheck  --在创建约束时忽略已存在的坏数据
add
constraint 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 stuAddress
go

exec sp_helpconstraint stuInfo  --查看约束
-- nocheck -临时禁用现有约束
alter table stuInfo
nocheck
constraint pk_stuNo --pk_stuNo改为ALL表示禁用所有约束
go
-- check -恢复现有约束
alter table stuInfo
check
constraint pk_stuNo
go
--添加数据
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 stuDB
go
--检查stuMarks表是否存在
if exists(select * from sysobjects where name='stuMarks')
drop table stuMarks
go
--新建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 stuMarks
add
constraint 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 stuMarks
insert into stuMarks
values('s25301',80,65)
insert into stuMarks
values('s25302',87,95)
insert into stuMarks
values('s25303',83,69)
insert into stuMarks
values('s25304',70,74)


--查看表中是否存在约束名为 ck%
select * from sysobjects where name like 'ck%'
--存在检测
if exists(select * from sysobjects where name = 'ck_stuAge')
alter table stuInfo
drop constraint ck_stuAge
go
--创建stuAge约束
alter table stuinfo
add
constraint ck_stuAge check (stuAge between 15 and 40)
go