《建表语句.docx》由会员分享，可在线阅读，更多相关《建表语句.docx（11页珍藏版）》请在三一办公上搜索。
1、建表语句1, 商品信息表 product_info - Create table create table product_info ( product_id number(10) not null, product_no number(10) not null, product_name varchar2(40) not null, unit varchar2(10) not null, price number(10) not null, cost number(10) not null, provider varchar2(50) not null, can_return varchar
2、2(10) not null, warn_account number(10) not null, max_account number(10) not null, can_change varchar2(10) not null ); - Create/Recreate primary, unique and foreign key constraints alter table product_info add constraint product_id_pk primary key (PRODUCT_ID); alter table product_info add constraint
3、 product_no_uk unique (PRODUCT_NO); alter table product_info add constraint product_name_uk unique (PRODUCT_NAME); = 2, 库房信息表 warehouse_info - Create table create table warehouse_info ( warehouse_id number(10) not null, warehouse_name varchar2(20) not null, warehouse_add varchar2(50) not null, wareh
4、ouse_admin varchar2(10) not null, warehouse_grade varchar2(10) not null ); - Create/Recreate primary, unique and foreign key constraints alter table warehouse_info add constraint warehouse_id_pk primary key (WAREHOUSE_ID); alter table warehouse_info add constraint warehouse_name_uk unique (WAREHOUSE
5、_NAME); = 3, 购货单表 inport_info - Create table create table inport_info ( inport_id number(10) not null, inport_no number(10) not null, product_id number(10) not null, short_account number(10) not null, inport_account number(10) not null, decision varchar2(20) not null, inport_time date not null ); -
6、Create/Recreate primary, unique and foreign key constraints alter table inport_info add constraint inport_id_pk primary key (INPORT_ID); alter table inport_info add constraint inport_no_uk unique (INPORT_NO); alter table inport_info add constraint product_id_fk foreign key (PRODUCT_ID) references pr
7、oduct_info (PRODUCT_ID); = 4, 出入库单表inout_info - Create table create table inout_info ( inout_id number(10) not null, inout_no number(10) not null, warehouse_id number(10) not null, inout_type varchar2(10) not null, product_id number(10) not null, inout_account number(10) not null, inout_date date no
8、t null ); - Create/Recreate primary, unique and foreign key constraints alter table inout_info add constraint inout_id_pk primary key (INOUT_ID); alter table inout_info add constraint inout_no_uk unique (INOUT_NO); alter table inout_info add constraint product_id foreign key (PRODUCT_ID) references
9、product_info (PRODUCT_ID); = 5, 客户信息表customer_info - Create table create table customer_info ( customer_id number(10) not null, customer_no number(10) not null, customer_name varchar2(10) not null, identity_no varchar2(50) not null, job_add varchar2(50), tel number(20), cellphone number(20) not null
10、, address varchar2(100) not null, mail varchar2(50), post number(10) ); - Create/Recreate primary, unique and foreign key constraints alter table customer_info add constraint customer_id_pk primary key (CUSTOMER_ID); alter table customer_info add constraint customer_no_uk unique (CUSTOMER_NO); alter
11、 table customer_info add constraint identity_no_uk unique (IDENTITY_NO); = 6, 订单表 order_info - Create table create table order_info ( order_id number(10) not null, order_no number(10) not null, order_type varchar2(20) not null, order_status varchar2(20) not null, product_id number(10) not null, prod
12、uct_account number(20) not null, pay_money number(20) not null, customer_id number(10) not null, start_date date not null, deadline date not null, return_reason varchar2(100) ); - Create/Recreate primary, unique and foreign key constraints alter table order_info add constraint order_id_pk primary ke
13、y (ORDER_ID); alter table order_info add constraint order_no_uk unique (ORDER_NO); alter table order_info add constraint product_id_fk foreign key (PRODUCT_ID) references product_info (PRODUCT_ID); = 7, 用户管理表user_info - Create table create table user_info ( user_id number(10) not null, user_name var
14、char2(20) not null, password varchar2(20) not null, mail varchar2(50), right varchar2(20) not null ) ; - Create/Recreate primary, unique and foreign key constraints alter table user_info add constraint user_id_pk primary key (USER_ID); alter table user_info add constraint user_name_uk unique (USER_N
15、AME); = 8, 中心库房库存信息表 ctrm_info - Create table create table ctrm_info ( ctrm_id number(10) not null, product_id number(10) not null, prodect_exit number(10) not null, product_status varchar2(10) not null ); - Create/Recreate primary, unique and foreign key constraints alter table ctrm_info add constr
16、aint ctrm_id_pk primary key (CTRM_ID); alter table ctrm_info add constraint pid_uk unique (PID); alter table ctrm_info add constraint pid_fk foreign key (PID) references product_info (PID); = 9, 任务单表 mission_info - Create table create table mission_info ( mission_id number(10) not null, mission_no n
17、umber(10) not null, order_id number(10) not null, mission_date date not null, mission_status varchar2(20) not null, substation_name varchar2(20) not null, warehouseid number(10) not null, takeout_name varchar2(10) not null, takeout_date date not null, mission_type varchar2(20) not null ); - Create/R
18、ecreate primary, unique and foreign key constraints alter table mission_info add constraint mission_id_pk primary key (MISSION_ID); alter table mission_info add constraint mission_no_uk unique (MISSION_NO); alter table mission_info add constraint order_id_uk unique (ORDER_ID); alter table mission_in
19、fo add constraint order_id_fk foreign key (ORDER_ID) references order_info (ORDER_ID); alter table mission_info add constraint whid_fk foreign key (WHID) references warehouse_info (WAREHOUSE_ID); alter table mission_info = 10, 调拨单表 transfer_info - Create table create table transfer_info ( transfer_i
20、d number(10) not null, transfer_no number(10) not null, order_id number(10) not null, transfer_date date not null, transfer_status varchar2(20) not null, transfer_type varchar2(20) not null, warehouse_id number(10) not null ); - Create/Recreate primary, unique and foreign key constraints alter table
21、 transfer_info add constraint transfer_id_pk primary key (TRANSFER_ID); alter table transfer_info add constraint transfer_no_uk unique (TRANSFER_NO); alter table transfer_info add constraint order_id_uk unique (ORDER_ID); alter table transfer_info add constraint order_id_fk foreign key (ORDER_ID) re
22、ferences order_info (ORDER_ID); alter table transfer_info add constraint customer_id_fk foreign key (CUSTOMER_ID) references customer_info (CUSTOMER_ID); alter table transfer_info add constraint whid_fk foreign key (WAREHOUSE_ID) references warehouse_info (WAREHOUSE_ID); = 11, 签收单表 assign_info - Cre
23、ate table create table assign_info ( assign_id number(10) not null, assign_no number(10) not null, mission_id number(10) not null, order_id number(10) not null, assign_date date not null, delivery_man varchar2(20) not null ); - Create/Recreate primary, unique and foreign key constraints alter table
24、assign_info add constraint assign_id_pk primary key (ASSIGN_ID); alter table assign_info add constraint assign_no_uk unique (ASSIGN_NO); alter table assign_info add constraint mission_id_fk foreign key (MISSION_ID) references mission_info (MISSION_ID); alter table assign_info = 12, 验货单表 checklist_in
25、fo - Create table create table checklist_info ( checklist_id number(10) not null, checklist_no number(10) not null, checklist_date date not null, warehouse_id number(10) not null, product_id number(10) not null, account number(10) not null, actural_account number(10) not null, ps varchar2(50) ) ; -
26、Create/Recreate primary, unique and foreign key constraints alter table checklist_info add constraint checklist_id_pk primary key (CHECKLIST_ID); alter table checklist_info add constraint checklist_no_uk unique (CHECKLIST_NO); alter table checklist_info add constraint warehouse_id_fk foreign key (WA
27、REHOUSE_ID) references warehouse_info (WAREHOUSE_ID); alter table checklist_info add constraint product_id foreign key (PRODUCT_ID) references product_info (PRODUCT_ID); = 13, 库房退货单表 back_info - Create table create table BACK_INFO ( BACK_ID NUMBER(10) not null, BACK_NO NUMBER(10) not null, WAREHOUSE
28、_ID NUMBER(10) not null, PRODUCT_ID NUMBER(10) not null, BACK_ACCOUNT NUMBER(10) not null, BACK_DATE DATE not null, BACK_STATUS VARCHAR2(20) not null, PS VARCHAR2(50) ); - Create/Recreate primary, unique and foreign key constraints alter table BACK_INFO add constraint BACK_ID_PK primary key (BACK_ID
29、) alter table BACK_INFO add constraint BACK_NO_UK unique (BACK_NO) alter table BACK_INFO add constraint PRODUCT_ID_FK foreign key (PRODUCT_ID) references PRODUCT_INFO (PRODUCT_ID); = 14, 缺货单 short_info - Create table create table SHORT_INFO ( SHORT_ID NUMBER(10) not null, SHORT_NO NUMBER(10) not nul
30、l, PRODUCT_ID NUMBER(10) not null, SHORT_ACCOUNT NUMBER(10) not null, SHORT_DATE DATE not null, CTRM_ID NUMBER(10) not null ) - Create/Recreate primary, unique and foreign key constraints alter table SHORT_INFO add constraint SHORT_ID_PK primary key (SHORT_ID) alter table SHORT_INFO add constraint SHORT_ID_UK unique (SHORT_NO);
