Oracle公司内部数据库培训资料10_约束8212.pptx

《Oracle公司内部数据库培训资料10_约束8212.pptx》由会员分享，可在线阅读，更多相关《Oracle公司内部数据库培训资料10_约束8212.pptx（29页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、10Copyright Oracle Corporation,2001.All rights reserved.约束 10-2 Copyright Oracle Corporation,2001.All rights reserved.目标通过本章学习，您将可以:描述约束 创建和维护约束10-3 Copyright Oracle Corporation,2001.All rights reserved.什么是约束 约束是表级的强制规定 约束放置在表中删除有关联关系的数据 有以下五种约束:NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY CHECK10-4 Copy

2、right Oracle Corporation,2001.All rights reserved.注意事项 如果不指定约束名 Oracle server 自动按照 SYS_Cn 的格式指定约束名 在什么时候创建约束:建表的同时 建表之后 可以在表级或列级定义约束 可以通过数据字典视图查看约束10-5 Copyright Oracle Corporation,2001.All rights reserved.定义约束CREATE TABLE schema.table(column datatype DEFAULT exprcolumn_constraint,.table_constraint,

3、.);CREATE TABLE employees(employee_id NUMBER(6),first_name VARCHAR2(20),.job_id VARCHAR2(10)NOT NULL,CONSTRAINT emp_emp_id_pk PRIMARY KEY(EMPLOYEE_ID);10-6 Copyright Oracle Corporation,2001.All rights reserved.定义约束 列级 表级column CONSTRAINT constraint_name constraint_type,column,.CONSTRAINT constraint_

4、name constraint_type(column,.),10-7 Copyright Oracle Corporation,2001.All rights reserved.NOT NULL 约束保证列值不能为空:NOT NULL 约束 无NOT NULL 约束NOT NULL 约束10-8 Copyright Oracle Corporation,2001.All rights reserved.CREATE TABLE employees(employee_id NUMBER(6),last_name VARCHAR2(25)NOT NULL,salary NUMBER(8,2),c

5、ommission_pct NUMBER(2,2),hire_date DATE CONSTRAINT emp_hire_date_nn NOT NULL,.NOT NULL 约束只能定义在列级:系统命名用户命名10-9 Copyright Oracle Corporation,2001.All rights reserved.UNIQUE 约束EMPLOYEES UNIQUE 约束INSERT INTO不允许:已经存在允许10-10 Copyright Oracle Corporation,2001.All rights reserved.UNIQUE 约束可以定义在表级或列级:CREATE

6、 TABLE employees(employee_id NUMBER(6),last_name VARCHAR2(25)NOT NULL,email VARCHAR2(25),salary NUMBER(8,2),commission_pct NUMBER(2,2),hire_date DATE NOT NULL,.CONSTRAINT emp_email_uk UNIQUE(email);10-11 Copyright Oracle Corporation,2001.All rights reserved.PRIMARY KEY 约束DEPARTMENTS PRIMARY KEYINSER

7、T INTO 不允许(空值)不允许(50 已经存在)10-12 Copyright Oracle Corporation,2001.All rights reserved.CREATE TABLE departments(department_id NUMBER(4),department_name VARCHAR2(30)CONSTRAINT dept_name_nn NOT NULL,manager_id NUMBER(6),location_id NUMBER(4),CONSTRAINT dept_id_pk PRIMARY KEY(department_id);PRIMARY KEY

8、约束可以定义在表级或列级:10-13 Copyright Oracle Corporation,2001.All rights reserved.FOREIGN KEY 约束DEPARTMENTS EMPLOYEESFOREIGNKEYINSERT INTO不允许(9 不存在)允许 允许PRIMARYKEY10-14 Copyright Oracle Corporation,2001.All rights reserved.FOREIGN KEY 约束可以定义在表级或列级:CREATE TABLE employees(employee_id NUMBER(6),last_name VARCHA

9、R2(25)NOT NULL,email VARCHAR2(25),salary NUMBER(8,2),commission_pct NUMBER(2,2),hire_date DATE NOT NULL,.department_id NUMBER(4),CONSTRAINT emp_dept_fk FOREIGN KEY(department_id)REFERENCES departments(department_id),CONSTRAINT emp_email_uk UNIQUE(email);10-15 Copyright Oracle Corporation,2001.All ri

10、ghts reserved.FOREIGN KEY 约束的关键字 FOREIGN KEY:在表级指定子表中的列 REFERENCES:标示在父表中的列 ON DELETE CASCADE:当父表中的列被删除是，子表中相对应的列也被删除 ON DELETE SET NULL:子表中相应的列置空10-16 Copyright Oracle Corporation,2001.All rights reserved.CHECK 约束 定义每一行必须满足的条件 以下的表达式是不允许的:出现CURRVAL,NEXTVAL,LEVEL,和ROWNUM 伪列 使用 SYSDATE,UID,USER,和 USE

11、RENV 函数 在查询中涉及到其它列的值.,salary NUMBER(2)CONSTRAINT emp_salary_min CHECK(salary 0),.10-17 Copyright Oracle Corporation,2001.All rights reserved.添加约束的语法使用 ALTER TABLE 语句:添加或删除约束,但是不能修改约束 有效化或无效化约束 添加 NOT NULL 约束要使用 MODIFY 语句 ALTER TABLE table ADD CONSTRAINT constraint type(column);10-18 Copyright Oracle

12、 Corporation,2001.All rights reserved.添加约束添加约束举例ALTER TABLE employeesADD CONSTRAINT emp_manager_fk FOREIGN KEY(manager_id)REFERENCES employees(employee_id);Table altered.Table altered.10-19 Copyright Oracle Corporation,2001.All rights reserved.删除约束 从表 EMPLOYEES 中删除约束 使用CASCADE 选项删除约束ALTER TABLE empl

13、oyeesDROP CONSTRAINT emp_manager_fk;Table altered.Table altered.ALTER TABLE departmentsDROP PRIMARY KEY CASCADE;Table altered.Table altered.10-20 Copyright Oracle Corporation,2001.All rights reserved.无效化约束 在ALTER TABLE 语句中使用 DISABLE 子句将约束无效化。使用 CASCADE 选项将相关的约束也无效化ALTER TABLE employeesDISABLE CONSTR

14、AINT emp_emp_id_pk CASCADE;Table altered.Table altered.10-21 Copyright Oracle Corporation,2001.All rights reserved.激活约束 ENABLE 子句可将当前无效的约束激活 当定义或激活UNIQUE 或 PRIMARY KEY 约束时系统会自动创建UNIQUE 或 PRIMARY KEY 索引ALTER TABLE employeesENABLE CONSTRAINT emp_emp_id_pk;Table altered.Table altered.10-22 Copyright Or

15、acle Corporation,2001.All rights reserved.及连约束 CASCADE CONSTRAINTS 子句在 DROP COLUMN 子句中使用 在删除表的列时 CASCADE CONSTRAINTS 子句指定将相关的约束一起删除 在删除表的列时 CASCADE CONSTRAINTS 子句同时也删除多列约束10-23 Copyright Oracle Corporation,2001.All rights reserved.及连约束及连约束举例:ALTER TABLE test1 DROP(pk)CASCADE CONSTRAINTS;Table altered.Table altered.ALTER TABLE test1 DROP(pk,fk,col1)CASCADE CONSTRAINTS;Table altered.Table altered.10-24 Copyright Oracle Corporation,2001.All rights reserved.SELECT constraint_name,constraint_type,search_conditionFROM user_constraintsWHERE table_name=EMPLOYEES;查询约束查询数据字典视图 USER_CONSTRAINTS