建表.sql

QL SERVER 应用开发 银行ATM(取款机）系统 数据库设计 我在学校的 SQL项目,模拟 银行ATM(取款机）系统

USE bankDB
GO
--建立用户信息表：
if exists(select * from SysObjects where name='userInfo')
drop table userInfo
GO
create table userInfo
(
customerID int not null identity(1,1)
constraint PK_USERINFO_CUSTOMERID
primary key,

customerName char(8) not null,
PID varchar(18) not null
constraint UN_USERINFO_PID
unique
constraint CH_USERINFO_PID
check(len(PID)=15 OR LEN(PID)=18),

telephone varchar(13) not null
constraint CH_USERINFO_TELEPHONE
check(telephone like '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' or telephone 
like'[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'or telephone 
like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
address varchar(20)
)
GO

--建立银行信息表：
if exists(select * from sysobjects where name='cardInfo')
drop table cardInfo
GO
create table cardInfo
(
cardID char(19) not null
constraint PK_cardInfo_cardID
Primary key
constraint CH_cardInfo_cardID
check(cardID like '1010 3576 [0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9]'),
curType varchar(10) not null
constraint DF_cardInfo_curType
default 'RMB',
savingType varchar(10)
constraint CH_cardInfo_savingType
check (savingType in('活期','定活两便','定期')),
openDate datetime not null
constraint DF_cardInfo_openDate
default (getdate()),
openMoney money not null
constraint CH_cardInfo_openMoney
check(openMoney>=1),
balance money not null
constraint CH_cardInfo_balance
check(balance>=1),
pass char(6) not null
constraint CH_cardInfo_pass
check(len(pass)=6)
constraint DF_cardInfo_pass
default ('888888'),
IsReportLoss bit not null
constraint DF_cardInfo_IsReportLoss
default 0,
customerID int not null
constraint FK_cardInfo_customerID
foreign key references userInfo(customerID)
)
GO

--交易信息表
if exists (select * from sysobjects where name='transInfo')
drop table transInfo
GO
create table transInfo
(
transDate datetime not null
constraint DF_transInfo_transDate
default getdate(),
cardID char(19) not null
constraint FK_transInfo_cardID
foreign key references cardInfo(cardID),
transType char(4) not null
constraint CH_transInfo_trasnType
check(transType in('存入','支取')),
transMoney money not null
constraint CH_transInfo_transMoney
check(transMoney>0),
remark text
)
GO