Oracle SQL&PLSQL课件：第10章 约束.ppt

单击此处编辑母版标题样式,*,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,Oracle SQL&PL/SQL,第10,章 约束,描述主要的约束类型,建立表的同时创建约束,对已存在的表添加、删除约束,禁用、启用约束,本章要点,什么是约束,约束在表一级实施相应规则,约束可以防止无效的数据进入表中,约束可以阻止删除和其它表具有相关性的表,以下是,Oracle,中,有效的约束类型,：,NOT NULL,UNIQUE Key,PRIMARY KEY,FOREIGN KEY,CHECK,约束准则,给约束命名或者由,Oreacle,服务器产生一个形式为,SYS_,Cn,的名称,创建约束,:,创建表的同时便创建约束,表创建以后再创建约束,在列,或者表一级定义约束,在数据字典表,USER_CONSTRAINTS,中可以查询约束,定义约束,CREATE TABLE schema.table,(column,datatype,DEFAULT,expr,column_constraint,table_constraint);,CREATE TABLE,emp,(,empno,NUMBER(4),ename,VARCHAR2(10),deptno,NUMBER(7,2)NOT NULL,CONSTRAINT,emp,_,empno,_,pk,PRIMARY KEY(EMPNO);,定义约束,列约束级：只能引用一个列，属于列定义,表约束级：可引用一个或多个列，属于表定义，可以定义为除,NOT NULL,以外的约束。,column CONSTRAINT constraint_name constraint_type,column,.,CONSTRAINT constraint_name constraint_type,(column,.),NOT NULL,约束,确保该列不允许存放空值,EMP,EMPNO ENAME JOB.COMM DEPTNO,7839KINGPRESIDENT 10,7698BLAKEMANAGER 30,7782CLARKMANAGER 10,7566JONESMANAGER 20,.,NOT NULL,约束,(,该列没有记录为,NUILL),没有,NOT NULL,约束,(,任何一个记录该列可以为空值,),NOT NULL,约束,NOT NULL,约束,在列一级上定义约束,SQL CREATE TABLE employee(,2,empno,NUMBER(4),3,ename,VARCHAR2(10)NOT NULL,4jobVARCHAR2(9),5mgrNUMBER(4),6,hiredate,DATE,7,sal,NUMBER(7,2),8,comm,NUMBER(7,2),9,deptno,NUMBER(7,2)NOT NULL);,UNIQUE,约束,DEPT,DEPTNO DNAME LOC,-,10ACCOUNTINGNEW YORK,20RESEARCHDALLAS,30SALESCHICAGO,40OPERATIONSBOSTON,UNIQUE,约束,50,SALESDETROIT,60BOSTON,Insert into,不允许插入,(,SALES,已经存在,),允许插入,UNIQUE,约束,可以定义在表或列一级,SQL CREATE TABLE,deptment,(,2,deptno,NUMBER(2),3,dname,VARCHAR2(14),4loc VARCHAR2(13),5CONSTRAINT deptdname,_,uk,UNIQUE(,dname,);,UNIQUE,约束允许输入空值除非我们在相应的列上定义,NOT NULL,约束。,PRIMARY KEY,约束,DEPT,DEPTNO DNAME LOC,-,10ACCOUNTINGNEW YORK,20RESEARCHDALLAS,30SALESCHICAGO,40OPERATIONSBOSTON,PRIMARY KEY,Insert into,20,MARKETINGDALLAS,FINANCENEW YORK,不允许,(,DEPTNO,-,20,已经存在,),不允许,(,DEPTNO,不允许为空,),PRIMARY KEY,约束,可以定义在表或列一级,SQL CREATE TABLE,deptment,(,2,deptno,NUMBER(2),3,dname,VARCHAR2(14),4loc VARCHAR2(13),5CONSTRAINT dept_,dname,_,uk,UNIQUE(,dname,),6CONSTRAINT dept_,deptno,_,pk,PRIMARY KEY(,deptno,);,FOREIGN KEY,约束,DEPT,DEPTNO DNAME LOC,-,10ACCOUNTINGNEW YORK,20RESEARCHDALLAS,.,PRIMARYKEY,EMP,EMPNO ENAME JOB.COMM DEPTNO,7839KINGPRESIDENT 10,7698BLAKEMANAGER 30,.,FOREIGNKEY,7571,FORDMANAGER.200 9,7571FORDMANAGER.200,Insert into,不允许插入,(,DEPTNO,-,9,在,DEPT,表中不存在,),允许,FOREIGN KEY,约束,可以定义在表或列一级,SQL CREATE TABLE employee(,2,empno,NUMBER(4),3,ename,VARCHAR2(10)NOT NULL,4jobVARCHAR2(9),5mgrNUMBER(4),6,hiredate,DATE,7,sal,NUMBER(7,2),8,comm,NUMBER(7,2),9,deptno,NUMBER(7,2)NOT NULL,10CONSTRAINT,emp,_,deptno,_,fk,FOREIGN KEY(,deptno,),11REFERENCES dept(,deptno,);,FOREIGN KEY,约束中的关键字,FOREIGN KEY,在表或列一级上定义外键,REFERENCES,标识父表及相应的列,ON DELETE CASCADE,如果删除父表中的记录则允许级联删除子表中相关连的记录,CHECK,约束,定义每条记录都必须满足的条件,表达式不允许使用,:,对伪列的引用，例如：,CURRVAL,NEXTVAL,LEVEL,及,ROWNUM,可以调用,SYSDATE,UID,USER,及,USERENV,函数,对其它记录其它值的查询,.,deptno,NUMBER(2),CONSTRAINT,emp,_,deptno,_ck,CHECK(DEPTNO BETWEEN 10 AND 99),.,添加约束,添加、删除但是不能修改它的结构。,启用一个已经禁用的约束,使用,MODIFY,子句增加,NOT NULL,约束,ALTER TABLE,table,ADD CONSTRAINT,constraint,type,(,column,);,添加约束为,EMP,表添加一个,FOREIGN KEY,约束，指定雇员的管理员编号必须是在,EMP,表中已经存在的雇员的雇员编号。,SQL ALTER TABLE,emp,2 ADD CONSTRAINT,emp,_mgr_,fk,3 FOREIGN KEY(mgr)REFERENCES,emp,(,empno,);,Table altered.,删除约束,从,EMP,表删除管理员约束,SQL ALTER TABLE,emp,2 DROP CONSTRAINT,emp,_mgr_,fk,;,Table altered.,从,DEPT,表删除主键约束,PRIMARY KEY，,同时删除也删除了与该约束关联的,EMP,表,DEPTNO,上的外键,FOREIGN KEY,约束。,SQL ALTER TABLEdept,2 DROP PRIMARY KEY CASCADE;,Table altered.,禁用约束,在,ALTER TABLE,语句中使用,DISABLE,子句可以使约束失效,使用,CASCADE,选项可以使与该约束是从属关系的约束同时失效,SQL ALTER TABLE,deptment,2 DISABLE CONSTRAINTdept_,deptno,_,pk,CASCADE;,Table altered.,启用约束,使用,ENABLE,子句可以激活已经禁用的约束,如果您启用唯一UNIQUE,或主键,PRIMARY KEY,约束，唯一,UNIQUE,或主键,PRIMARY KEY,索引便自动创建,SQL ALTER TABLE,deptment,2 ENABLE CONSTRAINTdept_,deptno,_,pk,;,Table altered.,查询约束,查询,USER_CONSTRAINTS,表可以检索出所有约束名称及定义,CONSTRAINT_NAME C SEARCH_CONDITION,-,DEPT_DEPTNO_CK C,deptno,BETWEEN 10 AND 99,DEPT_DEPTNO_PK P,DEPT_DNAME_UK U,SQL SELECT constraint_name,constraint_type,2 search_condition,3 FROM user_constraints,4 WHERE table_name=DEPTMENT;,查询与约束相关的列,CONSTRAINT_NAME COLUMN_NAME,-,EMP_DEPTNO_FK DEPTNO,EMP_EMPNO_PK EMPNO,EMP_MGR_FK MGR,SYS_C00674 EMPNO,SYS_C00675 DEPTNO,SQL SELECTconstraint_name,column_name,2 FROM,user_cons_columns,3 WHEREtable_name=DEPTMENT;,在,USER_CONS_COLUMNS,中查询与约束相关的列,小结,在,Oracle,中可以创建下列类型的约束：,NOT NULL,UNIQUE key,PRIMARY KEY,FOREIGN KEY,CHECK,通过数据字典表,USER_CONSTRAINTS,可以查询约束名称及其定义。,