Oracle创建表、主键,创建和获取表描述字段描述
?创建表,并给表和字段加上描述
CREATE TABLE MANTIS_CODE(
ID VARCHAR2(32) NOT NULL,
NAME VARCHAR2(255) NOT NULL
);
CREATE TABLE MANTIS_CODE_ITEM(
CODE_ID VARCHAR2(32) NOT NULL,
VALUE VARCHAR2(32) NOT NULL,
NAME VARCHAR2(255)
)
;
?.创建主键
alter TABLE MANTIS_CODE add constraint PK_MANTIS_CODE primary key (ID)
?联合主键
alter TABLE MANTIS_CODE add constraint PK_MANTIS_CODE primary key (ID,NAME)
?创建表描述
COMMENT ON TABLE mantis_code is '代码表'
?创建字段描述
COMMENT ON COLUMN mantis_code.ID is '代码标识'
?创建外键
alter table MANTIS_CODE_ITEM add constraint FK_MANTIS_CODE_ITEM foreign key(CODE_ID) references MANTIS_CODE(ID)
?取得表的描述
select * from user_tab_comments where comments is not null
?取得字段
select
t.TABLE_NAME,t.COLUMN_NAME,t.DATA_TYPE,t.DATA_LENGTH,t.DATA_PRECISIO N,t.DATA_SCALE,
t.NULLABLE,t.DATA_DEFAULT from user_tab_columns t where
t.TABLE_NAME='USER_INFO'
?取得字段的描述
select * from user_col_comments where comments is not null;