【Oracle Database】數據庫約束管理

主鍵約束
SQL>?alter?table?customers?add?constraint?customers_pk?primary?key?(customer_id);??
Table?altered.

col?constraint_name?for?a30
col?constraint_type?for?a15
col?table_name?for?a30
col?index_name?for?a30

SQL>?select?constraint_name,constraint_type,table_name,index_name,status?from?dba_constraints?where?constraint_type?=?'P'?and?owner?=?'SOE';
CONSTRAINT_NAME????????????????CONSTRAINT_TYPE?TABLE_NAME?????????????????????INDEX_NAME?????????????????????STATUS
------------------------------?---------------?------------------------------?------------------------------?--------
CUSTOMERS_PK???????????????????P???????????????CUSTOMERS??????????????????????CUSTOMERS_PK???????????????????ENABLED

col?constraint_name?for?a30
col?constraint_type?for?a15
col?table_name?for?a30
col?column_name?for?a30

SQL>?select?dba_cons_columns.constraint_name,
dba_cons_columns.table_name,
dba_cons_columns.column_name,
dba_cons_columns.position
from?dba_constraints?join?dba_cons_columns
on?(dba_constraints.constraint_name?=?dba_cons_columns.constraint_name)
where?constraint_type?=?'P'?and?dba_constraints.owner?=?'SOE';
CONSTRAINT_NAME????????????????TABLE_NAME?????????????????????COLUMN_NAME??????????????????????POSITION
------------------------------?------------------------------?------------------------------?----------
CUSTOMERS_PK???????????????????CUSTOMERS??????????????????????CUSTOMER_ID?????????????????????????????1

禁用約束
SQL>?alter?table?customers?disable?constraint?customers_pk;

啟用約束
SQL>?alter?table?customers?enable?constraint?customers_pk;

刪除約束
SQL>?alter?table?customers?drop?constraint?customers_pk;

外鍵約束
SQL>?alter?table?orders?add?constraint?orders_customer_id_fk?foreign?key?(customer_id)?references?customers?(customer_id);
Table?altered.

col?constraint_name?for?a30
col?constraint_type?for?a20
col?table_name?for?a20
col?r_constraint_name?for?a30
col?delete_rule?for?a15

SQL>?select?constraint_name,constraint_type,table_name,r_constraint_name,delete_rule,status?from?dba_constraints?where?constraint_type?=?'R'?and?owner?=?'SOE';
CONSTRAINT_NAME????????????????CONSTRAINT_TYPE??????TABLE_NAME???????????R_CONSTRAINT_NAME??????????????DELETE_RULE?????STATUS
------------------------------?--------------------?--------------------?------------------------------?---------------?--------
ORDERS_CUSTOMER_ID_FK??????????R????????????????????ORDERS???????????????CUSTOMERS_PK???????????????????NO?ACTION???????ENABLED

col?child_table_name?for?a20
col?father_table_name?for?a20
col?child_column_name?for?a20
col?father_column_name?for?a20

SQL>?select?dba_cons_columns.constraint_name,
dba_cons_columns.table_name?as?child_table_name,
dba_cons_columns.column_name?as?child_column_name,
dba_cons_columns.position,
dba_indexes.table_name?as?father_table_name,
dba_ind_columns.column_name?as?father_column_name
from
dba_constraints?join?dba_cons_columns?on?(dba_constraints.constraint_name?=?dba_cons_columns.constraint_name)
join?dba_indexes?on?(dba_constraints.r_constraint_name?=?dba_indexes.index_name)
join?dba_ind_columns?on?(dba_indexes.index_name?=?dba_ind_columns.index_name)?where?constraint_type?='R'?and?dba_constraints.owner?=?'SOE';
CONSTRAINT_NAME????????????????CHILD_TABLE_NAME?????CHILD_COLUMN_NAME??????POSITION?FATHER_TABLE_NAME????FATHER_COLUMN_NAME
------------------------------?--------------------?--------------------?----------?--------------------?--------------------
ORDERS_CUSTOMER_ID_FK??????????ORDERS???????????????CUSTOMER_ID???????????????????1?CUSTOMERS????????????CUSTOMER_ID

1、普通外鍵約束（如果存在子表引用父表主鍵，則無法刪除父表記錄）
SQL>?alter?table?orders?add?constraint?orders_customer_id_fk?foreign?key?(customer_id)?references?customers?(customer_id);
2、級聯外鍵約束（可刪除存在引用的父表記錄，而且同時把所有有引用的子表記錄也刪除）
SQL>?alter?table?orders?add?constraint?orders_customer_id_fk?foreign?key?(customer_id)?references?customers?(customer_id)?on?delete?cascade;
3、置空外鍵約束（可刪除存在引用的父表記錄，同時將子表中引用該父表主鍵的外鍵字段自動設為NULL，但該字段應允許空值）
SQL>?alter?table?orders?add?constraint?orders_customer_id_fk?foreign?key?(customer_id)?references?customers?(customer_id)?on?delete?set?null;