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);