数据完整性约束：主键、外键、各种约束的创建删除语句
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