第四 数据的完整性
--切换数据库
use master
go
--如果已存在这个数据库,则先删除该数据库
if exists (select * from sysdatabases
where name = 'dbStudent')
drop database dbStudent
go
--创建数据库
create database dbStudent
on primary
(
name = 'dbStudent_pri',
filename = 'c:/dbStudent_pri.mdf',
size = 10,
maxsize = 30,
filegrowth = 10%
),
filegroup fgData
(
name = 'dbStudent_data',
filename = 'd:/dbStudent_data.ndf',
size = 10,
maxsize = 20,
filegrowth = 10%
)
log on
(
name = 'dbStudent_log',
filename = 'd:/dbStudent.log.ldf',
size = 10,
maxsize = 20,
filegrowth = 10%
)
go
--切换到自定义的数据库
use dbStudent
go
--建表
create table class
(
classId varchar(5) primary key, --主键约束
className varchar(12) not null --非空约束
)
go
insert into class
values('07013','优秀班级')
insert into class
values('07014','优秀班级')
select * from class
create table student
(
studentId char(9) primary key, --主键约束
studentName varchar(12) not null,
classId varchar(5) foreign key references class(classId), --外键约束
age int not null,
sex char(2) check(sex in('男','女')) --检查约束
default '男' --默认约束
)
go
insert into student
values('HB0701233','张三','07014',21,'男')
insert into student
values('HB0701234','李四','07013',21,'男')
insert into student(studentId,studentName,classId,age)
values('HB0701235','王五','07013',21)
select * from student
create table course
(
courseId int primary key,
courseName varchar(12) unique not null,
)
go
insert into course
values(1,'SQL')
insert into course
values(2,'ASP')
select * from course
create table score
(
studentId char(9) foreign key references student(studentId), --列级约束
courseId int foreign key references course(courseId),
score int check(score between 0 and 100),
primary key (studentId,courseId) --表级约束,复合主键
)
go
insert into score
values('HB0701233',1,99)
insert into score
values('HB0701233',2,100)
insert into score
values('HB0701234',1,98)
insert into score
values('HB0701234',2,97)
select * from score
--sp_help score
--go
--sp_helpconstraint score
--go