Oracle基础知识之synonym(同义词)、database link(数据库链接)、数据完整性

一.同义词

（一）概念

Oracle数据库中提供了同义词管理的功能。同义词是数据库方案对象的一个别名，经常用于简化对象访问和提高对象访问的安全性。在使用同义词时，Oracle数据库将它翻译成对应方案对象的名字。与视图类似，同义词并不占用实际存储空间，只有在数据字典中保存了同义词的定义。在Oracle数据库中的大部分数据库对象，如表、视图、同义词、序列、存储过程、包等等，数据库管理员都可以根据实际情况为他们定义同义词。（本篇主要讲对表的操作）

（二）分类

1.私有同义词

• 概念

它与公共同义词所对应，是由创建它的用户所拥有。当然，这个同义词的创建者，可以通过授权控制其他用户是否有权使用属于自己的私有同义词。

• 创建

create synonym 同义词名称
for 方案名.表名;//方案名即我们所说的用户名

注：用户在自己的模式下创建私有同义词，这个用户必须拥有create synonym权限，否则不能创建私有同义词。

• 使用

select * from 同义词名;

• 删除

drop synonym 同义词名;

2.公共同义词

• 概念

由一个特殊的用户组Public所拥有。顾名思义，数据库中所有的用户都可以使用公共同义词。公共同义词往往用来标示一些比较普通的数据库对象，这些对象往往大家都需要引用。

• 创建

create public synonym 同义词名
for 方案名.表名;

• 使用

select * from 同义词名;

• 删除

drop public synonym 同义词名;

3.远程同义词

由于远程同义词要和数据库链接一起使用，所以在此不详细讲述。只需要了解其语法格式即可。

create [public] synonym 同义词名
for school_class@mydb_link;//school_class@mydb_link是创建数据库链接时的表名.链接名

（三）作用

1. 多用户协同开发中，可以屏蔽对象的名字及其持有者。如果没有同义词，当操作其他用户的表时，必须通过用户名.表名的形式，采用了Oracle同义词之后就可以隐蔽掉用户名。
2. 为用户简化sql语句。如果自己建的表的名字很长，可以为这个表创建一个Oracle同义词来简化sql开发。
3. 为分布式数据库的远程对象提供位置透明性。

二.数据库链接

（一）概念

database link是定义一个数据库到另一个数据库的路径的对象，database link允许你查询远程表及执行远程程序。在任何分布式环境里，database都是必要的。另外要注意的是database link是单向的连接。

（二）分类

1.私有数据库链接

只有创建者用户才可以使用。

• 创建

create database link 链接名
connect to 用户名 identified by 口令  //用户名是指创建链接的用户，口令即用户所对应的口令
using 链接字符串;  //链接字符串指的是被链接的数据库服务名

创建数据库链接分为两种：在同一服务器和不同服务器。如果要链接的数据库和本数据库在同一个服务器，则链接字符串直接写数据库名；若不在同一个服务器，则链接字符串为
’(DESCRIPTION = (ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))'
这个服务名保存在tnsnames.ora文件中，在该文件中定义了协议、主机名、端口和数据库名。

• 使用

select * from 表名@数据库链接名;

私有数据库链接只能创建的用户使用，同一数据库不同用户也不能使用。

• 删除

drop database link 链接名;

在哪创建在哪删除。 在删除时，若之前使用了数据库链接查询，则必须关掉查询结果。公共数据库链接也一样。

2.公共数据库链接

所有的用户均可使用。

• 创建

create public database link 链接名
connect to 用户名 identified by 口令  //用户名是指创建链接的用户，口令即用户所对应的口令
using 链接字符串;  //链接字符串指的是被链接的数据库服务名

• 使用

select * from 表名@数据库链接名;

所有用户均可使用。

• 删除

drop public database link 链接名;

（三）作用

可以实现跨数据库使用。

三.数据完整性

（一）概念

数据完整性（Data Integrity）是指数据的精确性（Accuracy） 和可靠性（Reliability）。它是应防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。

（二）类型

• 实体完整性（Entity Integrity）

是指保证表中所有的行唯一。实体完整性要求表中的所有行都有一个唯一标识符。这个唯一标识符可能是一列，也可能是几列的组合，称为主键。也就是说，表中的主键在所有行上必须取唯一值。如：student表中sno（学号）的取值必须唯一，它唯一标识了相应记录所代表的学生，学号重复是非法的。学生的姓名不能作为主键，因为完全可能存在两个学生同名同姓的情况。

• 域完整性（Domain Integrity）

是指一个列的输入有效性，是否允许为空值。如：学生的考试成绩必须在0～100之间，性别只能是“男”或“女”。

• 参照完整性（Referential Integrity）

是指保证主关键字（被引用表）和外部关键字（引用表）之间的参照关系。它涉及两个或两个以上表数据的一致性维护。外键值将引用表中包含此外键的记录和被引用表中主键与外键相匹配的记录关联起来。在输入、更改或删除记录时，参照完整性保持表之间已定义的关系，确保键值在所有表中一致。这样的一致性要求确保不会引用不存在的值，如果键值更改了，那么在整个数据库中，对该键值的所有引用要进行一致的更改。参照完整性是基于外键与主键之间的关系。例如学生学习课程的课程号必须是有效的课程号，score表（成绩表）的外键cno（课程号）将参考course表（课程表）中主键cno（课程号）以实现数据完整性。

• 4.用户自定义完整性（User-definedIntegrity）

域完整性、实体完整性及参照完整性分别在列、行、表上实施。数据完整性任何时候都可以实施，但对已有数据的表实施数据完整性时，系统要先检查表中的数据是否满足所实施的完整性，只有表中的数据满足了所实施的完整性，数据完整性才能实施成功。

