34,594
社区成员
发帖
与我相关
我的任务
分享
create table study
(
SNo int
constraint FK_SNo2 foreign key(SNo) references stu(SNo),
CNo int,
Tno int,
Grade int
constraint CK_Grade check(grade between 0 and 100),
primary key(Sno,Cno),
constraint FK_CNo foreign key(CNo,Tno) references course(Cno,Tno),
)
go
if exists (select * from sysdatabases where name='school')
drop database school
go
create database school
on
(
name='school_data',
filename='e:\data\school_data.mdf',
size=3mb,
filegrowth=15%
)
log on
(
name='school_log',
filename='e:\data\school_log.ldf',
size=1mb,
filegrowth=15%
)
go
use school
go
if exists(select * from sysobjects where name='stu')
drop table stu
go
create table stu
(
SNo int
constraint PK_Sno primary key,
SName nvarchar(10) not null,
Sex nvarchar(5) not null
constraint CK_Sex check(Sex in('男','女')),
Age int
constraint CK_Age check(Age between 0 and 100),
Native nvarchar(10)
)
go
if exists(select * from sysobjects where name='teacher')
drop table teacher
go
create table teacher
(
TNO int
constraint PK_TNO primary key,
TName nvarchar(10) not null,
Post varchar(5),
Sex varchar(5)
constraint CK_Sex1 check(Sex in('男','女')),
Age int
constraint CK_Age1 check(Age between 0 and 100)
)
go
if exists(select * from sysobjects where name='course')
drop table course
go
create table course
(
Cno int,
CName nvarchar(10),
Tno int
constraint FK_Tno foreign key(Tno) references teacher(Tno),
Period float,
primary key(Cno,Tno)
)
go
if exists(select * from sysobjects where name='study')
drop table study
go
create table study
(
SNo int
constraint FK_SNo2 foreign key(SNo) references stu(SNo),
CNo int
constraint FK_CNo foreign key(CNo) references course(Tno),
Grade int
constraint CK_Grade check(grade between 0 and 100),
primary key(Sno,Cno)
)
go