Oracle数据完整性约束：主键、外键、各种约束的创建删除语句

Oracle对数据库完整性的约束：

三种方法维护数据完整性：ORACLE完整性约束，数据库触发器，应用程序代码。应尽量使用ORACLE完整性约束，可靠性和效率高，容易修改，使用灵活，记录在数据字典。

ORACLE五种约束：

非空 not null,定义的列不能为空。只能在列级定义唯一，unique，表中每一行所定义的列或列值不能相同主键primary key 不能包括空值，主键唯一标识表中每一行，一列或几列组合为主键外键foreign key 指明一列或几列的组合为外键以维护从表chilD table和主表 parent table 之间的引用完整性referential integrity条件约束check，表中每一行要满足约束条件。约束加上表上，创建表时可以定义

1.查询约束

查询表中是否有约束并显示约束名：显示表列所对应的约束的信息BYS@bys1>select a.constraint_name, a.column_name from user_cons_columns a, user_constraints b where a.table_name = upper（"&AA"）;
Enter value for aa: emp
old 1: select a.constraint_name, a.column_name from user_cons_columns a, user_constraints b where a.table_name = upper（"&AA"）
new 1: select a.constraint_name, a.column_name from user_cons_columns a, user_constraints b where a.table_name = upper（"emp"）

CONSTRAINT_NAME COLUMN_NAME
------------------------------ ---------------
PK_EMPNO EMPNO
查表中是否有主键约束BYS@bys1>select a.constraint_name, a.column_name from user_cons_columns a, user_constraints b where a.constraint_name = b.constraint_name and b.constraint_type = "P" and a.table_name =upper（"&table_name"）;
Enter value for table_name: emp
old 1: select a.constraint_name, a.column_name from user_cons_columns a, user_constraints b where a.constraint_name = b.constraint_name and b.constraint_type = "P" and a.table_name =upper（"&table_name"）
new 1: select a.constraint_name, a.column_name from user_cons_columns a, user_constraints b where a.constraint_name = b.constraint_name and b.constraint_type = "P" and a.table_name =upper（"emp"）

CONSTRAINT_NAME COLUMN_NAME
------------------------------ ---------------
PK_EMPNO EMPNO

查当前用户下的所有约束的信息BYS@bys1>col owner for a10
BYS@bys1>col table_name for a10
BYS@bys1>select * from user_cons_columns;
OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME POSITION
---------- ------------------------------ ---------- --------------- ----------
BYS PK_OBJ_ID TEST2 OBJECT_ID 1
BYS SYS_C0011203 TEST1 OBJECT_ID 1
BYS PK_EMPNO EMP EMPNO 1 查询当前用户的相关约束的状态信息，可以查dba_constraints或USER_constraints BYS@bys1>select constraint_name,table_name,constraint_type,status,deferrable,deferred,validated from dba_constraints where owner="BYS";
CONSTRAINT_NAME TABLE_NAME C STATUS DEFERRABLE DEFERRED VALIDATED
------------------------------ ---------- - -------- -------------- --------- -------------
SYS_C0011203 TEST1 P ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED
PK_EMPNO EMP P ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED
PK_OBJ_ID TEST2 P ENABLED NOT DEFERRABLE IMMEDIATE VALIDATEDSCOTT@bys1>select constraint_name,table_name,constraint_type,status,deferrable,deferred,validated from user_constraints;
CONSTRAINT_NAME TABLE_NAME C STATUS DEFERRABLE DEFERRED VALIDATED
------------------------------ ---------- - -------- -------------- --------- -------------
FK_DEPTNO EMP R ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED
PK_DEPT DEPT P ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED
PK_EMP EMP P ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED
SYS_C0011265 TEST P ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED
PK_A TEST1 P ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED

2.增加、删除、修改约束

增加与修改：

增删主键及外键alter table dept add constraint pk_dept primary key（deptno）;alter table dept2 add primary key（dname）; 不指定约束名，则由系统自动命令约束名。
alter table emp add constraint fk_deptno foreign key（deptno） references dept（deptno） on delete cascade;
alter table emp add constraint fk_deptno foreign key（deptno） references dept（deptno）;注：on delete cascade和on delete set null的作用是用来处理级联删除问题的，如果你需要删除的数据被其他数据所参照（主、外键），那么你应该决定到底希望oracle怎么处理那些参照这些即将要删除数据的数据的.你可以有三种方式：
禁止删除，这也是oracle默认的。
将那些参照本值的数据的对应列赋空，使用on delete set null关键字。即删除本表的列中的值，对本列有外键引用的表的相应的列有相应值行会被修改为NULL。
将那些参照本值的数据一并删除，使用on delete cascade关键字，即删除本表的列中的值，对本列有外键引用的表的相应的列有相应值行会被删除增加CHECK 约束:alter table dept add constraint valid_deptno check （deptno<5000）; DEPTNO列的值需要少于5000SQL>alter table customer add constraint abc check （address in （’海淀’,’朝阳’,’东城’,’西城’,’通州’,’崇文’,’昌平’））; 增加客户的住址只能是’海淀’,’朝阳’,’东城’,’西城’,’通州’,’崇文’,’昌平’；增加惟一约束alter table customer add constraint aaa unique（cardId）;增加非空约束--注意增加非空约束时用的是关键字modify，其它四种约束都是ADDSQL>alter table dept modify dname not null;

删除

对非空约束的删除：因为非空约束不能指定约束名，应该先通过查询表和列所对应的的约束信息，找出约束名，再删除。如下：BYS@bys1>alter table dept drop constraint SYS_C0011725;修改约束--大可以删除了重建alter table dept2 modify constraint pk_d2 initially immediate;删除指定名字的约束---可以是主键、外建或其它约束的名字alter table emp drop constraint PK_EMPNO;alter table scott.event drop constraint evtid_pk; 删除其它用户的约束--需要DBA权限不用指定约束名，直接删除表中主键altertable emp drop primary key cascade; 删除主键约束的时候，如果在些主键上的外键创建时未指定on delete cascade参数，直接删除主键报错，要加上cascade参数。重命令约束alter table scott.emp rename constraint pk_emp to emp_empno_pk;更多详情见请继续阅读下一页的精彩内容： http://www.linuxidc.com/Linux/2013-10/91982p2.htm相关阅读：Oracle完整性约束 http://www.linuxidc.com/Linux/2013-04/82171.htmOracle的约束和索引 http://www.linuxidc.com/Linux/2012-12/76228.htm从Oracle的约束到索引 http://www.linuxidc.com/Linux/2010-08/27544.htmOracle常用数据类型和完整性约束 http://www.linuxidc.com/Linux/2013-08/89449.htmORA-02291: 违反完整约束条件 …… - 未找到父项关键字 http://www.linuxidc.com/Linux/2013-08/89159.htm

搭建MySQL负载均衡及高可用环境Oracle开启归档模式及其相关参数相关资讯 Oracle约束数据完整性约束

Oracle约束的分类（03/17/2015 19:28:41）
Oracle约束Constraint对于CBO优化（05/05/2014 19:49:38）
Oracle数据库的字段约束创建和维护（10/02/2013 19:31:45）

Oracle外键约束（Foreign Key）的（12/06/2014 14:41:09）
Oracle数据库中由于没有做任何约束（04/01/2014 17:27:56）
Oracle常用数据类型和完整性约束（08/29/2013 06:04:45）

本文评论查看全部评论（0）