Article From:https://www.cnblogs.com/qichunlin/p/9062281.html

–Now there is a store database that records customers and shopping.

Commodity table goods
(Commodity number goodsid VARCHAR2 (8)
Commodity name goodsname VARCHAR2 (20)
Univalent UnitPrice number (5,2)
Category category VARCHAR2 (8)
Supplier provider VARCHAR2 (20))

create table goods(
goodid varchar2(8),
goodname varchar2(20),
unitprice number(5,2),
category varchar2(8),
provider varchar2(20)
);

Customer table customer
customerid varchar2(8)
name varchar2(20)
address varchar2(30)
email varchar2(30)
sex varchar2(8)

create table customer(
customerid varchar2(8),
name varchar2(20),
address varchar2(30),
email varchar2(30),
sex varchar2(8)
);

Purchase table purchase
Customer number CustomerID VARCHAR2 (8)
Commodity number goodsid VARCHAR2 (20)
Purchase quantity nums number (2)

create table purchase(
customerid varchar2(8),
goodsid varchar2(20),
nums number(2)
);

Create three tables with the SQL statement
1、Define the main and external keys for each table

alter table customer add constraint pk_customerid primary key(customerid);
alter table goods add constraint pk_goods_goodid primary key(goodid);
alter table purchase add constraint fk_purchase_customerid foreign key(customerid) references customer(customerid) on delete cascade;
alter table purchase add constraint fk_purchase_goodsid foreign key(goodsid) references goods(goodid) on delete cascade;

2、The name of the customer can not be empty
alter table customer modify (name not null);

3、The unit price must be greater than 0, and the purchase quantity must be between 1 and 30.
alter table goods add constraint ck_unitPrice check(unitprice>0);
alter table purchase add constraint ck_nums check(nums between 1 and 30);
4、Email cannot be repeated
alter table customer add constraint un_email unique (email);
5、The gender of the customer must be female or male, and the default is male.
alter table customer modify (sex in(‘female’,’male’) default ‘male’);
alter table customer modify(sex varchar2(8) default ‘male’ );
alter table customer add constraint ck_sex check(sex=’female’ or sex=’male’ );

–chakan yueshu
select constraint_name,table_name from user_constraints where table_name =upper(‘goods’);
select constraint_name,table_name from user_constraints where table_name =upper(‘customer’);
select constraint_name,table_name from user_constraints where table_name =upper(‘purchase ‘);

Leave a Reply

Your email address will not be published. Required fields are marked *