易截截图软件、单文件、免安装、纯绿色、仅160KB

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行列互换

问题:假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想变成(得到如下结果): 
姓名 语文 数学 物理 
---- ---- --- ......

关于SQLSERVER中更新Text类型SQL语句解析

 基本方法:
                   
UPDATETEXT { table_name.dest_column_name dest_text_ptr }//{ 要更新的表以及 text、ntext 或 image 列的名称,指向要更新的 text、ntext 或 image 数据的文本指针的值(由 TEXT ......

sql server 内存表

--1加内存表
EXEC sp_tableoption '表名','pintable', 'true' 
--2卸载内存表
EXEC sp_tableoption '表名','pintable', 'false' 
--2查询是否有内存表驻留
SELECT * from INFORMATION_SCHEMA.Tables 
WHERE TABLE_TYPE = 'BASE TABLE' 
          AND OBJECTPROP ......

SQL语句PART3

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 ......
© 2009 ej38.com All Rights Reserved. 关于E健网联系我们 | 站点地图 | 赣ICP备09004571号