sql_存在检测、建库、_建表、约束、外键、级联删除.doc

sql 存在检测、建库、建表、约束、外键、级联删除--打开库 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:\sql\', size=5mb, maxsize=unlimited, filegrowth=10% ) log on ( name='stuDBlog', filename='d:\sql\', 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 che ck(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 go -- check -恢复现有约束 alter table stuInfo check constraint pk_stuNo go --添加数据 insert into stuInfo values(' 小强',