sql项目

/*alter table userinfo add constraint pk_customerid
primary key(customerid)
alter table userinfo add constraint ck_PID
check(len(pid)=18 and len(pid)=15)
alter table userinfo add constraint ck_telephone
check(telephone like '%-%' and len(telephone)=11)
*/--USERINFO创建约束
/*alter table cardinfo add constraint pk_cardID
primary key(cardID)
alter table cardinfo add constraint df_cardID
default ('1010 3576 ') for cardid
alter table cardinfo add constraint ck_cardID
check (cardID like '% %')
alter table cardinfo add constraint ck_cardID
check (len(cardID)=19)
alter table cardinfo add constraint df_curtype
default ('RMB') for curtype
alter table cardinfo add constraint df_openDate
default (getdate()) for opendate
alter table cardinfo add constraint ck_openmoney
check (openmoney>1)
alter table cardinfo add constraint ck_balance
check (balance>1)
alter table cardinfo add constraint df_pass
default (888888) for pass
alter table cardinfo add constraint ck_pass
check (len(pass)>=6)
alter table cardinfo add constraint df_isreportloss
default ('否') for isreportloss
*/--CARDINFO 约束
/*
alter table transinfo add constraint df_transdate
default (getdate()) for transdate
alter table transinfo add constraint ck_transType
check (transtype='支取'and transtype='存入')
alter table transinfo add constraint ck_transmoney
check (transmoney>0)
*/--transinfo 约束

 

 

/*if exists (select *from sysdatabases where name='bankdb')
drop database bankdb
go

exec xp_cmdshell 'mkdir D:/bank'
create database bankdb
on
(
name ='bankdb_data',
filename ='D:/bank/bankdb_data.mdf',
size =10,
filegrowth=15%
)
log on
(
name ='bankdb_log',
filename ='D:/bank/bankdb_data.ldf',
size =20,
filegrowth=10%
)*/--数据库建立
/*
create table userinfo
(
customerid int identity (1,1) not null,
customername varchar(15) not null,
PID varchar(15) not null,
telephone varchar(15) not null,
address varchar(20)
)
select *from userinfo
--建立表

create table cardinfo
(
cardID int identity (1,1) not null,
curtype varchar(15) not null,
savingtype varchar(15) not null,
opendate datetime not null,
openmoney money not null,
balance money not null,
pass varchar not null,
isreportloss varchar(10) not null,
customerid varchar(10) not null
)
select *from cardinfo
-- 创建表

create table transinfo
(
transdate datetime not null,
cardid varchar(10) not null,
transtype varchar(10) not null,
transmoney money not null,
remark varchar(20)
)--建立表
select *from transinfo
*/

alter table cardinfo
add constraint pk_carid primary key(cardid),
alter table cardinfo
add constraint