■■内置约束
■约束
Oracle服务器用约束 (constraints) 来防止无效数据输入到表中。
你可以使用约束做下面的事：
:: 约束强制规则在表级
:: 在插入、更新行或者从表中删除行的时候强制表中的数据遵循规则。对于成功的操作，约束必须被满足
:: 如果表之间有依赖关系，防止表的删除
数据一致性约束
约束 说明
NOT NULL 指定列不能包含空值
UNIQUE 指定列的值或者列的组合的值对于表中所有的行必须是唯一的
PRIMARY KEY 表的每行的唯一性标识
FOREIGN KEY 在列和引用表的一个列之间建立并且强制一个外键关系
CHECK 指定一个必须为真的条件
■约束原则
所有的约束存储在数据字典中。如果给约束一个有意义的名字，约束易于引用，约束命名必须遵守标准的对象命名规则。如果你不命名你的约束，Oracle服务器将用格式 SYS_Cn产生一个名字，这里 n 是一个唯一的整数，所以约束名是唯一的。
:: 命名一个约束，或者由Oracle 用SYS_Cn格式产生一个名字
:: 创建一个约束：
–在创建表的同时，或者
–在创建表之后
:: 在列或者表级定义一个约束
:: 在数据字典中查看约束
你可以用USER_CONSTRAINTS数据字典表查看对一个表的约束的定义。
constraint_name,constraint_type列
■定义约束
CREATE TABLE [schema.] table
(column datatype [DEFAULT expr]
[column_constraint],
...
[table_constraint][,...]);
column_constraint 是一个作为列定义一部分的完整性约束
table_constraint 是一个作为表定义一部分的完整性约束
eg:
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));
:: 列级约束
column[CONSTRAINT constraint_name] constraint_type,
:: 表级约束
column,...
[CONSTRAINT constraint_name] constraint_type(column, ...),
约束通常在创建表的同时被创建。在表创建后约束能够被添加，并且约束可以可以被临时禁用。
约束可以在两个级别上定义。
约束级别 说明
列 只涉及一个单个的列，对于该列用规范定义；能够定义完整性约束的任何类型
表 涉及一个或多个列，表中的列被分别定义；除了 NOT NULL，能够定义任意约束
①NOT NULL约束
确保某些列不允许空值
NOT NULL约束确保列不包含空值。在默认情况下，列没有NOT NULL约束，可以包含空值。
:: 只能定义在列级上,不能在表级.
eg:
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, <---用户指定了约束名字
...
②UNIQUE约束
UNIQUE键完整性约束，要求列或者列的组合中 (键) 的每个值是唯一的，既，在表中指定的列或列组合中不能有两行有相同的值。定义UNIQUE键约束的列 (或列组合) 被称为唯一键 (unique key)。
除非你对相同的列也定义了NOT NULL约束，UNIQUE约束允许输入空值，事实上，对于无NOT NULL约束的列，能包含空值的行可以是任意数目，因为空不等于任何事。在一个列 (或者在一个复合UNIQUE键中的所有列) 中的空值总是满足UNIQUE约束。
注：因为在多于一列上的UNIQUE约束的搜索机制原因，在一个部分为空的组合UNIQUE键约束的非空列中你不能有相同的值。
值在第一次插入后已经存在,不允许第二次输入.
既可以定义在表级也可以定义在列级,使用表级定义时一个复合唯一键被创建。
eg:
CREATE 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));
Oracle服务器在唯一键列或组合列上隐式地创建一个唯一索引强制UNIQUE约束。
③PRIMARY KEY 约束
PRIMARY KEY约束为表创建一个主键。每个表只能创建一个主键。PRIMARY KEY约束是表中的对行唯一标识的一个列或者列组合，该约束强制列或列组合的唯一性，并且确保作为主键一部分的列不能包含空值。
eg:
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 约束,但可以有多个UNIQUE 约束.
对于一个PRIMARY KEY列, UNIQUE索引被自动创建.
④FOREIGN KEY 约束
FOREIGN KEY，引用完整性约束，指明一个列或者列的组合作为一个外键，并且在相同表或者不同表的主键或者唯一键和外键之间建立一个关系。
一个外键值必须匹配一个在父表中存在的值或者空值。
外键基于数据值，并且纯粹是逻辑的，不是物理的，指针。
FOREIGN KEY约束关键字
外键被定义在子表中，包含引用列的表是父表。外键(FOREIGN KEY)用下面关键字的组合定义：
:: FOREIGN KEY 被用于在表约束级定义子表中的列。
:: REFERENCES 确定父表中的表和列。
:: ON DELETE CASCADE 指出当父表中的行被删除时，子表中相依赖的行也将被级联删除。
:: ON DELETE SET NULL 当父表的值被删除时，转换外键值为空。
eg:
定义在表级.
CREATE 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,
...
department_id NUMBER(4),
CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)
REFERENCES departments(department_id),
CONSTRAINT emp_email_uk UNIQUE(email)
);
定义在列级上: //FOREIGN KEY不出现.
CREATE TABLE employees
(...
department_id NUMBER(4) CONSTRAINT emp_deptid_fk
REFERENCES departments(department_id),
...
);
默认行为被称为约束规则，该规则不允许引用数据的更新或删除。
无ON DELETE CASCADE或ON DELETE SET NULL选项，如果父表中的行在子表中引用，则它不能被删除。
⑤CHECK约束
CHECK约束能够被定义在列级或表级。
一个单个列在它的定义中可以有多个CHECK约束，在一个列上能够定义的CHECK 约束的数目无限制。
:: 定义每行必须满足的条件
:: 下面的表达式不被允许使用：
–涉及到CURRVAL, NEXTVAL, LEVEL和ROWNUM伪列
–调用SYSDATE, UID, USER和USERENV函数
–涉及其它行中其它值的查询
eg:
//列级定义;
CREATE TABLE employees
(...
salary NUMBER(8,2) CONSTRAINT emp_salary_min
CHECK (salary > 0),
...
伪列不是表中实际的列，但它们的行为象列一样，例如，你能从伪列中选择够选择值，可是，你不能插入、更新或从伪列中删除。伪列能被用于SQL语句中。
■添加约束
你可以用带ADD子句的ALTER TABLE语句为已经存在的表添加一个约束。
ALTER TABLE table ADD [CONSTRAINT constraint] type (column);
在语法中：
table 是表的名字
constraint 是约束的名字
type 是约束的类型
column 是受约束影响的列的名字
用ALTER TABLE语句：
:: 添加或删除约束，但不修改它的结构
:: 启用或禁用约束
:: 用MODIFY子句添加一个NOT NULL约束到一个已经存在的列。
注：只有在表是空的或者每个行的该列都有非空值的情况下，你才可以定义一个NOT NULL列。
尽管建议命名约束，但约束名在语法中是个选项。如果你不命名约束，系统将产生约束名。
你可以延迟检查约束的有效，直到事务结束。
如果仅在提交(COMMIt)的时候进行系统检查，约束将被延迟 (deferred)。如果一个延迟的约束被违反，则该提交导致事务回退。(事务回退)
如果在每条语句结束时进行检查，约束是立即的 (immediate)。如果该约束被违反，则该语句被立即回退。(语句回退)
eg:
alter table table add (id varchar(10)); //添加列
･添加约束
添加一个FOREIGN KEY约束到EMPLOYEES表，注意:经理必须已经是EMPLOYEES表中的.
eg:
ALTER TABLE employees
ADD CONSTRAINT emp_manager_fk FOREIGN KEY(manager_id)
REFERENCES employees(employee_id);
Table altered.
用ALTER TABLE MODIFY语法添加一个NOT NULL约束：
ALTER TABLE employeesMODIFY (salary CONSTRAINT emp_salary_nn NOT NULL);
■删除约束
为了删除约束，你可以先从USER_CONSTRAINTS和USER_CONS_COLUMNS数据字典视图中确定约束的名字，然后使用带DROP子句的ALTER TABLE语句。DROP子句的CASCADE选项导致任何与
其相依赖的约束也被删除。
当你删除一个完整性约束时，约束不再由Oracle服务器强制，并且在数据字典中不再可用。
语法:
ALTER TABLE table
DROP PRIMARY KEY | UNIQUE (column) |
CONSTRAINT constraint [CASCADE];
在语法中：
table 是表的名字
column 是受约束影响的列的名字
constraint 是约束的名字
:: 从EMPLOYEES表中删除经理约束.
ALTER TABLE employees DROP CONSTRAINT emp_manager_fk;
Table altered.
删除主键的同时,把相关联的外表的约束也删除
eg:
::删除DEPARTMENTS表上的PRIMARY KEY约束，并且删除相关联的在EMPLOYEES.DEPARTMENT_ID列上的FOREIGN KEY约束
alter table departments
drop primary key cascade;
Table altered.
//删除列
alter table table drop (id);
■禁用约束
你可以禁用一个约束而不删除它，或者用带DISABLE子句的ALTER TABLE语句重新创建它。
:: 执行ALTER TABLE语句的DISABLE子句来禁用完整性约束(你即可以在CREATE TABLE语句也可以在ALTER TABLE语句中使用 DISABLE子句。)
:: 应用CASCADE选项禁用相依赖的完整性约束
:: 禁用唯一或主键约束会移除唯一性索引。
语法
ALTER TABLE table
DISABLE CONSTRAINT constraint [CASCADE];
在语法中：
table 是表的名字
constraint 是约束的名字
eg:
ALTER TABLE employees
DISABLE CONSTRAINT emp_emp_id_pk CASCADE;
Table altered.
■启用约束
你可以用带ENABLE子句的ALTER TABLE语句启用一个禁用的约束，而不需要重新创建它。
语法
ALTER TABLE table
ENABLE CONSTRAINT constraint;
在语法中：
table 是表的名字
constraint 是约束的名字
原则
:: 如果启用一个约束，约束将应用于表中所有的数据，(所有在表中的数据都必须适合该约束).
:: 如果你启用一个UNIQUE键或者PRIMARY KEY约束，一个UNIQUE或PRIMARY KEY索引将被自动地创建
:: 你即可以CREATE TABLE语句也可以在ALTER TABLE语句中使用ENABLE子句。
:: 启用一个带CASCADE选项的被禁用的主键约束不会起用任何依赖于该主键的外键。
:: 用ENABLE字句启用一个在表中定义的当前禁用的完整性约束
eg:
ALTER TABLE employees ENABLE CONSTRAINT emp_emp_id_pk;
Table altered.
■级联约束
:: CASCADE CONSTRAINTS子句连同DROP COLUMN子句一起被使用
:: CASCADE CONSTRAINTS子句删除所有定义在被删除列上的涉及主键和唯一键的引用完整性约束
:: CASCADE CONSTRAINTS子句也删除所有定义在被删除列上的多列约束
eg:
如果所有由定义在已删除列上的约束引用的列也被删除，那么就不需要CASCADE CONSTRAINTS，例如，假设没有其它表引用PK，提交下面的不带CASCADE CONSTRAINTS子句的语句是恰当的：
ALTER TABLE test1 DROP (pk, fk, col1);
如果任何约束被来自其它表中的列或在目的表中的保留列所引用(既约束的列被引用)，那么必须指定CASCADE CONSTRAINTS，否则，该语句异常中断，并且返回错误提示
ORA-12991：在多列约束中的列被引用。
CREATE TABLE test1 (
pk NUMBER PRIMARY KEY,
fk NUMBER,
col1 NUMBER,
col2 NUMBER,
CONSTRAINT fk_constraint FOREIGN KEY (fk) REFERENCES test1, //也对,(感觉要回上(pk))
CONSTRAINT ck1 CHECK (pk > 0 and col1 > 0),
CONSTRAINT ck2 CHECK (col2 > 0));
对于下面的语句返回一个错误：
ALTER TABLE test1 DROP (pk); -- pk是父键
ERROR 位于第 1 行:
ORA-12992: 无法删除父项关键字列
ALTER TABLE test1 DROP (col1); -- col1被多列约束ck1引用
ERROR 位于第 1 行:
ORA-12991: 引用的列处于多列约束条件
ALTER TABLE test1 DROP (pk) CASCADE CONSTRAINTS;
Table altered.
提交这条语句删除列PK，主键约束，fk_constraint外键约束和检查约束 CK1：
ALTER TABLE test1 DROP (pk, fk, col1) CASCADE CONSTRAINTS;
Table altered.
■查看约束
在创建表之后，你可以用DESCRIBE命令来确认它的存在,你唯一能够校验的约束是NOT NULL约束。为了查看表上所有的约束，查询USER_CONSTRAINTS表。
注：那些没有被表的所有者命名的约束将收到系统指定的约束名。在约束类型中，C代表CHECK，P代表PRIMARY KEY，R代表引用完整性(referential integrity)， U代表UNIQUE键。注意NOT NULL约束实际上是一个CHECK约束。即NOT NULL约束在constraint_type域输入C(表示CHECK)
①查询USER_CONSTRAINTS表来查看所有约束定义和命名
SELECT constraint_name,
constraint_type,search_condition
FROM user_constraints
WHERE table_name = 'EMPLOYEES';
SQL> set linesize 120
SQL> SELECT constraint_name,
2 constraint_type,search_condition
3 FROM user_constraints
4 WHERE table_name = 'EMPLOYEES';
CONSTRAINT_NAME C SEARCH_CONDITION
------------------------------ - ------------------------------
EMP_LAST_NAME_NN C "LAST_NAME" IS NOT NULL
EMP_EMAIL_NN C "EMAIL" IS NOT NULL
EMP_HIRE_DATE_NN C "HIRE_DATE" IS NOT NULL
EMP_JOB_NN C "JOB_ID" IS NOT NULL
EMP_SALARY_MIN C salary > 0
EMP_EMAIL_UK U
EMP_EMP_ID_PK P
EMP_DEPT_FK R
EMP_JOB_FK R
EMP_MANAGER_FK R
已选择10行。
②查看USER_CONS_COLUMNS查看约束关联的列
观察在USER_CONS_COLUMNS视图中与约束名关联的列
SELECT constraint_name, column_name
FROM user_cons_columns
WHERE table_name = 'EMPLOYEES';
