-- Create table
create table DISTRICT_CODE
(
DISTRICT_ID INTEGER PRIMARY KEY NOT NULL,
NAME VARCHAR2(30 CHAR) not null,
PARENT_DISTRICT_ID INTEGER default 0 not null,
PROVINCE_ID INTEGER default 0 not null,
PROVINCE_NAME VARCHAR2(30 CHAR),
CITY_ID INTEGER default 0 not null,
CITY_NAME VARCHAR2(30 CHAR),
COUNTY_ID INTEGER default 0 not null,
COUNTY_NAME VARCHAR2(30 CHAR),
DISTRICT_CODE INTEGER default 0 not null,
ABBREVIATION VARCHAR2(10 CHAR),
SHORT_NAME VARCHAR2(30 CHAR),
ALIAS VARCHAR2(30 CHAR),
TEL_AREA_CODE VARCHAR2(10),
LONGITUDE NUMBER(9,6) default 0 not null,
LATITUDE NUMBER(9,6) default 0 not null,
AREA NUMBER(9,2) default 0 not null,
IS_CAPITAL NUMBER(1) default 0,
GOV_LONGITUDE NUMBER(9,6) default 0 not null,
GOV_LATITUDE NUMBER(9,6) default 0 not null,
START_LONGITUDE NUMBER(9,6) default 0 not null,
START_LATITUDE NUMBER(9,6) default 0 not null,
END_LONGITUDE NUMBER(9,6) default 0 not null,
END_LATITUDE NUMBER(9,6) default 0 not null,
FLAG_ID NUMBER(1) default 0 not null,
CLASS_ID NUMBER(1) default 0 not null,
UPDATE_TIME DATE default sysdate not null,
LATEST_DISTRICT_CODE INTEGER,
SORT INTEGER default 0 not null,
AREA_ID INTEGER default 0 not null,
PINYIN_INITIALS VARCHAR2(20),
PINYIN VARCHAR2(100)
)
-- Add comments to the columns
comment on column DISTRICT_CODE.DISTRICT_ID
is '行政区编号';
comment on column DISTRICT_CODE.NAME
is '名称';
comment on column DISTRICT_CODE.PARENT_DISTRICT_ID
is '父编号';
comment on column DISTRICT_CODE.ABBREVIATION
is '简称';
comment on column DISTRICT_CODE.SHORT_NAME
is '简称';
comment on column DISTRICT_CODE.IS_CAPITAL
is '是否省会,0-否;1-是';
comment on column DISTRICT_CODE.FLAG_ID
is '1- 省;2-市;3-县;4-直辖市;5-特别行政区';
comment on column DISTRICT_CODE.CLASS_ID
is '1-省2-市、特别行政区3-县';
comment on column DISTRICT_CODE.SORT
is '搜索排序';
comment on column DISTRICT_CODE.AREA_ID
is '地区编号(e.g.华南地区、华北地区)';
comment on column DISTRICT_CODE.PINYIN_INITIALS
is '地名简称首字母';
comment on column DISTRICT_CODE.PINYIN
is '地名全拼';
-- Create index
CREATE SEQUENCE SEQ_DISTRICT_CODE;
-- Create/Recreate index
CREATE INDEX GOV_LAT_LNG
ON DISTRICT_CODE (GOV_LONGITUDE, GOV_LATITUDE)
COMPRESS 1;
CREATE INDEX IDX_DISTRICT_CODE_CLASS_ID
ON DISTRICT_CODE (CLASS_ID);
CREATE INDEX IDX_DISTRICT_CODE_FLAG_ID
ON DISTRICT_CODE (FLAG_ID);
CREATE INDEX IDX_DISTRICT_CODE_PARENT_ID
ON DISTRICT_CODE (PARENT_DISTRICT_ID);
CREATE UNIQUE INDEX IDX_LATEST_DC
ON DISTRICT_CODE (LATEST_DISTRICT_CODE);