幻灯片 1

Size: px

Start display at page:

Download "幻灯片 1"

娘花
5 years ago
Views:

3 什么是约束? 约束强制规则在表级如果有从属关系, 约束防止表的删除下面的约束类型是有效的 : NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY CHECK 10-3 Copyright Oracle Corporation, All rights reserved. 约束 Oracle 服务器用约束 (constraints) 来防止无效数据输入到表中你可以使用约束做下面的事 : 在插入更新行或者从表中删除行的时候强制表中的数据遵循规则对于成功的操作, 约束必须被满足如果表之间有依赖关系, 防止表的删除为 Oracle 工具提供规则, 例如 Oracle Developer 数据一致性约束约束 NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY CHECK 说明指定列不能包含空值指定列的值或者列的组合的值对于表中所有的行必须是唯一的表的每行的唯一性标识在列和引用表的一个列之间建立并且强制一个外键关系指定一个必须为真的条件更多的信息, 见 Oracle9i SQL Reference, 约束中国科学院西安网络中心 2005 Oracle SQL 入门 10-3

4 约束原则命名一个约束, 或者由 Oracle 用 SYS_Cn 格式产生一个名字创建一个约束 : 在创建表的同时, 或者在创建表之后在列或者表级定义一个约束在数据字典中查看约束 10-4 Copyright Oracle Corporation, All rights reserved. 约束原则所有的约束存储在数据字典中如果给约束一个有意义的名字, 约束易于引用, 约束命名必须遵守标准的对象命名规则如果你不命名你的约束,Oracle 服务器将用格式 SYS_Cn 产生一个名字, 这里 n 是一个唯一的整数, 所以约束名是唯一的约束可以在创建表时定义, 也可以在表创建之后定义你可以用 USER_CONSTRAINTS 数据字典表查看对一个表的约束的定义中国科学院西安网络中心 2005 Oracle SQL 入门 10-4

5 定义约束 CREATE TABLE [schema.]table (column datatype [DEFAULT expr] [column_constraint],... [table_constraint][,...]); 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-5 Copyright Oracle Corporation, All rights reserved. 定义约束幻灯片给出了在创建表的同时定义约束的语法在语法中 : schema 与所有者同名 table 表的名字 DEFAULT expr 指定一个默认值如果在插入语句中省略了一个值, 在省略处使用该默认值 column 列的名字 datatype 列的数据类型和长度 column_constraint 是一个作为列定义一部分的完整性约束 table_constraint 是一个作为表定义一部分的完整性约束更多信息, 见 Oracle9i SQL Reference, 创建表中国科学院西安网络中心 2005 Oracle SQL 入门 10-5

6 定义约束列级约束 column [CONSTRAINT constraint_name] constraint_type, 表级约束 column,... [CONSTRAINT constraint_name] constraint_type (column,...), 10-6 Copyright Oracle Corporation, All rights reserved. 定义约束 ( 续 ) 约束通常在创建表的同时被创建在表创建后约束能够被添加, 并且约束可以可以被临时禁用约束可以在两个级别上定义约束级别列表说明只涉及一个单个的列, 对于该列用规范定义 ; 能够定义完整性约束的任何类型涉及一个或多个列, 表中的列被分别定义 ; 除了 NOT NULL, 能够定义任意约束在语法中 : constraint_name constraint_type 是约束的名字是约束的类型教师注释解释在语法中的列级和表级中国科学院西安网络中心 2005 Oracle SQL 入门 10-6

7 NOT NULL 约束确保某些列不允许空值 : NOT NULL 约束 ( 对于该列来说没有行能够包含一个空值 ) NOT NULL 约束缺少 NOT NULL 约束 ( 对于该列来说任何行都能包含空值 ) 10-7 Copyright Oracle Corporation, All rights reserved. NOT NULL 约束 NOT NULL 约束确保列无不包含空值在默认情况下, 列没有 NOT NULL 约束, 可以包含空值中国科学院西安网络中心 2005 Oracle SQL 入门 10-7

