SQL语句PART6
Differring Constraints:
Constraints can have the following attributes: DEFFERRABLE / NOT DEFFERRABLE, INITIALLY DEFFERRED / INITIALLY IMMEDIATE.
e.g.:
alter table dept2 add constraint dept2_id_pk primary key (department_id) deferrable initially deferred; // deferring constraint on creation.
set constraints dept2_id_pk immediate // changing a specific constraint attribute.
alter session set constraints=immediate // changing all constraints for a session.
Difference between initially deferrable and initially immediate
initially deferred: waits to check the constraints until the transaction ends.
initially immediate: checks the constraint at the end of the statement execution.
Dropping a constraint:
1. Removing the manager constraint from the EMP2 table:
alter table emp2 drop constraint emp_mgr_fk;
2. Removing the primary key constraint on the dept2 table and drop the associate foreign key constraint on the emp2.department_id column:
alter table dept2 drop primary key cascade;
Disabling Constraint
1. execute the disable clause of the alter table statement to deactivate an integrity constraint.
2. apply the cascade option to disable dependent integrity constraints.
eg: alter table emp2 disable constraint emp_dt_fk;
Enabling Constraints
1. activate an integrity constraint currently disabled in the table definition by using the enable clause.
2. a unique index is automatically created if you enable a unique key or a primary key constraint.
e.g.: alter table emp2 enable constraint emp_dt_fk;
Cascading constraints
1. used along with the drop column clauses.
2. drops all referential integrity constraints that refer to the primary key and unique keys defined on the dropped columns. 3. drops all multicolumn constraints defined on the dropped columns.
e.g.:
alter table emp2 drop column employee_id cascade constraints
alter table test1 drop (col1_pk, col2_fk, col1) cascade constraints;
e.g. foreign key example1:
create table depart(departid num
相关文档：
SQL语句语法
目录
13.1. 数据定义语句
13.1.1. ALTER DATABASE语法
13.1.2. ALTER TABLE语法
13.1.3. CREATE DATABASE语法
13.1.4. CREATE INDEX语法
13.1.5. CREATE TABLE语法
13.1.6. DROP DATABASE语法
13.1.7. DROP INDEX语法
13.1.8. DROP TABLE语法
13.1.9. RENAME TABLE语法
13.2. 数据操作语� ......
基本方法:
UPDATETEXT { table_name.dest_column_name dest_text_ptr }//{ 要更新的表以及 text、ntext 或 image 列的名称,指向要更新的 text、ntext 或 image 数据的文本指针的值（由 TEXT ......
包由包规范和包体两部分组成。
1、包规范（Package Specification）
包规范，也叫做包头，包含了有关包的内容的信息。但是，它不包含任何过程的代码。
创建包头的语法一般如下
CREATE [OR REPLACE] PACKAGE package_name {IS | AS}
Procedure_name | function_name | variable_declaration | type_def ......
Subquery: (single-row subqueries and multi-rows subqueries).
select select_list
from table
where expr operator (select select_list from table);
single-row subqueries operator: =, >, >=, <, <=, <>
e.g.:
1. select department_id, min(salary) from employees group by department_id ......
constraint Example:
1. grammer:
create table [schema.]table
(column datatype [DEFAULT expr]
[column_constraint], ...
[table_constraint] [,......]);
2. example of a column_level constraint:
create table empl ......
