oracle中什么是外键连接,如何知道外键在Oracle SQL Developer中引用哪些列？

您可以加入 user_cons_columns 和 user_constraints 视图以获取主键和外键引用的元数据信息 .

例如，

SQL> COLUMN table_name format a10

SQL> COLUMN column_name format a11

SQL> COLUMN constraint_name format a20

SQL> COLUMN owner format a10

SQL> COLUMN ref_table_name format a15

SQL> COLUMN ref_pk format a10

SQL> SELECT a.table_name,

2 a.column_name,

3 a.constraint_name,

4 b.owner,

5 c_pk.table_name ref_table_name,

6 c_pk.constraint_name ref_pk

7 FROM user_cons_columns a

8 JOIN user_constraints b

9 ON a.owner = b.owner

10 AND a.constraint_name = b.constraint_name

11 JOIN user_constraints c_pk

12 ON b.r_owner = c_pk.owner

13 AND b.r_constraint_name = c_pk.constraint_name

14 WHERE b.constraint_type = 'R';

TABLE_NAME COLUMN_NAME CONSTRAINT_NAME OWNER REF_TABLE_NAME REF_PK

---------- ----------- -------------------- ---------- --------------- ----------

EMP DEPTNO FK_DEPTNO SCOTT DEPT PK_DEPT

SQL>

您还可以使用 DBMS_METADATA.GET_DDL 为表生成DDL . 它将具有完整的表格信息 .

例如，

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"