SCOTT@ORA12C> create table emp_1 as select * from emp; --附表 Table created. SCOTT@ORA12C> create table dept_1 as select * from dept; --主表 Table created. SCOTT@ORA12C> alter table dept_1 add constraint PK_DEPT_1 primary key(deptno); --主表添加主键约束 Table altered. SCOTT@ORA12C> alter table emp_1 add constraint FK_EMP_1 foreign key(deptno) references dept_1(deptno); Table altered. --添加外键约束
查询表的索引键值情况：
SELECT a.owner, --主键拥有者
a.table_name, --主键表
b.column_name, --主键列
c.owner, --外键拥有者
c.table_name, --外键表
d.column_name --外键列
FROM user_constraints a
LEFT JOIN user_cons_columns b
ON a.constraint_name = b.constraint_name
LEFT JOIN user_constraints c
ON c.r_constraint_name = a.constraint_name
LEFT JOIN user_cons_columns d
ON c.constraint_name = d.constraint_name
WHERE a.constraint_type = 'P'
AND a.table_name IN ('DEPT_1', 'EMP_1') --需要查看主外键关系的表
16 ORDER BY a.table_name;
OWNER TABLE_NAME COLUMN_NAM OWNER TABLE_NAME COLUMN_NAM
---------- --------------- ---------- ---------- --------------- ----------
SCOTT DEPT_1 DEPTNO SCOTT EMP_1 DEPTNO
1 row selected.通过以上发现dept_1和emp_1 列deptno主外键关系。
如果删除dept_1的数据，那么必须对附表emp_1进行全表扫描：
以下语句可以查询：那些外键没有索引:
SELECT TABLE_NAME, CONSTRAINT_NAME, CNAME1 || NVL2(CNAME2, ',' || CNAME2, NULL) || NVL2(CNAME3, ',' || CNAME3, NULL) || NVL2(CNAME4, ',' || CNAME4, NULL) || NVL2(CNAME5, ',' || CNAME5, NULL) || NVL2(CNAME6, ',' || CNAME6, NULL) || NVL2(CNAME7, ',' || CNAME7, NULL) || NVL2(CNAME8, ',' || CNAME8, NULL) COLUMNS FROM (SELECT B.TABLE_NAME, B.CONSTRAINT_NAME, MAX(DECODE(POSITION, 1, COLUMN_NAME, NULL)) CNAME1, MAX(DECODE(POSITION, 2, COLUMN_NAME, NULL)) CNAME2, MAX(DECODE(POSITION, 3, COLUMN_NAME, NULL)) CNAME3, MAX(DECODE(POSITION, 4, COLUMN_NAME, NULL)) CNAME4, MAX(DECODE(POSITION, 5, COLUMN_NAME, NULL)) CNAME5, MAX(DECODE(POSITION, 6, COLUMN_NAME, NULL)) CNAME6, MAX(DECODE(POSITION, 7, COLUMN_NAME, NULL)) CNAME7, MAX(DECODE(POSITION, 8, COLUMN_NAME, NULL)) CNAME8, COUNT(*) COL_CNT FROM (SELECT SUBSTR(TABLE_NAME, 1, 30) TABLE_NAME, SUBSTR(CONSTRAINT_NAME, 1, 30) CONSTRAINT_NAME, SUBSTR(COLUMN_NAME, 1, 30) COLUMN_NAME, POSITION FROM USER_CONS_COLUMNS) A, USER_CONSTRAINTS B WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND B.CONSTRAINT_TYPE = 'R' GROUP BY B.TABLE_NAME, B.CONSTRAINT_NAME) CONS WHERE COL_CNT > ALL (SELECT COUNT(*) FROM USER_IND_COLUMNS I WHERE I.TABLE_NAME = CONS.TABLE_NAME AND I.COLUMN_NAME IN (CNAME1, CNAME2, CNAME3, CNAME4, CNAME5, CNAME6, CNAME7, CNAME8) AND I.COLUMN_POSITION <= CONS.COL_CNT 37 GROUP BY I.INDEX_NAME) ; TABLE_NAME CONSTRAINT_NAME COLUMNS --------------- -------------------- --------------- EMP_1 FK_EMP_1 DEPTNO 1 row selected.
====外键上面要建立索引===========
