例如,如果表是主键或唯一键,如何查看表的约束? [英] How to see the constraints of a table for example if it's a primary key or unique key?
本文介绍了例如,如果表是主键或唯一键,如何查看表的约束?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在Oracle
中创建了一个表Class
创建表查询是
I have created a table Class in Oracle
Create table query is
CREATE TABLE Class
(Name VARCHAR2(10) UNIQUE,
Time VARCHAR2(10),
Room VARCHAR2(10),
Fid NUMBER);
但是,我想检查名称是否为UNIQUE
.要使用DESCRIBE
命令进行检查.
But, I want to check if name is UNIQUE
or not. To have checked using DESCRIBE
command.
这没有给我限制.我不明白
It is not giving me the constraints. I can't figure it out
推荐答案
您可以加入 USER_CONSTRAINTS 和 USER_CONS_COLUMNS 这两个视图.
You could join the two views, USER_CONSTRAINTS and USER_CONS_COLUMNS.
SELECT a.*,
b.constraint_type
FROM user_cons_columns A,
user_constraints b
WHERE A.owner =b.owner
AND A.constraint_name=b.constraint_name
AND A.table_name = b.table_name
and a.table_name='CLASS'
/
例如
SQL> SELECT a.*,
2 b.constraint_type
3 FROM user_cons_columns A,
4 user_constraints b
5 WHERE A.owner =b.owner
6 AND A.constraint_name=b.constraint_name
7 AND A.table_name = b.table_name
8 and a.table_name='EMP'
9 /
OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME POSITION C
---------- -------------------- ---------- -------------------- ---------- -
SCOTT PK_EMP EMP EMPNO 1 P
SCOTT FK_DEPTNO EMP DEPTNO 1 R
SQL>
另一种方法,使用 DBMS_METADATA.GET_DDL 生成表的DDL.它将具有完整的表格信息-
Another way, use the DBMS_METADATA.GET_DDL to generate the DDL for the table. It will have complete table information -
SQL> set long 200000 pages 0 lines 131
SQL> column txt format a121 word_wrapped
SQL> select dbms_metadata.get_ddl('TABLE', 'EMP') from dual;
CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE,
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
SQL>
因此,您可以看到生成的DDL具有完整的表信息.
So, you can see the generated DDL has complete table information.
这篇关于例如,如果表是主键或唯一键,如何查看表的约束?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文