[Oracle Database] Database constraint management

Primary key constraint 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 disables the constraint SQL > alter table customers disable constraint customers_pk Enable constraints SQL > alter table customers enable constraint customers_pk; delete constraints SQL > alter table customers drop constraint customers_pk; foreign key constraints 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_ID1 CUSTOMERS CUSTOMER_ID1, Normal foreign key constraint (if there is a child table referencing the parent table primary key Cannot delete parent table record) SQL > alter table orders add constraint orders_customer_id_fk foreign key (customer_id) references customers (customer_id) 2. Cascading foreign key constraints (parent table records with references can be deleted, and all child table records with references can be deleted at the same time) SQL > alter table orders add constraint orders_customer_id_fk foreign key (customer_id) references customers (customer_id) on delete cascade 3. Empty the foreign key constraint (you can delete the parent table record that has a reference, and automatically set the foreign key field in the child table referencing the parent table primary key to NULL, but this field should allow null values) SQL > alter table orders add constraint orders_customer_id_fk foreign key (customer_id) references customers (customer_id) on delete set null