SQL数据库创建表的问题？

use master
go

if exists (select * from sysdatabases where name = 'bookinfo')
drop database bookinfo

create database bookinfo
------ 如果不写 数据库就创建在 默认路径 ------
--on
--(
-- name = 'bookinfo_data',
-- filename = 'E:\bookinfo_data.mdf',
-- size = 10mb,
-- filegrowth = 10%
--)
--log on
--(
-- name = 'bookinfo_log',
-- filename = 'e:\bookinfo_log.ldf',
-- size = 1mb,
-- filegrowth = 1
--)
--go

use bookinfo
go
------ 创建表 student ------
create table student
(
stuid char(10) not null,
stuname varchar(10) not null,
major varchar(50) not null,
)
go

alter table student add constraint pk_student primary key(stuid) ---主键约束

----- 创建表 book --------
create table book
(
bid char(10) not null,
title varchar(50) not null,
author varchar(50)
)
go

alter table book add constraint pk_book primary key(bid) ---主键约束

--- 创建表 borrow -------
create table borrow
(
borrowid char(10) not null,
stuid char(10) not null,
bid char(10) not null,
t_time datetime not null,
b_time datetime not null
)
go

alter table borrow add constraint pk_borrow primary key(borrowid) ---主键约束
alter table borrow add constraint fk_stuid foreign key(stuid)
references student(stuid) ----外键约束
alter table borrow add constraint fk_bid foreign key(bid)
references book(bid) ----外键约束

------ 向 student表中 插入数据 -------
insert into student(stuid,stuname,major)
values('1001','林林','计算机')
insert into student(stuid,stuname,major)
values('1002','二番队','计算机')
insert into student(stuid,stuname,major)
values('1003','豆腐干','数学')
insert into student(stuid,stuname,major)
values('1004','任天狗','工商管理')

-------- 向 book表中 插入数据 ----------
insert into book (bid,title,author)
values('001','飞鸽网','威尔')
insert into book (bid,title,author)
values('002','撒旦法','岁的菲尔')
insert into book (bid,title,author)
values('003','羊肉汤','嘎达')
insert into book (bid,title,author)
values('0034','特务身份','官方公会')
insert into book (bid,title,author)
values('005','俄国人威尔','安意如')

------ 向 borrow表中 插入数据 ---------

insert into borrow(borrowid,stuid,bid,t_time,b_time)
values('01','1001','001','2007-12-11','2007-12-15')
insert into borrow(borrowid,stuid,bid,t_time,b_time)
values('02','1002','003','2007-11-11','2007-11-18')
insert into borrow(borrowid,stuid,bid,t_time,b_time)
values('03','1002','002','2007-6-11',null)
insert into borrow(borrowid,stuid,bid,t_time,b_time)
values('04','1004','005','2007-5-11','2007-5-16')
insert into borrow(borrowid,stuid,bid,t_time,b_time)
values('05','1003','0034','2007-4-11',null)
insert into borrow(borrowid,stuid,bid,t_time,b_time)
values('06','1001','002','2007-12-11','2007-12-13')

select * from student
select * from book
select * from borrow

------- 查询视图 计算机专业学员的借书记录 -----------
create view view_book
as
SELECT dbo.student.stuid as 学生编号, dbo.student.stuname as 学生姓名, dbo.book.bid as 图书编号, dbo.book.title as 图书名, dbo.borrow.t_time as 借书日期
FROM dbo.book INNER JOIN
dbo.borrow ON dbo.book.bid = dbo.borrow.bid INNER JOIN
dbo.student ON dbo.borrow.stuid = dbo.student.stuid where major = '计算机'
go

select * from view_book ---- 查看视图

------ 查询 查询借书的学员信息 ---------------
create view view_student
as
select stuid as 学生编号, stuname as 学生姓名, major as 专业 from student
go

select * from view_student ---- 查看视图

------ 查询 作者 安意如 图书 的借阅情况 --------------
create view view_borrow
as
SELECT dbo.student.stuname as 学生姓名, dbo.book.title as 书名, dbo.borrow.t_time as 借书日期, dbo.borrow.b_time as 归还日期
FROM dbo.book INNER JOIN
dbo.borrow ON dbo.book.bid = dbo.borrow.bid INNER JOIN
dbo.student ON dbo.borrow.stuid = dbo.student.stuid where author = '安意如'
go

select * from view_borrow ---- 查看视图

------------- 查询
SELECT dbo.student.stuname as 学员姓名,(select count(*) from borrow where dbo.borrow.stuid = dbo.student.stuid and b_time is null ) as 借书数量
FROM dbo.borrow INNER JOIN
dbo.student ON dbo.borrow.stuid = dbo.student.stuid
go

一个表a的某列的值是唯一的(通过设置主键来达到),然后,另一个表b某的列值只能取表a里面那个主键列的值,且是一对一或多对一的关系.

示例:
表a
(num_id int primary key,
......
)

表b
(id int,
num_id int references 表a(num_id),
......
)

这样就表示表b的列num_id是到表a的列num_id的外键(即表b的列num_id的取值只能取自表a的列num_id的值).

一个表中的主码 是另一个表中的外键