Sql Server 2005数据库sql代码写外键、复合键作主键

 一、环境是sql server 2005,首先自己创建了一个ClassDB的数据库

二、运行以下的sql代码

 

use ClassDB
drop table enrol /*引用student 和classes*/
drop table student/*被enrol引用*/
drop table admin/*未被引用*/
drop table classes/*引用course和teacher同时被classes引用*/
drop table course/*被classes引用*/
drop table teacher/*被classes引用*/
/*创建课程表*/
create table course(
id int primary key identity,
name varchar(20) not null,
mark int not null,
prepare varchar(10) not null,
dep varchar(10) not null
)
/*创建教师表*/
create table teacher(
id int primary key identity,
name varchar(20) not null,
title varchar(50) not null,
password varchar(50) not null,
)
/*创建学生信息表*/
create table student( 
id int primary key identity,
name varchar(10) not null,
password varchar(50) not null,
jiguan varchar(10) not null,
department varchar(10) not null,
sex varchar(10) not null,
mark int not null,
tel varchar(50),
e_mail varchar(50)
)
/*创建管理员信息表*/
create table admin(
id int primary key identity,
ad_name varchar(10) not null,
password varchar(10) not null
)
/*创建班级信息表，其中教师id和课程id作为班级信息表的外键*/
create table classes(
id int primary key identity,
tea_id int not null,
cour_id int not null,
foreign key(tea_id) references teacher(id),
foreign key(cour_id) references course(id),
room_id varchar(50) not null,
cour_time char(10) not null
)
/*选课信息表，其中下面为复合主键*/
create table enrol(
stu_id int not null,
class_id int not null,
foreign key(stu_id) references student(id),
foreign key(class_id) references classes(id),
accept bit not null,
score varchar(50) not null,
primary key(stu_id,class_id)/*创建复合主键*/
)