PLSQL - 取得各資料表的 PK 與 FK，並且啟用及停止 FK | 張小呆的碎碎唸 - 點部落

2011-01-10

PLSQL - 取得各資料表的 PK 與 FK，並且啟用及停止 FK

摘要:PLSQL - 取得各資料表的 PK 與 FK，並且啟用及停止 FK

本篇主要是分享如何在 Oracle 中找到資料表中有哪一些 PK 與 FK，並且針對 FK 做啟用及停用。那獲取到資料表中有哪一些 PK 與 FK 有啥好處呢!? 其實這對於管理者或者要快速查找時，會有很大的幫助，比起在茫茫資料表撈針，這種方式比較好檢閱；而啟用與停用 FK，主要是要刪除一些資料時用到，當然本篇不提倡停用 FK，但萬不得已時要用還是得用的，以下就來看看唄...

Code：

select * from user_constraints

Column	Datatype	NULL	Description
`OWNER`	`VARCHAR2(30)`	`NOT NULL`	Owner of the constraint definition
`CONSTRAINT_NAME`	`VARCHAR2(30)`	`NOT NULL`	Name of the constraint definition
`CONSTRAINT_TYPE`	`VARCHAR2(1)`		Type of constraint definition: `C` (check constraint on a table) `P` (primary key) `U` (unique key) `R` (referential integrity) `V` (with check option, on a view) `O` (with read only, on a view)
`TABLE_NAME`	`VARCHAR2(30)`	`NOT NULL`	Name associated with the table (or view) with constraint definition
`SEARCH_CONDITION`	`LONG`		Text of search condition for a check constraint
`R_OWNER`	`VARCHAR2(30)`		Owner of table referred to in a referential constraint
`R_CONSTRAINT_NAME`	`VARCHAR2(30)`		Name of the unique constraint definition for referenced table
`DELETE_RULE`	`VARCHAR2(9)`		Delete rule for a referential constraint (`CASCADE` or `NO ACTION`)
`STATUS`	`VARCHAR2(8)`		Enforcement status of constraint (`ENABLED` or `DISABLED`)
`DEFERRABLE`	`VARCHAR2(14)`		Whether the constraint is deferrable
`DEFERRED`	`VARCHAR2(9)`		Whether the constraint was initially deferred
`VALIDATED`	`VARCHAR2(13)`		Whether all data obeys the constraint (`VALIDATED` or `NOT VALIDATED`)
`GENERATED`	`VARCHAR2(14)`		Whether the name of the constraint is user or system generated
`BAD`	`VARCHAR2(3)`		A `YES` value indicates that this constraint specifies a century in an ambiguous manner. To avoid errors resulting from this ambiguity, rewrite the constraint using the `TO_DATE` function with a four-digit year. See Also: the `TO_DATE` function in Oracle Database SQL Reference and Oracle Database Application Developer's Guide - Fundamentals
`RELY`	`VARCHAR2(4)`		Whether an enabled constraint is enforced or unenforced. See Also: the constraints in Oracle Database SQL Reference
`LAST_CHANGE`	`DATE`		When the constraint was last enabled or disabled
`INDEX_OWNER`	`VARCHAR2(30)`		Name of the user owning the index
`INDEX_NAME`	`VARCHAR2(30)`		Name of the index (only shown for unique and primary-key constraints)
`INVALID`	`VARCHAR2(7)`		Whether the constraint is invalid
`VIEW_RELATED`	`VARCHAR2(14)`		Whether the constraint depends on a view

啟用及停用 FK

Code：

--啟用
ALTER TABLE TABLE NAME ENABLE NOVALIDATE CONSTRAINT FK NAME;
--停用
ALTER TABLE TABLE NAME DISABLE NOVALIDATE CONSTRAINT FK NAME;

參考：
ALL_CONSTRAINTS
45.6.1 約束：USER_CONSTRAINTS
oracle 查看主外件約束
 SQL: 設定約束條件

回首頁