【Oracle Database】数据库约束管理

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了【Oracle Database】数据库约束管理，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4491字，纯文字阅读大概需要7分钟。

内容图文

主键约束
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;

 

【Oracle Database】数据库约束管理

标签：where   let   enc   引用   constrain   column   自动   ble   prim   

本文系统来源：https://www.cnblogs.com/dbamonkey/p/14043959.html

内容总结

以上是互联网集市为您收集整理的【Oracle Database】数据库约束管理全部内容，希望文章能够帮你解决【Oracle Database】数据库约束管理所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---