数据库创建和约束条件添加

use master
go
--创建数据库shops
create database shops
go
--打开数据库shops
use shops
--在shops数据库中创建3张表
create table goods
(
goodsId int not null,
goodsName varchar(20) not null,
unitprice money not null,
category varchar(10) not null,
provider varchar(20) not null
)

create table customer
(
customerId int not null,
cname varchar(10) not null,
address varchar(50) not null,
email varchar(20) not null,
sex varchar(8) not null,
cardId varchar(20) not null
)

create table purchase
(
customerId int not null,
goodsId int not null,
nums int not null
)
--查看表结构
exec sp_help customer
--添加check约束
alter table customer
add constraint ck_sex check(sex='男' or sex='女')
--添加default约束
alter table customer
add constraint df_sex default('性别不详') for sex
--添加check约束
alter table goods
add constraint ck_unitprice check(unitprice>0)
--添加unique约束
alter table customer
add constraint uq_email unique(email)
--添加unique约束
alter table customer
add constraint uq_cardId unique(cardId)
--添加primary key约束
alter table goods
add constraint pk_goodsId primary key(goodsId)
--添加primary key约束
alter table customer
add constraint pk_customerId primary key(customerId)
--添加foreign key约束
alter table purchase
add constraint fk_customerId foreign key(customerId) references customer(customerId)
--添加foreign key约束
alter table purchase
add constraint fk_goodsId foreign key(goodsId) references goods(goodsId)