数据的完整性 SQL

   第四 数据的完整性

--切换数据库
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