易截截图软件、单文件、免安装、纯绿色、仅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经典面试题集(二)

第十一题:
有表students(name,class,grade),请用标准sql语句完成
name class grade
张三 数学 81
李四 语文 70
王五 数学 90
张三 语文 60
李四 数学 100
王五 语文 90
王五 英语 81
要求: 用sql语句输出各门功课都大于80分的同学姓名?
create table students (
name varchar(25),
class varchar(25),
grad ......

(转)SQL经典面试题集(三)


第二十题:
怎么样抽取重复记录
表:
id name
--------
1 test1
2 test2
3 test3
4 test4
5 test5
6 test6
2 test2
3 test3
2 test2
6 test6
查出所有有重复记录的数据,用一句sql 来实现
create table D(
id varchar (20),
name varchar (20)
)
insert into D values('1','test1')
insert into D v ......

ORACLE PL/SQL包(package)学习笔记

包由包规范和包体两部分组成。
 
1、包规范(Package Specification)
包规范,也叫做包头,包含了有关包的内容的信息。但是,它不包含任何过程的代码。
创建包头的语法一般如下
 
CREATE [OR REPLACE] PACKAGE package_name {IS | AS}
Procedure_name | function_name | variable_declaration | type_def ......

ORACLE和POSTGRESQL的SQL语句比较

oracle 方法                                                & ......

SQL语句PART5

Confirming granted privileges
Data Dictionary View                Description
ROLE_SYS_PRIVS                System privileges granted to roles
ROLE_TAB_PRIVS    & ......
© 2009 ej38.com All Rights Reserved. 关于E健网联系我们 | 站点地图 | 赣ICP备09004571号