创建唯一索引 alter 创建唯一索引 oracle

转载

mob64ca1407d5aa 2024-03-15 20:09:30

文章标签 创建唯一索引 alter oracle 数据库学习外键 文章分类 数据仓库大数据

5.1 使用视图

增加安全性。
隐藏数据的复杂性。
实现命名简洁性和易读性。
实现更改灵活性。

5.2 实现记录的唯一性

实现记录唯一性主要有以下三种方法：

用键实现。
创建唯一索引。
用序列生成唯一索引。

1. 用键实现

键能够唯一区分数据表中每个记录的属性或者属性组合，因此使用键可以保证记录的唯一性。当为表指定PRIMARY KEY约束时，Oracle通过为主键列创建唯一索引强制数据的唯一性。

-- 为学号设置主键约束
alter table stu add constraint c1 primary key(sno);

2. 创建唯一索引

唯一索引是不允许任何两行具有相同索引值得索引。创建唯一索引的语法格式如下：

create unique index name on table (column [, ...]);
-- 将已有的主键约束c1删除
alter table stu drop constraint c1;
-- 创建一个名为ind1的唯一索引
create unique index ind1 on stu(sno);
-- 向表中插入一条重复的记录后会报错

3. 使用序列实现

序列是一个可以为表中的行自动生成序列号的数据库对象，利用它可生成唯一的整数，产生一组等间隔的数值(类型为数字)，主要用于生成唯一、连续的序号。一个序列的值是由特殊的Oracle程序自动生成，因此序列避免了在应用层实现序列而引起的性能瓶颈。序列的主要用途是生成表的主键值，可以在插入语句中引用，也可以通过查询检查当前值，或使序列增至下一个值，因此可以使用序列实现记录的唯一性。
-- 创建序列需要CREATE SEQUENCE系统权限，其语法格式如下:
CREATE SEQUENCE 序列名
        [INCREMENT BY n]
        [START WITH n]
        [{MAXVALUE/ MINVALUE n|NOMAXVALUE}]
        [{CYCLEINOCYCLE}]
        [{CACHE n[NOCACHE}];
-- 删除序列的语法格式为：
DROP SEQUENCE 序列名;
-- 产生序列的第一个值
SELECT ABC.NEXTVAL FROM DUAL;
-- 产生序列的下一个值
SELECT ABC.NEXTVAL FROM DUAL;
-- 产生序列的当前值
SELECT ABC.CURRVAL FROM DUAL;

5.3 实现数据的完整性

域完整性：域完整性是对数据表中字段属性的约束，包括字段的值域、字段的类型及字段的有效规则等约束，是由确定关系结构时所定义的字段的属性决定的。
实体完整性：实体完整性即指关系中的主属性值不能为NULL且不能有相同值。实体完整性是对关系中的记录唯一性，也就是主键的约束。
参照完整性：参照完整性即指关系中的外键必须是另一个关系的主键有效值，或是NULL。参照完整性是对关系数据库中建立关联关系的数据表间数据参照引用的约束,也就是对外键的约束。

1. 域完整性

NOT NULL（非空）约束
UNIQUE（唯一）约束
CHECK约束
-- 在修改表时添加约束
alter table student modify sname not null;
alter table student add constraint stu_uk unique(sname);

2. 实体完整性

检查主键值是否唯一，如果不唯一则拒绝插入或者修改。
检查主键的各个属性是否为空，如果有一个为空，则拒绝插入或者修改。
使用ALTER TABLE语句为已经创建的表重新定义PRIMARY KEY约束
alter table student add constraint stu_pk primary key(sno);

3. 引用完整性

引用完整性是通过外键FOREIGN KEY约束来实现的。定义为FOREIGN KEY约束的列称为“外键列”，被FOREIGN KEY约束引用的列称为“引用列”。包含外键的表称为子表，也称为引用表，包含引用列的表称为父表，也称为被引用表，通过使用公共列在表之间建立一种父子关系。在表上定义的外键可以指向主键或者其他表的唯一键。

create table sc
(sno char(7),
cno char(10),
grade int,
foreign key(sno) references student(sno));
如果在定义外键PRIMARY KEY约束时使用了CASCADE关键学，那么当被引用表中的被引用列的数据被删除时，引用表中对应的外键数据也将被删除。
如果在定义外键PRIMARY KEY约束时使用了SET NULL关键字，那么当被引用表中的被引用列的数据被删除时，引用表中对应的外键数据将被设置为NULL。要使这个关键字起作用，外键列必须支持NULL值。
如果在定义外键PRIMARY KEY约束时使用了NO ACTION关键字，那么删除被引用表中的被引用列的数据将违反外键约束，该操作也会被禁止执行，这也是外键的默认引用类型。

4. 存储过程检查

-- 创建储存过程
(dept_ id IN departments.department_id%TYPE,
dept_ name IN departments.department name%TYPE,
mgr_ id IN departments.manager_id%TYPE,
loc_ id IN departments.location_id%TYPE)
IS
ept nullerror EXCEPTION;
PRAGMA EXCEPTION INIT(ept_nullerror,-1400);
ept nolocid EXCEPTION;
PRAGMA EXCEPTION INIT(ept_nolocid,-2291);
BEGIN
        INSERT INTO departments
        (department id,department name,manager_ id,location id)
        VALUES
        (dept_ id,dept name,mgr_ id,loc id);
        DBMS _OUTPUTPUT LINE(成功添加部门"|dept id);
EXCEPTION
        WHEN DUP VAL ON INDEX THEN
                RAISE APPLICATION ERROR(-20000,'部门编号不能重复);
        WHEN ept nullerror THEN
                RAISE APPLICATION ERROR(-20001,'部门编号及部门不能为空);
        WHEN ept nolocid THEN
                RAISE APPLICATION ERROR(-20002,'没有该地址");
END p_ insert dept;

5.4 避免更改引起的大量改动

为了避免基表名和列名的变化会对语句产生影响，可以采用：

使用视图为表名和列名起别名，在应用过程中可以借助视图中的名字来代替基表名和列名，当表名和列名改变时，只需改变相应视图的定义即可。
类似于视图定义，只不过提供了一种更直接更广泛的方法来为各种对象定义别名，其中也包括视图对象。
在程序中用定义游标的方法防止直接依赖于表。当表名改变时，只需改变游标定义即可。

1. 使用视图

视图是从基表中选取部分行和列所构成的虚表，使用视图可以为表名和列名重命名，在应用过程中可以借助视图中的名字来代替基表名和列名，当基表的表名和列名改变时，只需改变相应视图的定义即可。

-- 学生信息表
student(sno,sname,ssex,sage,sdept)
-- 分为两个基表
SX(sno,sname,sage)
SY(sno,ssex,sdept)
-- 应用程序(修改前)
create view v_student(学号,姓名,性别,年龄)
as select sno,sname,ssex,sage
from student;
-- 应用程序(修改后)
create view v_student(学号,姓名,性别,年龄)
as select SX.sno,SX.sname,SY.ssex,SY.sdept
from SX,SY
where SX.sno=SY.sno;

2. 使用同义词

同义词不但可以应用在表的命名中，也可以应用在视图、序列、存储过程和函数以及包中，用同义词的不便之处就是它不能对列起别名。

create [public] synonym 同义词 for 对象;
public:公共同义词，所有用户都可以引用，若省略此关键字不写，则默认是private同义词，即私有同义词，它只能为某一用户使用。
同义词:为对象起的别名，在以后使用对象时可以用此名来代替原对象名。
对象:某一特定对象名，它可以是基表、视图、序列、过程、存储函数、存储包和其他同义词，指定对象时可以指定所属用户，中间用“.”分开。
-- 创建私有同义词
create synonym stu for student;
-- 创建公共同义词
create public synonym stu ofr student;
-- 使用同义词 stu
insert into stu values('100','黎明',25,'phy');
-- 若基表名字变了只需修改同义词的定义
drop synonym person;
create synonym stu for student_infor;

3. 使用游标

在存储过程和函数中可以使用显式游标，游标相当于定义了一个查询，在以后应用中可能用这个游标的查询结果。当表名改变时，在存储过程和函数中只需改变定义在这个表上的游标即可，后面对游标的引用不用变，从而避免了直接依赖于表的操作。

declare
stu_no char(10),
stu_nane char(20),
stu_sex char(10),
cursor cur_stu
is
select sno,sname,ssex from student where sage<20;
begin
open cur_stu
fetch cur_stu into stu_no,stu_name,stu_sex;
loop
exit when cur_stu%NOTFOUND;
if stu_sex='男' then
insert into sman values(stu_no,stu_name);
else
insert into swoman values(stu_no,stu_name);
end if;
fetch cur_stu into stu_no,stu_name,stu_sex;
end loop;
close cur_stu;
end;
修改游标定义以适应变化
当将persons表名改为persons_ information 时，只需将游标定义处的表名修改一下即可，而程序将完成相同的功能。