ITPub博客

首页 > IT职业 > IT生活 > 1z0-007-Including Constraints

1z0-007-Including Constraints

原创 IT生活 作者:fsm 时间:2005-08-23 17:04:15 0 删除 编辑

数据完整性包括:类级(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是作为表结构的一部分而存在的。所以删除表同时表下面定义的约束条件也被删除。[@more@]

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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: UNIX文件的权限
请登录后发表评论 登录
全部评论

注册时间:2008-01-02

  • 博文量
    21
  • 访问量
    293138