1z0-007-Including Constraints-CSDN博客

Including Constraints

数据完整性包括：类级(demand)完整性、实体完整性（一张表中不能出现两条完全相同的纪录，即纪录唯一性）、参照完整性（表和表或表和表类之间的约束）。
What are Constraints?
* Constraints enforce rules at the table level.
* Constraints prevent the deletion of a table if there are dependencies.
* The following constraint types are valid:
-NOT NULL
-UNIQUE
-PRIMARY KEY
-FOREIGN KEY
-CHECK
constraint是作为表结构的一部分而存在的。所以删除表同时表下面定义的约束条件也被删除。
Constraint Guidelines
* Name a constraint or the Oracle server generates a name by using the SYS_cn format.注：SYS_cn中的n为序列号。
* Create a constraint either:
- At the same time as the table is created,or
- After the table has been created
* Define a constraint at the column or table level.
* View a constraint in the data dictionary
select * from user_constraints;
all_constraints
dba_constraints
Defining Constraints
CREATE TABLE [schema.]table
(column datatype [DEFAULT expr]
[column_constraint],
...
[table_constraint][,...]);

CREATE TABLE exployees(
employee_id NUMBER(6),
first_name VARCHAR2(20),
...
job_id VARCHAR2(10) NOT NULL,
CONSTRAINT emp_emp_id_pk
PRIMARY KEY (EMPLOYEE_ID));

create table test1
(id int constraint pk_1 primary key,
lname varchar2(20),
fname varchar2(20),
constraint uk_1 unique(lname,fname));
注：column 一级的constraint是不需要参数的，表级的则要用参数指明作用于那些类。
Defining Constraints
* Column constraint level
column [CONSTRAINT constraint_name] constraint_type,
* Table constraint level
column,...
[CONSTRAINT constraint_name] constraint_type
(column,...),

The NOT NULL constraint
Ensures that null values are not permitted for the column:
create table test2
(id int,
lname varchar(20) not null);
eg:
is defined at the column level:
CREATE TABLE employees(
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,
...

The UNIQUE Constraint
唯一约束，可以是column一级的，也可以是table一级的。
是primary key的补充。
* Oracle在创建唯一约束的同时自动创建相应的索引，主键创建时也会创建相应索引。

* 与主键的不同：
唯一约束在一张表上可以有多个，唯一约束所在的表列上面是允许有空值的。主键在一张表上只能有一个，而且不能为空值。
Defined at either the table level or the column level:
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),
hiredate DATE NOT NULL,
....
CONSTRAINT emp_email_uk UNIQUE(email));

The PRIMARY KEY Constraint
与unique区别：见上面.
都有唯一性，但primary key不可以填空值。且在一个表里primary key 只能有一个。

The FOREIGN KEY Constraint

用来实现表和表之间，或者是表内部不同类之间的这种相互的约束条件。
注：1、被参照的一方只能是相应的主键或唯一约束，而参照的一方（外键）可以是任意的。
2、一旦在表和表之间或表类之间形成约束条件，外键取值只能取主键已经有的值。而主键一旦被外键使用后，外键不可以被更改，同时主键所在的纪录也不可以被删除。Cascade（级联）例外
参照和被参照的数据类型必须是兼容的。

FOREIGN KEY constraint Keywords
* FOREIGN KEY:Defines the column in the child table at the table constraint level
* REFERENCES:Identifies the table and column in the parent table.
* ON DELETE CASCADE:Deletes the dependent rows in the child table when a row in the parent table is deleted.(主键记录被删除时，外键相应的纪录也被删除。级联）
* ON DELETE SET NULL:Converts dependent foreign key values to null.（逐渐被删除时，外键视为空）

eg:
alter table test5
add constraint fk_1 foreign key(rid) references test4(id)
on delete cascade;

The CHECK Constraint
限制表类的取值范围和取值格式。通常可以出现在where条件中的表达式都可以用来定义CHECK约束。
* Defines a condition that each row must satisfy
* The following expressions are not allowed:
- References to CURRVAL,NEXTVAL,LEVEL,and ROWNUM pseudocolumns（不可以参照这几个伪列）
- Calls to SYSDATE,UID,USER,and USERENV functions（不可以调用这几个系统派生函数）
- Queries that refer to other values in other rows（不可以参照其他行的值，不可使用子查询）
Adding Constraint to a Table
Alter table add constraint .......
...,salary NUMBER(2)
CONSTRAINT emp_salary_min
CHECK (salary>0,...

Adding a Constraint Syntax
Use the ALTER TABLE statement to:
* Add or drop a constraint,but not modify its structure
* Enable or disable constraints
* Add a NOT NULL constraint by using the MODIFY clause
* Enable or disable constraints
* Add a NOT NULL constraint by using the MODIFY clause
ALTER TABLE ADD [CONSTRAINT constraint] type (column);
Adding a Constraint
Add a FOREIGN KEY constraint to the EMPLOYEES table indicating that a manager must already exist as a valid employee in the EMPLOYEES table.
ALTER TABLE employees
ADD CONSTRAINT emp_manager_fk
FOREIGN KEY(manager_id)
REFERENCES employees(employee_id);

Dropping a Constraint
* Remove the manager constraint from the EMPLOYEES table.
ALTER TABLE employees
DROP CONSTRAINT emp_manager_fk;
* Remove the PRIMARY KEY constraint on the DEPARTMENTS table and drop the associated FOREIGN KEY constraint on the EMPLOYEES.DEPARTMENT_ID column.
ALTER TABLE departments DROP PRIMARY KEY CASCADE;
eg: alter table stores drop pk_3 cascade;
Disabling Constraints
* Execute the DISABLE clause of the ALTER TABLE statement to deactivate an integrity constraint.
* Apply the CASCADE option to disable dependent integrity constraints.
ALTER TABLE employees DISABLE CONSTRAINT emp_emp_id_pk CASCADE;
Enabling Constraints
* Activate an integrity constraint currently disabled in the table definition by using the ENABLE clause.
ALTER TABLE employees
ENABLE CONSTRAINT emp_emp_id_pk;
* A UNIQUE or PRIMARY KEY index is automatically created if you enable a UNIQUE key or PRIMARY KEY constraint.

Cascading Constraints
* The CASCADE CONSTRAINTS clause is used along with the DROP COLUMN clause.
* The CASCADE CONSTRAINTS clause drops all referential integrity that refer to the primary and unique keys defined on the dropped columns.
* The CASCADE CONSTRAINTS clause also drops all multicolumn constraints defined on the dropped columns.

Example:
ALTER TABLE test1 DROP (pk) CASCADE CONSTRAINTS;
ALTER TABLE test1 DROP (pk,fk,col1) CASCADE CONSTRAINTS;

Viewing Constraints
Query the USER_CONSTRAINTS table to view all constraint definitions and names.
SELECT constraint_name,constraint_type,search_condition
FROM user_constraints
WHERE table_name='EMPLOYEES';

eg: select * from user_constraints;
返回结果中，C表示约束的类型。

若要看约束在那些表类下定义的，看
View the columns associated with the constraint names in the USER_CONS_COLUMNS view.
SELECT constraint_name,column_name
FROM user_cons_columns
WHERE table_name='EMPLOYEES';

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/86728/viewspace-804976/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/86728/viewspace-804976/