用代码创建数据库,表,约束

最新推荐文章于 2021-10-25 22:18:09 发布

qq_36074062

最新推荐文章于 2021-10-25 22:18:09 发布

阅读量530

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/qq_36074062/article/details/54140248

本文详细介绍了一个图书馆管理系统的数据库设计过程，包括创建数据库、定义表结构、设置主键、外键及各种约束条件等。该系统涵盖了图书、读者、借阅记录及罚款管理等多个方面。

--建立数据库
 use master
 if exists(select * from sysdatabases where name='Library')
 drop database Library
 create database Library 
 on primary 
 (
 name='Library_data',
 filename='D:\\project\\Library_data.mdf',
 size=5mb,
 filegrowth=15%
 )
 log on 
 (
 name='Library_log',
 filename='D:\\project\\Library_log.ldf',
 size=1mb,
 filegrowth=15%
 )
 
 use Library 
 --建立表Book及其约束
if exists (select * from sysobjects where name='Book')

drop table Book

Create table Book
(
BID varchar(20) primary key not null,
BName varchar(30) not null,
Author varchar(20),
PubComp varchar(100),
PubDate datetime ,
BCount int ,
Price money
)
--建立约束
alter table Book add Constraint CK_BID Check(BID LIKE 'ISBN%')
alter table Book add Constraint CK_PubDate Check(PubDate<GETDATE())
ALTER TABLE Book add Constraint CK_BCount Check(BCount>=1) 
ALTER TABLE Book add Constraint CK_Price Check(Price>0) 

--建立Reader及其约束
if exists (select * from sysobjects where name='Reader')

drop table Reader

Create table Reader
(
RID varchar(20) primary key not null,
RName varchar(20) not null,
LendNum int ,
RAddress varchar(40)
)
ALTER TABLE  Reader  add Constraint CK_LendNum CHECK(LendNum>=0)

--建立表Penalty及其约束
if exists (select * from sysobjects where name='Penalty')

drop table Penalty

CREATE table Penalty
(
RID VARCHAR(20)  NOT NULL,
BID VARCHAR(20)   NOT NULL,
pDate datetime  not null,
Ptype int ,
Amount money 
)
alter table penalty add constraint PK_RIDduo primary key (RID,BID,PDATE)
alter table penalty add constraint CK_Amount cHECK(Amount>0)

alter table penalty add constraint DE_pDate DEFAULT(GETDATE()) FOR pDate
alter table penalty add constraint CK_Ptype cHECK(Ptype=0 OR Ptype=1 OR Ptype=2)

--建立外键
 alter table penalty add constraint FK_RID FOREIGN KEY (RID) REFERENCES  Reader (RID)
  alter table penalty add constraint FK_BID FOREIGN KEY (BID) REFERENCES  Book (BID)
--建立表Borrow及其约束
  if exists (select * from sysobjects where name='Borrow')

drop table Borrow

CREATE table Borrow
(
RID VARCHAR(20) NOT NULL,
BID VARCHAR(20) NOT NULL,
lendDate datetime default(getdate()) not null,
WillDate datetime ,
ReturnDate datetime default('')
)

--建立主键
 alter table Borrow add constraint PK_RIDduo1 primary key(BID,RID,LendDate)
 --建立外键
 alter table Borrow add constraint FK_RIDB FOREIGN KEY (RID) REFERENCES  Reader (RID)
  alter table Borrow add constraint FK_BIDB FOREIGN KEY (BID) REFERENCES  Book (BID)
  --建立约束
  alter table Borrow add constraint CK_WillDate check (WillDate>LendDate)