8 NOT NULL 约束定义在列级 : CREATE TABLE employees( employee_id NUMBER(6), last_name VARCHAR2(25) NOT NULL, salary NUMBER(8,2), commission_pct NUMBER(2,2), hire_date DATE CONSTRAINT emp_hire_date_nn NOT NULL,... 由系统指定约束名字用户指定约束名字 10-8 Copyright Oracle Corporation, All rights reserved. NOT NULL 约束 ( 续 ) NOT NULL 约束只能在列级被指定, 不能在表级幻灯片的例子应用 NOT NULL 约束到 EMPLOYEES 表的 LAST_NAME 和 HIRE_DATE 列因为对列 LAST_NAME 的约束未被命名,Oracle 服务器将为它创建名字在你指定约束时你可以指定约束的名字 :... last_name VARCHAR2(25) CONSTRAINT emp_last_name_nn NOT NULL... 注 : 在本课中讲述的约束的例子可能没有出现在随课程提供的表中, 如果需要, 这些约束可以被加到表中中国科学院西安网络中心 2005 Oracle SQL 入门 10-8

UNIQUE 约束 EMPLOYEES UNIQUE 约束 INSERT INTO 允许不被允许 : 已经存在 10-9 Copyright Oracle Corporation, 2001. All rights reserved.

9 UNIQUE 约束 EMPLOYEES UNIQUE 约束 INSERT INTO 允许不被允许 : 已经存在 10-9 Copyright Oracle Corporation, All rights reserved. UNIQUE 约束 UNIQUE 键完整性约束, 要求列或者列的组合中 ( 键 ) 的每个值是唯一的, 既, 在表中指定的列或列组合中不能有两行有相同的值定义 UNIQUE 键约束的列 ( 或列组合 ) 被称为唯一键 (unique key) 除非你对相同的列也定义了 NOT NULL 约束,UNIQUE 约束允许输入空值, 事实上, 对于无 NOT NULL 约束的列, 能包含空值的行可以是任意数目, 因为空不等于任何事在一个列 ( 或者在一个复合 UNIQUE 键中的所有列 ) 中的空总是满足 UNIQUE 约束注 : 因为在多于一列上的 UNIQUE 约束的搜索机制原因, 在一个部分为空的组合 UNIQUE 键约束的非空列中你不能有相同的值教师注释给学生解释由于 JSMITH ID 在第一次插入后已经存在, 不允许第二次输入中国科学院西安网络中心 2005 Oracle SQL 入门 10-9

10 UNIQUE 约束既可以定义在表级也可以定义在列级 : CREATE TABLE employees( employee_id NUMBER(6), last_name VARCHAR2(25) NOT NULL, VARCHAR2(25), salary NUMBER(8,2), commission_pct NUMBER(2,2), hire_date DATE NOT NULL,... CONSTRAINT emp_ _uk UNIQUE( )); Copyright Oracle Corporation, All rights reserved. UNIQUE 约束 ( 续 ) UNIQUE 约束既可以在列级也可以在表级定义使用表级定义时一个复合唯一键被创建幻灯片的例子应用 UNIQUE 约束到 EMPLOYEES 表的列约束的名字是 EMP_ _UK 注 :Oracle 服务器在唯一键列或组合列上隐式地创建一个唯一索引强制 UNIQUE 约束中国科学院西安网络中心 2005 Oracle SQL 入门 10-10

11 PRIMARY KEY 约束 DEPARTMENTS PRIMARY KEY 不允许 ( 空值 ) INSERT INTO 不允许 (50 已经存在 ) Copyright Oracle Corporation, All rights reserved. PRIMARY KEY 约束 PRIMARY KEY 约束为表创建一个主键每个表只能创建一个主键 PRIMARY KEY 约束是表中的对行唯一标识的一个列或者列组合, 该约束强制列或列组合的唯一性, 并且确保作为主键一部分的列不能包含空值中国科学院西安网络中心 2005 Oracle SQL 入门 10-11

12 PRIMARY KEY 约束既可以定义在表级也可以定义在列级 : 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)); Copyright Oracle Corporation, All rights reserved. PRIMARY KEY 约束 ( 续 ) PRIMARY KEY 约束既可以定义在列级也可以定义在表级用表级定义创建一个组合 PRIMARY KEY 一个表只能有一个 PRIMARY KEY 约束, 但可以有多个 UNIQUE 约束幻灯片的例子在 DEPARTMENTS 表的 DEPARTMENT_ID 列上定义了一个 PRIMARY KEY 约束约束的名字是 DEPT_ID_PK 注 : 对于一个 PRIMARY KEY 列 UNIQUE 索引被自动创建教师注释上面显示的例子在你的方案中将不工作, 因为 DEPARTMENTS 表不存在为了示范该代码, 修改脚本中的表的名字, 然后再运行脚本中国科学院西安网络中心 2005 Oracle SQL 入门 10-12

