oracle实验24：约束
约束
Oracle服务器用约束(constraints) 来防止无效数据输入到表中。约束可以：
–多个表之间的具体关系，比如两个表之间的主外键关系。
–表在插入、更新行或者删除行的时候强制表中的数据遵循约束规则。
–对于成功的操作，约束条件是必须被满足的。
–如果表之间有依赖关系，使用约束可以防止表或表中相关数据的删除。
oracle的五类约束
- not null非空约束
- unique唯一约束
- check检测约束
- primary key主键约束
- foreign key外键约束
约束命名
- 约束命名原则：所有的约束定义存储在数据字典中。
- 如果给约束一个有意义的名字，约束易于维护，约束命名必须遵守标准的对象命名规则。
- 如果没有给约束命名，Oracle服务器将用默认格式SYS_Cn产生一个名字，这里n 是一个唯一的整数，来保证名称的唯一性。
- 建议至少应该给表的主、外键按照命名原则来命名，如可以采用这样的原则来命名，表名_字段名_约束类型。
约束相关的数据字典
- 约束相关的数据字典USER_CONSTRAINTS和USER_CONS_COLUMNS可以查约束的信息。
- USER_CONSTRAINTS表：查看表上所有的约束。
- USER_CONS_COLUMNS表查看与约束相关的列名，该视图对于那些由系统指定名字的约束特别有用。
- 在约束类型中，C代表CHECK，P代表PRIMARY KEY，R代表FOREIGN KEY，U代表UNIQUE，NOT NULL约束实际上是一个CHECK约束。
- 可以使用select constraint_name,constraint_type,table_name,column_name from user_constraints natural join user_cons_columns;语句查询。
约束的语法
CREATE TABLE [schema.] table(column datatype[ DEFAULTexpr][column_constraint],...[table_constraint][,...])
- 约束可以在两个级别上定义，表级约束与列级约束。
- 列级约束能够定义完整性约束的任何类型。
- 表级约束除了NOT NULL之外，能够定义完整性约束的任何类型。
实验24：在表上建立不同类型的约束
not null
- 定义表列后，该列必须有值
- 可以在建立表的时候说明
- 可以在表建立后修改
- 可以给约束指定名称
- 如果不指定名称，数据库会给一个系统自动指定名称，SYS_C#######
- user_constraints，user_cons_column可以查约束的信息
建立表的时候指定not null约束，一个系统命名，一个自命名。
SQL> drop table t1 purge;
表已删除。
SQL> create table t1(name char(9) not null,teleum char(8) constraints t1_tele_n1 not null);
表已创建。
SQL> desc t1
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
NAME NOT NULL CHAR(9)
TELEUM NOT NULL CHAR(8)
SQL> select constraint_name,constraint_type,table_name,column_name from
user_constraints natural join user_cons_columns;
CONSTRAINT_NAME C TABLE_NAME COLUMN_NAME
------------------------------ - -----------------------------------------------
PK_DEPT P DEPT DEPTNO
FK_DEPTNO R EMP DEPTNO
SYS_C004500 C T1 NAME
T1_TELE_N1 C T1 TELEUM
建立表后指定not null约束
SQL> drop table t1 purge;
表已删除。
SQL> create table t1 as select * from dept;
表已创建。
SQL> desc t1
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
使用modify语法，系统命名。
SQL> alter table t1 modify(dname not null);
表已更改。
SQL> desc t1;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
DEPTNO NUMBER(2)
DNAME NOT NULL VARCHAR2(14)
LOC VARCHAR2(13)
唯一约束UNIQUE
- 列的值不能重复
- 可以为null
- 用索引来维护唯一的
- 索引的名称和约束的名称相同
建立表时指定UNIQUE约束
SQL> drop table t1 purge;
表已删除。
SQL> create table t1 (name char(9) unique, mail char(8) constraint t1_mail_u unique);
表已创建。
SQL> select constraint_name,constraint_type,table_name,column_name from
user_constraints natural join user_cons_columns;
CONSTRAINT_NAME C TABLE_NAME COLUMN_NAME
------------------------------ - -------------------------------------------------
PK_DEPT P DEPT DEPTNO
PK_EMP P EMP EMPNO
FK_DEPTNO R EMP DEPTNO
SYS_C004503 U T1 NAME
T1_MAIL_U U T1 MAIL
SQL> desc t1
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
NAME CHAR(9)
MAIL CHAR(8)
建立表之后指定UNIQUE约束
SQL> drop table t1 purge;
表已删除。
SQL> create table t1 as select * from dept;
表已创建。
SQL> alter table t1 add constraint u_dname unique (dname);
表已更改。
SQL> select constraint_name,constraint_type,table_name,column_name from
user_constraints natural join user_cons_columns;
CONSTRAINT_NAME C TABLE_NAME COLUMN_NAME
------------------------------ - ----------------------------------------------------
PK_DEPT P DEPT DEPTNO
PK_EMP P EMP EMPNO
FK_DEPTNO R EMP DEPTNO
U_DNAME U T1 DNAME
SQL> select table_name ,index_name,column_name from user_ind_columns;
TABLE_NAME INDEX_NAME COLUMN_NAME
------------------------------ ------------------------------------------------------
DEPT PK_DEPT DEPTNO
EMP PK_EMP EMPNO
T1 U_DNAME DNAME
check约束
- 定义在字段上的每一记录都要满足的条件
- 在check中定义检查的条件表达式，数据需要符合设置的条件
- 条件表达式不允许使用
- :SYSDATE, UID, USER, USERENV 等函数
- 参照其他记录的值
建立表时指定check约束
SQL> drop table t1 purge;
表已删除。
SQL> create table t1(name varchar2(8) check (length(name)>4),mail varchar2(10));
表已创建。
SQL> desc t1
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(8)
MAIL VARCHAR2(10)
建立表之后指定check约束
SQL> alter table t1 add constraint check_mail check(length(mail)>4) ;
表已更改。
违反约束，会操作失败，给出提示。
SQL> insert into t1 (name,mail) values('asd','zxcvb');
insert into t1 (name,mail) values('asd','zxcvb')
*
第 1 行出现错误:
ORA-02290: 违反检查约束条件 (SCOTT.SYS_C004527)
SQL> insert into t1 (name,mail) values('asdff','asd');
insert into t1 (name,mail) values('asdff','asd')
*
第 1 行出现错误:
ORA-02290: 违反检查约束条件 (SCOTT.CHECK_MAIL)
符合约束条件，操作成功。
SQL> insert into t1 (name,mail) values('asdff','asdfg');
已创建 1 行。
primary key主键约束
- 一个表只能有一个主键
- 主键要求唯一并且非空
- 可以是联合主键，联合主键每列都要求非空
- 主键能唯一定位一行，所以主键也叫逻辑rowid
- 主键不是必需的，可以没有
- 主键是通过索引实现的
- 索引的名称和主键的名称相同
建立表的时候指定主键，系统命名
SQL> drop table t1 purge;
表已删除。
SQL> create table t1(mail char(8) primary key, name char(8));
表已创建。
查询用户的约束信息表
SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,COLUMN_NAME
FROM user_constraints natural join user_cons_columns;
CONSTRAINT_NAME C TABLE_NAME COLUMN_NAME
----------------------------- - -----------------------------------------
PK_DEPT P DEPT DEPTNO
PK_EMP P EMP EMPNO
FK_DEPTNO R EMP DEPTNO
SYS_C004529 P T1 MAIL
查询用户的索引信息表
SQL> select table_name,index_name,column_name from user_ind_columns;
TABLE_NAME INDEX_NAME COLUMN_NAME
--------------------------- -------------------------------------------
DEPT PK_DEPT DEPTNO
EMP PK_EMP EMPNO
T1 SYS_C004529 MAIL
表建立后指定自命名主键
SQL> drop table t1 purge;
表已删除。
SQL> create table t1(mail char(8),name char(8));
表已创建。
SQL> alter table t1 add constraint pk_t1_mail primary key(mail);
表已更改。
SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,COLUMN_NAME
FROM user_constraints natural join user_cons_columns;
CONSTRAINT_NAME C TABLE_NAME COLUMN_NAME
------------------------------ - -----------------------------------------
PK_DEPT P DEPT DEPTNO
PK_EMP P EMP EMPNO
FK_DEPTNO R EMP DEPTNO
PK_T1_MAIL P T1 MAIL
SQL> select table_name,index_name,column_name from user_ind_columns;
TABLE_NAME INDEX_NAME COLUMN_NAME
--------------------------- ---------------------------------------------
DEPT PK_DEPT DEPTNO
EMP PK_EMP EMPNO
T1 PK_T1_MAIL MAIL
foreign key 外键
- 指定在表的列上
- 引用本表其他列，或其他表的其他列
- 被引用的列有唯一约束或者主键约束
- 目的是维护数据的完整性
- 核心是一列是另外一列的自己，null除外
建立主键，建立一个外键来引用主键
SQL> drop table e purge;
drop table e purge
*
第 1 行出现错误:
ORA-00942: 表或视图不存在
SQL> drop table d purge;
drop table d purge
*
第 1 行出现错误:
ORA-00942: 表或视图不存在
SQL> create table d as select * FROM dept;
表已创建。
SQL> create table e as select * from emp;
表已创建。
SQL> alter table d add constraint pk_d primary key (deptno);
表已更改。
SQL> alter table e add constraint pk_e foreign key(deptno) references d(deptno);
表已更改。
违反约束
SQL> delete d where deptno=10;
delete d where deptno=10
*
第 1 行出现错误:
ORA-02292: 违反完整约束条件 (SCOTT.PK_E) - 已找到子记录
SQL> update e set deptno=50;
update e set deptno=50
*
第 1 行出现错误:
ORA-02291: 违反完整约束条件 (SCOTT.PK_E) - 未找到父项关键字
SQL> select table_name,index_name,column_name from user_ind_columns;
TABLE_NAME INDEX_NAME COLUMN_NAME
---------------------------- ---------------------------------------------------
DEPT PK_DEPT DEPTNO
EMP PK_EMP EMPNO
T1 PK_T1_MAIL MAIL
D PK_D DEPTNO
SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,COLUMN_NAME
FROM user_constraints natural join user_cons_columns;
CONSTRAINT_NAME C TABLE_NAME COLUMN_NAME
------------------------------ - --------------------------------------------------
PK_DEPT P DEPT DEPTNO
PK_EMP P EMP EMPNO
FK_DEPTNO R EMP DEPTNO
PK_T1_MAIL P T1 MAIL
PK_D P D DEPTNO
PK_E R E DEPTNO
已选择6行。
建立被级联的外键
父表的值被删除，子表的相关列自动被赋予null
SQL> alter table e drop constraint pk_e;
表已更改。
SQL> alter table e add constraint fk_e foreign key(deptno)
references d(deptno) on delete set null;
表已更改。
父表的值被删除，子表的相关行自动被删除
SQL> alter table e drop constraint fk_e;
表已更改。
SQL> alter table e add constraint fk_e foreign key(deptno)
references d(deptno) on delete cascade;
表已更改。
验证过程及结果
SQL> select * from d;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select empno,ename,deptno from e;
EMPNO ENAME DEPTNO
---------- ---------- ----------
7369 SMITH 20
7499 ALLEN 30
7521 WARD 30
7566 JONES 20
7654 MARTIN 30
7698 BLAKE 30
7782 CLARK 10
7839 KING 10
7844 TURNER 30
7900 JAMES 30
7902 FORD 20
7934 MILLER 10
已选择12行。
SQL> delete d where deptno=10;
已删除 1 行。
SQL> select * from d;
DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select ename,deptno from e;
ENAME DEPTNO
---------- ----------
SMITH 20
ALLEN 30
WARD 30
JONES 20
MARTIN 30
BLAKE 30
TURNER 30
JAMES 30
FORD 20
已选择9行。
增加约束语法：
- ALTER TABLE table ADD [CONSTRAINT constraint] type (column);
删除约束：
- Alter table dossier drop constraint dossier_countrycode_fk;
- Alter table country drop primary key CASCADE;
禁用约束语法：
- ALTER TABLE table DISABLE CONSTRAINT constraint[CASCADE];
启用约束语法：
- ALTER TABLE table ENABLE CONSTRAINT constraint;
