oracle数据库建表及增加主键、索引、约束、授权等操作

oracle数据库建表及增加主键、索引、约束、授权等操作

-- Create table
create table MCCDATA.MCC_PUSH_CLUE
(
  external_id   VARCHAR2(10) not null,
  call_event_id VARCHAR2(20) not null,
  client_phone  VARCHAR2(20) not null,
  client_name   VARCHAR2(50) not null,
  client_sex    VARCHAR2(1),
  branch_code   VARCHAR2(10) not null,
  valid_flag    VARCHAR2(1) not null,
  updated_user  VARCHAR2(50) not null,
  updated_date  DATE not null,
  created_user  VARCHAR2(50) not null,
  created_date  DATE not null,
  task_status   VARCHAR2(1),
  push_time     DATE,
  deal_user     VARCHAR2(50),
  push_status   VARCHAR2(1)
)
tablespace MCCTBS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 1M
    next 1M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
-- Add comments to the table 
comment on table MCCDATA.MCC_PUSH_CLUE
  is '线索系统推送记录';
-- Add comments to the columns 
comment on column MCCDATA.MCC_PUSH_CLUE.external_id
  is '外部渠道线索id';
comment on column MCCDATA.MCC_PUSH_CLUE.call_event_id
  is '呼入事件id';
comment on column MCCDATA.MCC_PUSH_CLUE.client_phone
  is '客户电话';
comment on column MCCDATA.MCC_PUSH_CLUE.client_name
  is '客户姓名';
comment on column MCCDATA.MCC_PUSH_CLUE.client_sex
  is '客户性别:1男, 2女';
comment on column MCCDATA.MCC_PUSH_CLUE.branch_code
  is '机构代码';
comment on column MCCDATA.MCC_PUSH_CLUE.valid_flag
  is '有效标志';
comment on column MCCDATA.MCC_PUSH_CLUE.updated_user
  is '修改人';
comment on column MCCDATA.MCC_PUSH_CLUE.updated_date
  is '修改日期';
comment on column MCCDATA.MCC_PUSH_CLUE.created_user
  is '录入人';
comment on column MCCDATA.MCC_PUSH_CLUE.created_date
  is '录入日期';
comment on column MCCDATA.MCC_PUSH_CLUE.task_status
  is '任务状态0-待分配,1-处理中,2-复核通过,3-撤销';
comment on column MCCDATA.MCC_PUSH_CLUE.push_time
  is '推送时间';
comment on column MCCDATA.MCC_PUSH_CLUE.deal_user
  is '处理人';
comment on column MCCDATA.MCC_PUSH_CLUE.push_status
  is '推送状态0-推送失败,1-推送成功';
-- Create/Recreate indexes 
create index MCCDATA.IX_MCC_PUSH_CLUE_PHONE on MCCDATA.MCC_PUSH_CLUE (CLIENT_PHONE)
  tablespace MCCTBS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 1M
    next 1M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table MCCDATA.MCC_PUSH_CLUE
  add constraint PK_MCC_PUSH_CLUE primary key (EXTERNAL_ID)
  using index 
  tablespace MCCTBS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 1M
    next 1M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
alter table MCCDATA.MCC_PUSH_CLUE
  add constraint FK_MCC_PUSH_CLUE_CEID foreign key (CALL_EVENT_ID)
  references MCCDATA.MCC_CALL_EVENT (CALL_EVENT_ID);
-- Create/Recreate check constraints 
alter table MCCDATA.MCC_PUSH_CLUE
  add constraint CK_MCC_PUSH_CLUE_PT
  check (PUSH_STATUS in ('0','1'));
alter table MCCDATA.MCC_PUSH_CLUE
  add constraint CK_MCC_PUSH_CLUE_TS
  check (TASK_STATUS in ('0','1','2','3'));
alter table MCCDATA.MCC_PUSH_CLUE
  add constraint CK_MCC_PUSH_CLUE_V
  check (valid_flag in('Y','N'));
-- Grant/Revoke object privileges 
grant select, insert, update on MCCDATA.MCC_PUSH_CLUE to MCCOPR;