oracle_建表并指定字段和描述

1. 建表

CREATE TABLE  tablename
(

  ID1            VARCHAR2(50) NOT NULL,
  ID2            INTEGER   NOT NULL,
  SPARE_FIELD_1           NUMBER(28,10),
  SPARE_FIELD_2            NUMBER(28,10),
  SPARE_FIELD_3            NUMBER(28,10),
  UPDATE_TIME              TIMESTAMP(6)
)
TABLESPACE TS_TABLE
  PCTFREE 10
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    NEXT 1M
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
  );
 
2. 表描述

COMMENT ON TABLE  tablename
  IS '表tablename';
--字段描述 
COMMENT ON COLUMN  tablename.ID1
  IS 'ID1';
COMMENT ON COLUMN  tablename.ID2
  IS 'ID2';
COMMENT ON COLUMN  tablename.SPARE_FIELD_1
  IS '备用字段1';
COMMENT ON COLUMN  tablename.SPARE_FIELD_2
  IS '备用字段2';
COMMENT ON COLUMN  tablename.SPARE_FIELD_3
  IS '备用字段3';
COMMENT ON COLUMN  tablename.UPDATE_TIME
  IS '数据更新时间';

3. 主键

ALTER TABLE  tablename
  ADD CONSTRAINT PK_tablename PRIMARY KEY (ID1,ID2)
   USING INDEX
  TABLESPACE TS_PK_TABLE
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    NEXT 1M
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
  );

4. 外键

ALTER TABLE  tablename
  ADD FOREIGN KEY (ID1)
  REFERENCES  ID1TABLE (ID1);
ALTER TABLE  tablename
  ADD FOREIGN KEY (ID2)
  REFERENCES  ID2TABLE (ID2);