oracle_建表并指定字段和描述

来源：互联网发布：sqlserver订阅发布编辑：程序博客网时间：2024/06/06 03:51

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

--表描述
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 '数据更新时间';

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

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

0 0