当前位置:操作系统 > Unix/Linux >>

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

数据完整性约束:主键、外键、各种约束的创建删除语句
 
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 VALIDATED
SCOTT@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列的值需要少于5000
SQL>alter table customer add constraint abc check (address in (’海淀’,’朝阳’,’东城’,’西城’,’通州’,’崇文’,’昌平’));           增加客户的住址只能是’海淀’,’朝阳’,’东城’,’西城’,’通州’,’崇文’,’昌平’;
增加惟一约束
alter table customer add constraint  aaa unique(cardId);
增加非空约束--注意增加非空约束时
CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,