oracle学习笔记（txt格式，自己总结的，很详细，每章附有例题和习题及答案）

1.约束类型 NOT NULL----指定列不能包含空值 UNIQUE-----指定列的值或者列的组合的值对于表中所有的行必须是唯一的 PRIMARY KEY----表的每行的唯一性标识 FOREIGN KEY----在列和表引用表的一个列之间建立并强制一个外键关系 CHECK----指定一个必须为真的条件 2.约束原则 1)命名一个约束，必须按照命名规则。如果不命名约束，Oracle服务器将用SYS_Cn格式产生一个名字，这里n是一个唯一的整数，所以约束名是唯一的。 2)可以在创建表的同时，或者创建表之后创建一个约束 3)在列或者表级定义一个约束 4)在数据字典USER_CONSTRAINTS中查看约束 3.定义约束 CREATE TABLE [schema.]table (column datatype [DEFAULT expr] [column_constraint], ... [table_constraint]); CREATE TABLE emp( empno NUMBER(4), ename VARCHAR2(10), ... deptno NUMBER(7,2) NOT NULL, CONSTRAINT emp_empno_pk PRIMARY KEY (empno)); 1)列约束级：只能引用一个列，属于列定义 column [CONSTRAINT constraint_name] constraint_type, 2)表约束级：可引用一个或多个列，属于表定义，可以定义为除NOT NULL以外的约束 column,... [CONSTRAINT constraint_name] constraint_type (column,...), 3)约束通常在创建表的同时被创建，在表创建后约束能被添加，且约束可被临时禁用 约束可以在两个级别上定义： 约束级别 说明 列 只涉及一个单个的列，对该列用规范定义； 能够定义完整性约束的任何类型 表 涉及一个或多个列，表中的列被分别定义； 除了NOT NULL，能够定义任何约束 4.NOT NULL约束 -确保某些列不允许空值： -NOT NULL约束：对该列来说没有行能够包含一个空值 -缺少NOT NULL约束：对于该列来说任何行都能包含空值 -NOT NULL约束确保列不包含控制。默认情况下，没有此约束，可包含空值 例1：在列一级上定义约束 CREATE TABLE emp( empno NUMBER(4), ename VARCHAR2(10) NOT NULL, job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(7,2) NOT NULL); 5.UNIQUE约束 -UNIQUE完整性约束要求在表中指定的列或列组合中不能有两行相同的值。定义UNIQUE约束的列（或列组合）被称为唯一键（unique key） -除非我们在相应列上定义了NOT NULL约束，否则UNIQUE约束允许输入空值 -UNIQUE可以在列级也可以在表级定义 例1：定义在表一级 CREATE TABLE deptment( deptno NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(13), CONSTRAINT dept_dname_uk UNIQUE(dname)); 例2：定义在列一级 CREATE TABLE deptment( deptno NUMBER(2), dname VARCHAR2(14) CONSTRAINT dept_dname_uk UNIQUE, loc VARCHAR2(13)); 6.PRIMARY KEY约束 -PRIMARY KEY约束为表创建一个主键 -每个表只能创建一个主键 -PRIMARY KEY约束是表中对行唯一标识的一个列或列组合，该约束强制列或列组合的唯一性 -主键列不能包含空值 -可定义在表一级或列一级 例1：定义在表一级 CREATE TABLE deptment( deptno NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(13), CONSTRAINT dept_dname_uk UNIQUE(dname), CONSTRAINT dept_deptno_pk PRIMARY KEY(deptno)); 例2：定义在列一级 CREATE TABLE deptment( deptno NUMBER(2) CONSTRAINT dept_deptno_pk PRIMARY KEY, dname VARCHAR2(14), loc VARCHAR2(13), CONSTRAINT dept_dname_uk UNIQUE(dname)); 7.FOREIGN 约束 -可定义在表或列一级 例：列一级约束： CREATE TABLE emp( empno NUMBER(4), ename VARCHAR2(10) NOT NULL, job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUBMER(7,2), comm NUMBER(7,2), deptno NUMBER(7,2) NOT NULL CONSTRAINT emp_deptno_fk REFERENCES dept(deptno)); -FOREIGN KEY:在表或列约束级别 -REFERENCES:标识父表和父表中列 -ON DELETE CASCADE:当父表中的行被删除时，删除子表中相依赖的行 -ON DELETE SET NULL:当父表的值被删除时，转换外键值为空 -在无ON DELETE CASCADE或ON DELETE SET NULL选项时，若父表中的行在子表中引用，那么它不能被删除 例：ON DELETE CASCADE选项 CREATE TABLE test( empno NUMBER(4), ename VARCHAR2(10) NOT NULL, deptno NUMBER(7,2) NOT NULL CONSTRAINT emp_deptno_fk REFERENCES dept(deptno) ON DELETE CASCADE); 8.CHECK约束 -CHECK约束定义一个每行都必须满足的条件，该条件可以使用和查询条件一样的结构 salary NUMBER(8,2) CONSTRAINT department_salary_min CHECK(salary>0), ... -不允许使用下面的表达式： -引用CURRVAL,NEXTVAL,LEVEL和ROWNUM伪列 -调用SYSDATE,UID,USER和UDERENV函数 -涉及其它行的值 9.改变约束 用ALTER TABLE语句： -添加或删除玉树，但不修改它的结构 -启用或禁用约束 -用MODIFY子句添加一个NOT NULL约束 1)添加约束 ALTER TABLE table ADD [CONSTRAINT constraint] type (column); 其中： table----表的名字 constraint----约束的名字 type----约束的类型 column----受约束影响的列的名字 例1：添加一个约束到emp表，要求薪水大于零 ALTER TABLE emp ADD CONSTRAINT emp_sal CHECK (sal>0); 例2：添加一个FOREIGN KEY约束到emp表，指示经理必须已经是emp表中的职员 ALTER TABLE emp ADD CONSTRAINT emp_mgr_fk FOREIGN KEY(mgr) REFERENCES emp (empno); 可以用ALTER TABLE语句的MODIFY子句添加一个NOT NULL约束到一个已经存在的列 例： ALTER TABLE emp MODIFY (salary CONSTRAINT emp_salary_nn NOT NULL)； 注：只有在表示空的或者每个行的该列都有非空值的情况下，才可以定义NOT NULL 2)查看约束 -数据字典user_constraints SELECT table_name,constraint_name,constarint_type,status FROM user_constraints WHERE table_name='EMP'; //表名必须大写 -在constraint_type中，C代表CHECK，P代表PRIMARY KEY，R代表引用完整性，U代表UNIQUE键。NOT NULL 在数据字典中被作为CHECK约束存储 -可用USER_CONS_COLUMNS数据字典视图查看与约束相关的列名。该视图对于由系统指定名字的约束特别有用 SELECT constant_name,column_name FROM user_cons_columns WHERE table_name='EMP'; 3)禁用约束 语法： ALTER TABLE teble DISABLE CONSTRAINT constraint [CASCADE]; 其中： table----表名 constraint----约束名 注：既可以在CREATE TABLE也可以在ALTER TABLE语句中使用DISABLE子句 CASCADE子句禁用想依赖的完整性约束 例：ALTER TABLE test_p DISABLE CONSTRAINT test_deptno_pk CASCADE; -执行ALTER TABLE语句的DISABLE子句禁用完整性约束，可以禁用一个约束而不删除它 -应用CASCADE选项禁用想依赖的完整性约束 -禁用唯一键或主键会移除唯一性索引 4)启用约束 语法： ALTER TABLE table ENABLE CONSTRAINT constraint; 例：ALTER TABLE test_p ENABLE CONSTRAINT test_deptno_pk; -若启用一个约束， 约束将应用于表中所有数据，所有表中数据都必须适合该约束 -若启用一个UNIQE键或者PRIMARY KEY约束，一个UNIQUE或PRIMARY KEY索引将被自动地创建 -既可以在CREATE TABLE也可以在ALTER TABLE语句中使用ENABLE子句 -启用一个带CASCADE选项的被禁用的主键约束不会启用任何依赖于该主键的外键 5)删除约束 语法： ALTER TABLE table DROP PRIMARY KEY | UNIQUE(column)| CONSTRAINT constraint [CASCADE]; 其中： table----表名 column----受约束影响的列的名字 constraint----约束的名字 CASCADE选项导致任何与其相依赖的约束也被删除 例1：从emp表中删除经理约束 ALTER TABLE emp DROP CONSTRAINT emp_mgr_fk; 例2：删除test_p表上的PRIMARY KEY约束，并删除相关联的在test_f.deptno列上的FOREIGN KEY约束 ALTER TABLE test_p DROP PRIMARY KEY CASCADE; 删除约束步骤： -从USER_CONSTRAINTS和USER_CONS_COLUMNS数据字典视图中确定