1.1_创建practicedb1数据库中的表.sql

这是我在学习《SQL Server数据库系统结构基础》时所用到的三个脚本文件

/* 1.1_创建PracticeDB1数据库中的表.sql */

USE PracticeDB1
go

/* 创建需要的数据类型 */
execute sp_addtype id,'char(6)' ,'NOT NULL'
Go

raiserror('创建 Category表....',0,1)
create table Category
(
	cCategoryId	char(3) constraint ct_pk primary key,
	cCategory	char(20) not null,
	vDescription	varchar(100)
)
go

raiserror('创建 Wrapper表....',0,1)
create Table Wrapper
(
	cWrapperId 	char(3)	constraint w_id primary key clustered,
	vDescription	varchar(20),
	mWrapperRate	money not null,
	imPhoto		image null,
	vWrapperImgPath varchar(50) null

)
go

raiserror('创建 ToyBrand表....',0,1)
create table ToyBrand
(
	cBrandId	char(3) constraint TB_pk primary key,
	cBrandName	char(20) not null,
)
go

raiserror('创建 Country表....',0,1)
create table Country
(
	cCountryId	char(3) constraint c_pk primary key,
	cCountry		char(25) not null,
)
go

raiserror('创建 ShippingMode表....',0,1)
create table ShippingMode
(
	cModeId		char(2) constraint spm_pk primary key,
	cMode 		char(25) not null,
	iMaxDelDays	int,
)
go

raiserror('创建 ShippingRate表....',0,1)
create table ShippingRate
(
	cCountryID	char(3) references Country(cCountryId) ,
	cModeId 		char(2) references  ShippingMode(cModeId),
	mRatePerPound	money not null,
	constraint SR_PRK primary key(cCountryID,cModeId)
)

raiserror('创建 Shopper表....',0,1)
create table Shopper
(
	cShopperId 	char(6)	constraint s_id primary key CLUSTERED,
	cPassword 	char(10) not null,
	
	vFirstName 	varchar(20) not null,
	vLastName 	varchar(20) not null,
	vEmailId	varchar(40) not null,
	vAddress 	varchar(40) not null,
	cCity 		char(15) not null,
	cState 		char(15) not null,
	cCountryId 	char(3) references Country(cCountryId),
	cZipCode	char(10), /*  check(cZipCode like '[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'), */
	cPhone 		char(15) not null ,
	cCreditCardNo 	char(16) not null,
	vCreditCardType 	varchar(15) not null,
	dExpiryDate 	datetime 
)
go

raiserror('创建 Toys表....',0,1)
create table Toys
(
	cToyId		char(6)  check(cToyId like('[0-9][0-9][0-9][0-9][0-9][0-9]') )
				constraint t_id primary key clustered,
				
	vToyName	varchar(20) not null,
	vToyDescription	varchar(250),
	cCategoryId	char(3) references Category(cCategoryId) ,
	mToyRate	money not null,
	cBrandId	char(3)references ToyBrand(cBrandId),
	imPhoto 	image,
	siToyQoh	smallint not null,
	siLowerAge	smallint not null,
	siUpperAge	smallint not null,
	siToyWeight	smallint,
	vToyImgPath	varchar(50) null

)
go

raiserror('创建 ShoppingCart表....',0,1)
create table ShoppingCart
(
	cCartId 	char(6) not null, 
	cToyId		char(6)REFERENCES Toys(cToyId),/* foreign key to Toys table */
	siQty		smallint not null,
	constraint SCHP_PK primary key(cCartId,cToyId)
)
go

raiserror('创建 Order表....',0,1)
create table Orders
(
	cOrderNo 	char(6) constraint CO_PK Primary key, 
	dOrderDate 	datetime not null,
	cCartId		char(6) not null, 
	cShopperId	char(6) not null references Shopper(cShopperId),
	cShippingModeId	char(2) null references ShippingMode(cModeId),
	mShippingCharges money  null,
	mGiftWrapCharges	money null,
	cOrderProcessed	char null ,
	mTotalCost	money  null,
	dExpDelDate	DateTime null
)
go

raiserror('创建 OrderDetails表....',0,1)
create table OrderDetail
(
	cOrderNo 	char(6) references Orders(cOrderNo),
	cToyId 		char(6)  references toys(cToyId),
	siQty 		smallint  not null,
	cGiftWrap	char null, 
	cWrapperId	char(3) references Wrapper(cWrapperId) null,
	vMessage	varchar(256) null,
	mToyCost        money null,
		constraint z_key primary key(cOrderNo,cToyId)
)
go

raiserror('创建  Shippment表....',0,1)
create table Shipment
(
	cOrderNo		char(6)  REFERENCES Orders(cOrderNo) constraint SHP_PK primary key,  
	dShipmentDate	datetime null, /* cannot be before order date */
	cDeliveryStatus	char null, 
	dActualDeliveryDate datetime null
)
go

raiserror('创建 Recipient表....',0,1)
create table Recipient
(
	cOrderNo		char(6) REFERENCES Orders(cOrderNo) constraint RCP_PK primary key, /* foreign key to order table */
	vFirstName 		varchar(20) not null,
	vLastName 		varchar(20) not null,
	vAddress		varchar(20) not null,
	cCity 		        char(15) not null,
	cState			char(15) not null,
	cCountryId	char(3) references Country(cCountryId),
	cZipCode		char(10) check(cZipCode like '[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'),
	cPhone		char(15)
)