【Oracle Database】数据库约束管理

主键约束SQL> alter table customers add constraint customers_pk primary key (customer_id);  Table altered.col constraint_name for a30col constraint_type for a15col table_name for a30col index_name for a30SQL> 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                   ENABLEDcol constraint_name for a30col constraint_type for a15col table_name for a30col column_name for a30SQL> select dba_cons_columns.constraint_name,dba_cons_columns.table_name,dba_cons_columns.column_name,dba_cons_columns.positionfrom dba_constraints join dba_cons_columnson (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 a30col constraint_type for a20col table_name for a20col r_constraint_name for a30col delete_rule for a15SQL> 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       ENABLEDcol child_table_name for a20col father_table_name for a20col child_column_name for a20col father_column_name for a20SQL> 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_namefromdba_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_ID1、普通外键约束（如果存在子表引用父表主键，则无法删除父表记录）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;