DEPARTMENTS FOREIGN KEY 约束 PRIMARY KEY EMPLOYEES FOREIGN KEY INSERT INTO 不允许 (9 不存在 ) 允许 10-13 Copyright Oracle

FOREIGN KEY 约束 FOREIGN KEY, 引用完整性约束, 指明一个列或者列的组合作为一个外键, 并且在相同表或者不同表的主键或者唯一键和外键之间建立一个关系在幻灯片的例子中, DEPARTMENT_ID 已经在

13 DEPARTMENTS FOREIGN KEY 约束 PRIMARY KEY EMPLOYEES FOREIGN KEY INSERT INTO 不允许 (9 不存在 ) 允许 Copyright Oracle Corporation, All rights reserved. FOREIGN KEY 约束 FOREIGN KEY, 引用完整性约束, 指明一个列或者列的组合作为一个外键, 并且在相同表或者不同表的主键或者唯一键和外键之间建立一个关系在幻灯片的例子中, DEPARTMENT_ID 已经在 EMPLOYEES 表 ( 依赖表或子表 ) 中被定义为外键 ; 它引用 DEPARTMENTS 表 ( 引用表或父表 ) 的 DEPARTMENT_ID 列一个外键值必须匹配一个在父表中存在的值或者空值外键基于数据值, 并且纯粹是逻辑的, 不是物理的, 指针教师注释向学生解释, 你不能创建不存在的主键值的外键中国科学院西安网络中心 2005 Oracle SQL 入门 10-13

14 FOREIGN KEY 约束既可以定义在表级也可以定义在列级 : CREATE TABLE employees( employee_id NUMBER(6), last_name VARCHAR2(25) NOT NULL, 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_ _uk UNIQUE( )); Copyright Oracle Corporation, All rights reserved. FOREIGN KEY 约束 ( 续 ) FOREIGN KEY 约束能够被定义在列或者表约束级一个组合外键必须用表级定义创建幻灯片上的例子中用表级语法定义了一个 EMPLOYEES 表的关于 DEPARTMENT_ID 列的 FOREIGN KEY 约束, 约束的名字是 EMP_DEPTID_FK 倘若约束是基于单个列的, 外键也能够被定义在列级, 其语法不同之处在于 FOREIGN KEY 不出现, 例如 : CREATE TABLE employees (... department_id NUMBER(4) CONSTRAINT emp_deptid_fk REFERENCES departments(department_id),... ) 中国科学院西安网络中心 2005 Oracle SQL 入门 10-14

15 FOREIGN KEY 约束关键字 FOREIGN KEY: 在表约束级别, 定义在子表的列中 REFERENCES: 标识表和父表中列 ON DELETE CASCADE: 当父表中的行被删除时, 删除子表中相依赖的行 ON DELETE SET NULL: 转换相依赖的外键为空 Copyright Oracle Corporation, All rights reserved. FOREIGN KEY 约束 ( 续 ) 外键被定义在子表中, 包含引用列的表是父表外键用下面关键字的组合定义 : FOREIGN KEY 被用于在表约束级定义子表中的列 REFERENCES 确定父表中的表和列 ON DELETE CASCADE 指出当父表中的行被删除时, 子表中相依赖的行也将被级联删除 ON DELETE SET NULL 当父表的值被删除时, 转换外键值为空默认行为被称为约束规则, 该规则不允许引用数据的更新或删除无 ON DELETE CASCADE 或 ON DELETE SET NULL 选项, 如果父表中的行在子表中引用, 则它不能被删除中国科学院西安网络中心 2005 Oracle SQL 入门 10-15