数据完整性这么重要，那么如何维护呢？下面就说说如何用约束条件去实现数据完整性
完整性与约束的关系如下表：

完整性类型	约束类型	描述
实体完整性	primary key(主键约束)	每一行的唯一标识符,不允许空值,确保自动创建唯一聚集索引
域完整性	not null(非空约束)	一般主键需要设置成非空约束
实体完整性	unique(唯一约束)	防止冗余值,允许空值,确保自动创建唯一非聚集索引
域完整性	check(条件约束)	指定某个列中的可保存值的范围
引用(参照)完整性	foreign key(外键约束)	定义一列或几列,其值与本表或另一个表的主键值相匹配
域完整性	default(默认值)	使用INSERT插入数据时,若未指定某个列的取值,则将默认值插入

四.约束类型

PRIMARY KEY（主键约束）

• 创建
  创建主键的方法有很多，可以在创建表时，也可以在创建表之后。

//创建表时添加主键约束
create table school_students
(
stu_id nvarchar2(20) not null, //添加约束条件也可以在字段的后面直接添加，不过不建议这样做
stu_name nvarchar2(20) not null,
stu_sex nvarchar2(20) not null,
stu_class_id nvarchar2(20),
constraint pk_sch_stu_id primary key(stu_id)  //约束名称命名规则：约束名+表名+字段名，这样我们之后使用时更方便，也不易混淆
);
//创建表之后添加约束条件
alter table 表名
add constraint 键值 primary key(字段名);//这里的键值指的是约束名称

• 删除

alter table 表名
drop constraint 约束名称;

• 每个表最多只能有一个主键约束
• 主键列的值(或其多列的组合)是唯一的,且不允许空值
• 主键约束在指定列上创建了一个唯一索引,默认是聚集索引,也可以是非聚集的,可以指定主键对应的索引为非聚集索引
• 可以在定义主键时添加级联操作

UNIQUE（唯一约束）

• 创建

//创建表时添加唯一约束
create table school_class
(
class_id nvarchar2(20) not null, 
class_name nvarchar2(20) not null,
constraint pk_sch_class_id primary key(class_id),
constraint pk_sch_class_name unique(class_name)
);
//创建表之后添加约束条件
alter table 表名
add constraint 键值 unique(字段名);

• 删除

alter table 表名
drop constraint 约束名称;

• 一个表可有多个UNIQUE约束
• UNIQUE可出现在多列中,这些列的组合值必须唯一,但是UNIQUE所在列并不是主键列
• UNIQUE默认创建唯一性非聚集索引,但是也可以指定创建的索引为聚集索引

CHECK（条件约束）

• 创建

//创建表时添加约束
create table school_students
(
stu_id nvarchar2(20) not null,
stu_name nvarchar2(20) not null,
stu_sex nvarchar2(20) not null,
stu_credits number(2) default 0,
stu_class_id nvarchar2(20) not null,
constraint pk_school_students_stu_id primary key(stu_id),//添加主键约束
constraint ck_school_students_stu_sex check(stu_sex in('男','女')),//添加条件约束，使得添加记录时，性别只能是男或女
constraint ck_school_students_stu_credits check(stu_credits>=0 and stu_credits<=12)//添加条件约束，使得添加记录时，分数只能在0-12之间
);
//创建表之后添加

• 删除

alter table 表名
drop constraint 约束名称;

• 一个列上可以有多个CHECK约束,CHECK约束不能包含子查询语句
• 执行INSERT、UPDATE语句时将会验证该列的CHECK约束,防止发生错误,执行DELETE语句时不检查CHECK约束

FOREIGN KEY（外键约束）

外键约束定义一个或多个列,这些列可以引用当前或其它表的主键列/UNIQUE约束列,通过外键约束可以实现表和表之间的依赖关系。

• 创建

//创建表时添加唯一约束
create table school_students
(
stu_id nvarchar2(20) not null,
stu_name nvarchar2(20) not null,
stu_sex nvarchar2(20) default '男',
stu_credits number(2) default 0,
stu_class_id nvarchar2(20) not null,
constraint pk_school_students_stu_id primary key(stu_id),
constraint fk_school_students_stu_class_id foreign key(stu_class_id) references school_class(class_id) 
[on delete {cascade|set null]  //on delete cascade 表示在主表删除列时，从表的列也会随之删除，on delete set null 表示在主表删除列时，从表的列会置为空，如果不加上，默认时主表无法删除，必须将外键约束删除之后主表才可以删除列
);
//创建表之后在新建列时添加约束条件
alter table school_students
add stu_class_name nvarchar2(20) constraint fk_school_students_stu_class_name references school_class(class_name) [on delete {cascade|set null];
//创建表之后在表中原有的列上添加约束条件
alter table school_students
add constraint fk_school_students_stu_class_id foreign key(stu_class_id) references school_class(class_id) [on delete {cascade|set null];

注意，Oracle中没有ON DELETE NO ACTION，ON DELETE SET DEFAULT，
ON UPDATE NO ACTION，ON UPDATE CASCADE，ON UPDATE SET NULL，ON UPDATE SET DEFAULT

• 删除

alter table 表名
drop constraint 约束名称;

• 要先在主表中插入数据,才能在从表中插入数据,且插入从表列的数据要么和主表相等,要么为NULL
• 当我们更新主表时，从表所对应的列值也会随着更新

我们添加外键约束时应注意：

• 添加约束时，所参照的字段必须是主键或唯一键
• 不能把主表中没有的属性定义在从表的外键中
• 如果表中有数据，且定义外键约束的字段含有被参照表字段以外的值，则无法添加约束

我们在插入数据时应注意：

• A表（从表）的外键关联的为B表（主表）中的字段，则应该A有数据则B必有数据，B为空则A必为空。