16 CHECK 约束定义每行必须满足的条件下面的表达式不被允许 : 涉及到 CURRVAL, NEXTVAL, LEVEL 和 ROWNUM 伪列调用 SYSDATE, UID, USER 和 USERENV 函数涉及其它行中其它值的查询..., salary NUMBER(2) CONSTRAINT emp_salary_min CHECK (salary > 0), Copyright Oracle Corporation, All rights reserved. CHECK 约束 CHECK 约束定义一个每行都必须满足的条件, 该条件可以用和查询条件一样的结构, 下面的情况例外 : 引用 CURRVAL, NEXTVAL, LEVEL 和 ROWNUM 伪列调用 SYSDATE, UID, USER 和 USERENV 函数查询涉及其它行中的其它值一个单个列在它的定义中可以有多个 CHECK 约束, 在一个列上能够定义的 CHECK 约束的数目无限制 CHECK 约束能够被定义在列级或表级 CREATE TABLE employees (... salary NUMBER(8,2) CONSTRAINT emp_salary_min CHECK (salary > 0),... 教师注释解释什么是伪列伪列不是表中实际的列, 但它们的行为象列一样, 例如, 你能从伪列中选择够选择值, 可是, 你不能插入更新或从伪列中删除伪列能被用于 SQL 语句中中国科学院西安网络中心 2005 Oracle SQL 入门 10-16

17 添加约束语法用 ALTER TABLE 语句 : 添加或删除约束, 但不修改它的结构启用或禁用约束用 MODIFY 子句添加一个 NOT NULL 约束 ALTER TABLE table ADD [CONSTRAINT constraint] type (column); Copyright Oracle Corporation, All rights reserved. 添加约束你可以用带 ADD 子句的 ALTER TABLE 语句为已经存在的表添加一个约束在语法中 : table 是表的名字 constraint 是约束的名字 type 是约束的类型 column 是受约束影响的列的名字尽管建议命名约束, 但约束名在语法中是个选项如果你不命名约束, 系统将产生约束名原则你可以添加删除或禁用一个约束, 但你不能修改它的结构你可以用 ALTER TABLE 语句的 MODIFY 子句添加一个 NOT NULL 约束到一个已经存在的列注 : 只有在表是空的或者每个行的该列都有非空值的情况下, 你才可以定义一个 NOT NULL 列教师注释你可以延迟检查约束的有效, 直到事务结束如果仅在提交的时候进行系统检查, 约束将被延迟 (deferred) 如果一个延迟的约束被违反, 则该提交导致事务回退如果在每条语句结束时进行检查, 约束是立即的 (immediate) 如果该约束被违反, 则该语句被立即回退中国科学院西安网络中心 2005 Oracle SQL 入门 10-17

18 添加约束添加一个 FOREIGN KEY 约束到 EMPLOYEES 表, 指示经理必须已经是 EMPLOYEES 表中的 ALTER TABLE employees ADD CONSTRAINT emp_manager_fk FOREIGN KEY(manager_id) REFERENCES employees(employee_id); Table altered Copyright Oracle Corporation, All rights reserved. 添加约束 ( 续 ) 幻灯片中的例子在 EMPLOYEES 表上创建 FOREIGN KEY 约束, 该约束确保在 EMPLOYEES 表中经理作为一个有效的雇员而存在教师注释用 ALTER TABLE MODIFY 语法添加一个 NOT NULL 约束 : ALTER TABLE employees MODIFY (salary CONSTRAINT emp_salary_nn NOT NULL); 中国科学院西安网络中心 2005 Oracle SQL 入门 10-18

19 删除约束从 EMPLOYEES 表中删除经理约束 ALTER TABLE employees DROP CONSTRAINT emp_manager_fk; Table altered. 删除 DEPARTMENTS 表上的 PRIMARY KEY 约束, 并且删除相关联的在 EMPLOYEES.DEPARTMENT_ID 列上的 FOREIGN KEY 约束 ALTER TABLE departments DROP PRIMARY KEY CASCADE; Table altered Copyright Oracle Corporation, All rights reserved. 删除约束为了删除约束, 你可以先从 USER_CONSTRAINTS 和 USER_CONS_COLUMNS 数据字典视图中确定约束的名字, 然后使用带 DROP 子句的 ALTER TABLE 语句 DROP 子句的 CASCADE 选项导致任何与其相依赖的约束也被删除语法 ALTER TABLE table DROP PRIMARY KEY UNIQUE (column) CONSTRAINT constraint [CASCADE]; 在语法中 : table 是表的名字 column 是受约束影响的列的名字 constraint 是约束的名字当你删除一个完整性约束时, 约束不再由 Oracle 服务器强制, 并且在数据字典中不再可用中国科学院西安网络中心 2005 Oracle SQL 入门 10-19

20 禁用约束执行 ALTER TABLE 语句的 DISABLE 子句来禁用完整性约束应用 CASCADE 选项禁用相依赖的完整性约束 ALTER TABLE DISABLE CONSTRAINT Table altered. employees emp_emp_id_pk CASCADE; Copyright Oracle Corporation, All rights reserved. 禁用约束你可以禁用一个约束而不删除它, 或者用带 DISABLE 子句的 ALTER TABLE 语句重新创建它语法 ALTER TABLE table DISABLE CONSTRAINT constraint [CASCADE]; 在语法中 : table constraint 是表的名字是约束的名字原则你即可以在 CREATE TABLE 语句也可以在 ALTER TABLE 语句中使用 DISABLE 子句 CASCADE 子句禁用相依赖的完整性约束禁用唯一或主键约束会移除唯一性索引中国科学院西安网络中心 2005 Oracle SQL 入门 10-20

21 启用约束用 ENABLE 字句启用一个在表中定义的当前禁用的完整性约束 ALTER TABLE ENABLE CONSTRAINT Table altered. employees emp_emp_id_pk; 如果启用一个 UNIQUE 键或 PRIMARY KEY 约束一个 UNIQUE 或 PRIMARY KEY 索引被自动创建 Copyright Oracle Corporation, All rights reserved. 启用约束你可以用带 ENABLE 子句的 ALTER TABLE 语句启用一个禁用的约束, 而不需要重新创建它语法 ALTER TABLE table ENABLE CONSTRAINT constraint; 在语法中 : table 是表的名字 constraint 是约束的名字原则如果启用一个约束, 约束将应用于表中所有的数据, 所有在表中的数据都必须适合该约束如果你启用一个 UNIQUE 键或者 PRIMARY KEY 约束, 一个 UNIQUE 或 PRIMARY KEY 索引将被自动地创建你即可以 CREATE TABLE 语句也可以在 ALTER TABLE 语句中使用 ENABLE 子句启用一个带 CASCADE 选项的被禁用的主键约束不会起用任何依赖于该主键的外键教师注释关于 VALIDATE 和 NOVALIDATE 选项的更多信息, 请看页的教师注释中国科学院西安网络中心 2005 Oracle SQL 入门 10-21

22 级联约束 CASCADE CONSTRAINTS 子句连同 DROP COLUMN 子句一起被使用 CASCADE CONSTRAINTS 子句删除所有定义在被删除列上的涉及主键和唯一键的引用完整性约束 CASCADE CONSTRAINTS 子句也删除所有定义在被删除列上的多列约束 Copyright Oracle Corporation, All rights reserved. 级联约束该语句举例说明 CASCADE CONSTRAINTS 子句的用法假设表 TEST1 被如下创建 : CREATE TABLE test1 ( pk NUMBER PRIMARY KEY, fk NUMBER, col1 NUMBER, col2 NUMBER, CONSTRAINT fk_constraint FOREIGN KEY (fk) REFERENCES test1, CONSTRAINT ck1 CHECK (pk > 0 and col1 > 0), CONSTRAINT ck2 CHECK (col2 > 0)); 对于下面的语句返回一个错误 : ALTER TABLE test1 DROP (pk); -- pk 是父键 ALTER TABLE test1 DROP (col1); -- col1 被多列约束 ck1 引用中国科学院西安网络中心 2005 Oracle SQL 入门 10-22

23 级联约束例子 : ALTER TABLE test1 DROP (pk) CASCADE CONSTRAINTS; Table altered. ALTER TABLE test1 DROP (pk, fk, col1) CASCADE CONSTRAINTS; Table altered Copyright Oracle Corporation, All rights reserved. 级联约束 ( 续 ) 提交下面的语句删除行 PK, 主键约束,fk_constraint 外键约束和检查约束 CK1: ALTER TABLE test1 DROP (pk) CASCADE CONSTRAINTS; 如果所有由定义在已删除列上的约束引用的列也被删除, 那么就不需要 CASCADE CONSTRAINTS, 例如, 假设没有其它表引用 PK, 提交下面的不带 CASCADE CONSTRAINTS 子句的语句是恰当的 : ALTER TABLE test1 DROP (pk, fk, col1); 教师注释让学生知道如果任何约束被来自其它表中的列或在目的表中的保留列所引用, 那么必须指定 CASCADE CONSTRAINTS, 否则, 该语句异常中断, 并且返回错误提示 ORA-12991: 在多列约束中的列被引用中国科学院西安网络中心 2005 Oracle SQL 入门 10-23

查看约束查询 USER_CONSTRAINTS 表来查看所有约束定义和命名 SELECT FROM WHERE constraint_name, constraint_type, search_condition user_constraints table_name = 'EMPLOYEES'; 10-24 Copyright Oracle Corporation, 2001.

24 查看约束查询 USER_CONSTRAINTS 表来查看所有约束定义和命名 SELECT FROM WHERE constraint_name, constraint_type, search_condition user_constraints table_name = 'EMPLOYEES'; Copyright Oracle Corporation, All rights reserved. 查看约束在创建表之后, 你可以用 DESCRIBE 命令来确认它的存在你唯一能够校验的约束是 NOT NULL 约束为了查看表上所有的约束, 查询 USER_CONSTRAINTS 表幻灯片的例子显示了 EMPLOYEES 表上的约束注 : 那些没有被表的所有者命名的约束将收到系统指定的约束名在约束类型中,C 代表 CHECK,P 代表 PRIMARY KEY,R 代表引用完整性, U 代表 UNIQUE 键注意 NOT NULL 约束实际上是一个 CHECK 约束教师注释给学生指出 NOT NULL 约束在数据字典中被作为 CHECK 约束存储将他们的注意力引到约束类型, 对于幻灯片中的 NOT NULL 约束, 在 constraint_type 域输入 C ( 表示 CHECK) 中国科学院西安网络中心 2005 Oracle SQL 入门 10-24

查看约束关联的列观察在 USER_CONS_COLUMNS 视图中与约束名关联的列 SELECT FROM WHERE constraint_name, column_name user_cons_columns table_name = 'EMPLOYEES'; 10-25 Copyright

25 查看约束关联的列观察在 USER_CONS_COLUMNS 视图中与约束名关联的列 SELECT FROM WHERE constraint_name, column_name user_cons_columns table_name = 'EMPLOYEES'; Copyright Oracle Corporation, All rights reserved. 查看约束 ( 续 ) 你可以用 USER_CONS_COLUMNS 数据字典视图查看与约束相关的列名该视图对于那些由系统指定名字的约束特别有用中国科学院西安网络中心 2005 Oracle SQL 入门 10-25

26 小结在本课中, 您应该已经学会如何创建约束 : 约束类型 : NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY CHECK 你能够通过查询 USER_CONSTRAINTS 表来观察所有约束定义和命名 Copyright Oracle Corporation, All rights reserved. 中国科学院西安网络中心 2005 Oracle SQL 入门 10-26

27 练习 10 概览本章练习包括下面的主题 : 添加约束到已经存在的表中添加更多的列到表中显示在数据字典视图中的信息 Copyright Oracle Corporation, All rights reserved. 练习 10 概览在本章的练习中, 你将用本课中学过的语句添加约束和更多的列到表中注 : 建议你命名你在练习中所定义的约束中国科学院西安网络中心 2005 Oracle SQL 入门 10-27

28 练习添加一个表级 PRIMARY KEY 约束到 EMP 表的 ID 上, 约束在创建时应该被命名, 将该约束命名为 my_emp_id_pk 提示 : 约束在 ALTER TABLE 命令执行成功后立即生效 ALTER TABLE emp ADD CONSTRAINT my_emp_id_pk PRIMARY KEY (id); 2. 创建一个 PRIMARY KEY 约束到 DEPT 表的 ID 列上, 约束在创建时应该被命名, 将该约束命名为 my_dept_id_pk 提示 : 约束在 ALTER TABLE 命令执行成功后立即生效 ALTER TABLE dept ADD CONSTRAINT my_deptid_pk PRIMARY KEY(id); 3. 添加一个列 DEPT_ID 到 EMP 表中添加一个外键到 EMP 表的 DEPT_ID 列上, 确保雇员不被指定到一个不存在的部门, 将该约束命名为 my_emp_dept_id_fk ALTER TABLE emp ADD (dept_id NUMBER(7)); ALTER TABLE emp ADD CONSTRAINT my_emp_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept(id); 4. 查询 USER_CONSTRAINTS 视图, 确认约束已被添加, 注意约束的类型和名字将语句文本保存到文件 lab10_4.sql 中 SELECT constraint_name, constraint_type FROM user_constraints WHERE table_name IN ('EMP', 'DEPT'); 5. 从 USER_OBJECTS 数据字典视图中显示 EMP 和 DEPT 表的对象名和类型注意新表和新的索引被创建 SELECT object_name, object_type FROM user_objects WHERE object_name LIKE 'EMP%' OR object_name LIKE 'DEPT%'; 如果有时间, 你可以完成下面的 : 6. 修改 EMP 表, 添加 NUMBER 数据类型的列 COMMISSION, 精度 2, 数值范围 2 添加一个约束到 commission 列, 确保佣金值大于零 ALTER TABLE EMP ADD commission NUMBER(2,2) CONSTRAINT my_emp_comm_ck CHECK (commission >= 0; 中国科学院西安网络中心 2005 Oracle SQL 入门 10-28

29 幻灯片 29 教师注释 (10-21 页 ) 你可以用带 ENABLE 或 DISABLE 的任意组合设置约束到 VALIDATE 或 NOVALIDATE VALIDATE 确保现有的数据符合约束 NOVALIDATE 意思是有些现有的数据可能不符合约束另外 : ENABLE VALIDATE 与 ENABLE 相同检查约束并且保证对所有行有效 ENABLE NOVALIDATE 意思是约束被检查, 但结果不必对所有行都为真该设置允许现有的行行违反约束, 但确保所有新行的或修改的行是正确的在一个 ALTER TABLE 语句中,ENABLE NOVALIDATE 在不确认表中现有数据的情况下, 恢复禁用的约束 DISABLE NOVALIDATE 与 DISABLE 是相同的不检查约束, 并且结果不必为真 DISABLE VALIDATE 显示约束, 删除约束上的索引, 并且不允许修改任何被约束的行在这些状态之间的事务受下面的规则支配 : 除非 NOVALIDATE 被指定,ENABLE 暗示 VALIDATE 除非 VALIDATE 被指定,DISABLE 暗示 NOVALIDATE VALIDATE 和 NOVALIDATE 对于 ENABLE 和 DISABLE 状态没有任何默认的暗示当一个唯一或主键从 DISABLE 状态移动到 ENABLE 状态, 并且没有现有的索引, 一个唯一索引被自动创建类似地, 当一个唯一或主键从 ENABLE 移动到 DISABLE, 并且一个唯一索引是启用的, 该唯一索引被删除当任何约束从 NOVALIDATE 状态移动到 VALIDATE 状态时, 所有的数据都必须被检查 ( 这个过程可能很慢 ) 但从 VALIDATE 状态移动到 NOVALIDATE 状态只是简单地忽略曾经被检查过的数据从 ENABLE NOVALIDATE 状态移动一个单个的约束到 ENABLE VALIDATE 状态不妨碍读写或者其它 DDL 语句, 它可以以并行方式执行下面的语句的启用但不验证禁用的完整性约束 : ALTER TABLE employees ENABLE NOVALIDATE CONSTRAINT EMP_ _UK; ALTER TABLE employees ENABLE NOVALIDATE PRIMARY KEY ENABLE NOVALIDATE UNIQUE (employee_id, last_name); 下面的语句启用或验证禁用的完整性约束 : ALTER TABLE employees MODIFY CONSTRAINT emp_ _uk VALIDATE; ALTER TABLE employees MODIFY PRIMARY KEY ENABLE NOVALIDATE; 中国科学院西安网络中心 2005 Oracle SQL 入门 10-29

教学目标描述主要数据库对象创建表描述列定义时可用的数据类型改变表的定义删除改名和截断表描述每个 DML 语句插入行到表中更新表中的行从表中删除行描述约束创建

教学目标描述主要数据库对象创建表描述列定义时可用的数据类型改变表的定义删除改名和截断表描述每个 DML 语句插入行到表中更新表中的行从表中删除行描述约束创建 2 第二讲创建和管理表与简单记录操作 Copyright SWFC-CISC, 2013. All rights reserved. 教学目标描述主要数据库对象创建表描述列定义时可用的数据类型改变表的定义删除改名和截断表描述每个 DML 语句插入行到表中更新表