Oracle数据库设计——定义约束 主键

Oracle数据库设计——定义约束 主键

声明约束
主键(PRIMARY KEY)

    一张表不一定有主键，但大多数表都创建了主键，主键值必须唯一并且组成主键的各列都不能为空。

    想象一下存储学生信息的一张表。在学生表（STUDENTS）每个学生有且仅有一行记录。因此，在STUDENTS表中每一行只能确定一个且仅有一个学生。举例来说，一个处理学生学费清单的应用中，每个学生只能有一张且仅有一张学费单

    除了商业强制规则，还有其他数据库要求，主键是保证参照完整性的父子关系的一部分。另外，主键需要一个索引（Index）,一块物理硬盘空间，主要是提供快速的数据访问能力

    Oracle 数据库引擎强制主键约束规则，在创建主键时，首先，所有的主键值都是唯一的，第二，它们必须有一个值，也就是说，作为主键的列不能为NULL

    主键约束执行可以暂时的disabled，然后再enabled.使用ALTER TABLE语句来完成，约束选项可为：DISABLE或ENABLE.一个原因是在加载数据时disable约束可以减少加载时间，disable约束将减少数据加载时间是因为索引被disabled了，因此索引不需要更新(not update)。

    在一个事务中，一个应用能够暂时的挂起约束执行。这里一个程序开始一个事务通过设置约束为延缓状态。数据加载后有一个commit，然后，约束重新应用于Oracle.这个选项要求约束使用DEFERRABLE关键字创建。

    你可以向一个表中加载数据时使约束disable，加载完后，enable约束，约束规则仅应用于新加载的数据。旧的数据，即使违反商业规则，仍保留在数据表中，这个面向商业规则强制性策略可以应用于数据仓库，它必须有可供分析的历史数据。这个选项要求enable约束时使用NOVALIDATE关键字

 

    几种类型的主键强制性，比如DEFERRABLE和NOVALIDATE，将影响使用主键约束的索引类型.这些选项将使用nonunique索引，一个常规主键约束，从来不试图违反它，甚至临时的在一个事务内，使用一个唯一索引。

    事实上，一个主键的表存储在数据字典内，我们看一下数据字典视图USER_TAB_COLUMNS，它是一个查看一个表内字段名的字典视图。我们也有约束的视图，是USER_CONSTRAINTS和USER_CONS_COLUMNS,这些视图展示哪些表有约束，约束名、类型和状态
创建主键约束

这部分中将会使用一个示例表：TEMP,表结构如下：

Name                           Null?    Type

ID                                      VARCHAR2(5)

NO                                      NUMBER

创建主键有几种方式：

   1、 列约束子句

   2、 表约束子句

   3、 ALTER TABLE语句

下面讨论创建主键约束的三种格式技巧和关系。其他类型的约束，Unique,Foreign key, check，也可以使用每种方式创建
列约束子句

   下面创建一张表，有两个字段，字段id为主键，这是一个列约束子句的例子。

   CREATE TABLE temp(

       id VARCHAR2(5) PRIMARY KEY,

       no NUMBER

   );

   也可以为主键约束指定名称，

   CREATE TABLE temp(

       id VARCHAR2(5) CONSTRAINT PRIMARY KEY my_constraint_name,

       no NUMBER

   );
表约束子句

    表约束子句是在表定义结尾定义主键约束。表约束子句是CREATE TABLE语句的一部分，如果约束子句有语法错误，整个语句将失败，表不会被创建。

    下面以模板方式举例说明，Create table语句声明了一个主键，表约束子句允许包括多个约束，使用“，”分隔每一个约束定义。

    CREATE TABLE temp

       (id  VARCHAR2(5),

        no  NUMBER,

       CONSTRAINT PRIMARY KEY (id),

       CONSTRAINT. . next constraint,

       CONSTRAINT. . next constraint) TABLESPACE etc;

    下面创建一个TEMP表，使用表约束子句。

    CREATE TABLE temp

       (id  VARCHAR2(5),

        no  NUMBER,

        CONSTRAINT PRIMARY KEY (id)) TABLESPACE student_data;
ALTER TABLE 语句

    alter table语句是另外一个管理约束的选择。一旦你创建了一张表，你可以使用alter table语句管理约束、增加列、改变存储参数。

     执行功能                  ALTER 语法

     Add a constraint          ALTER TABLE table_name ADD CONSTRAINT etc

     Drop a constraint         ALTER TABLE table_name DROP CONSTRAINT etc

     Disable a constraint      ALTER TABLE table_name DISABLE CONSTRAINT etc

     Enable a constraint       ALTER TABLE table_name ENABLE CONSTRAINT etc

 下面DDL包含两个DDL语句：Create table语句和Alter table语句。主键名为：PK_TEMP

 CREATE TABLE temp (

     id  VARCHAR2(5),

     no  NUMBER

 );

 ALTER TABLE temp ADD CONSTRAINT pk_temp PRIMARY KEY (id);

Alter table 命令有许多选项，一个记住语法的方法是Oracle执行这个操作需要的信息：

1）、你不得不告诉Oracle你准备alter什么表：

    Alter table table_name

2)、然后，你准备做什么？Adding 一个约束

    ALTER TABLE table_name ADD CONSTRAINT

3）、强烈建议但不要求为约束定义个名字。约束名不需要放在引号里，但会以大写字母形式存储在数据字典里，

ALTER TABLE temp ADD CONSTRAINT pk_temp

4）、表示约束类型将是Primary Key，Unique，Foreign Key，或Check约束

ALTER TABLE temp ADD CONSTRAINT pk_temp PRIMARY KEY

5)、跟在约束类型后面有几种特殊选项，Primary Key和Unique约束，需要指定约束的列；Check约束需要指定约束规则。

ALTER TABLE temp ADD CONSTRAINT pk_temp PRIMARY KEY (ID);

6)、Primary Key和Unique约束应当指定索引的表空间名，使用USING INDEX TABLESPACE子句.

ALTER TABLE temp ADD CONSTRAINT pk_temp PRIMARY KEY (ID) USING INDEX TABLESPACE student_index;

 
命名约束

    主键命名一般有两种格式：

1. PK_table_name

2. table_name_PK

    创建主键不一定非要给主键定义个名字，如果不指定主键名，oracle会自动生成一个主键名，存储在数据字典中，但如果出现逐渐冲突，查询起来没有直接定义主键名方便，如果主键名定义为：PK_TEMP，就可以迅速的知道问题的所在：TEMP表上的主键出现冲突。
主键索引（The Primary Key Index）

当创建一个主键时，oracle会自动在作为主键的列上创建一个主键索引。如果索引已存在，则oracle利用已存在的索引。

索引是主键完整的一部分。根据主键选项，索引可以是Unique也可以不是。可延期的主键约束使用nonunique索引。索引不用来增强主键的商业规则，但索引还是需要的。索引的好处是对表的查询，如果主键不可用，索引被删掉了，查询性能就非常的差。

表占用物理存储空间，索引也需要。创建主键时需要为索引指定一个表空间。因为I/O争夺和索引增长不同于表，我们总是把索引放在洋单独的表空间中。

下面Alter table语句创建一个主键，并使用Using index tablespace关键字为索引镇定表空间。

CREATE TABLE students

 (student_id    VARCHAR2(10),

  student_name  VARCHAR2(30),

  college_major VARCHAR2(15),

  status        VARCHAR2(20)) TABLESPACE student_data;

 

ALTER TABLE students

  ADD CONSTRAINT pk_students PRIMARY KEY (student_id)

  USING INDEX TABLESPACE student_index;

    如果不指定表空间，Oracle使用默认表空间里创建索引。所有的oracle用户都可以在默认表空间中创建索引。没有指定表空间的表和索引都会创建在默认表空间中。

 

设置权限配额：

REVOKE UNLIMITED TABLESPACE FROM SCOTT;

ALTER USER SCOTT QUOTA UNLIMITED ON STUDENT_DATA;

ALTER USER SCOTT QUOTA UNLIMITED ON STUDENT_INDEX;

ALTER USER SCOTT DEFAULT TABLESPACE STUDENT_DATA;

查询表空间限额

    SELECT tablespace_name, max_bytes FROM user_ts_quotas;

 

索引是一个创建在表空间中的对象。有物理结构占用物理空间。当创建一个主键或Unique约束时，自动创建或使用已存在的索引。

索引基于树型结构，被Oracle使用执行Select语句。使用索引的select语句执行效率要远远高于不使用索引的select语句。

索引创建是用来提高查询速度。

下面总结一下主键约束和索引之间的关系：

·         索引可以在任何一列或一组列上创建，而主键不能。当我们创建主键时，不管用哪种方式，都会创建索引，在不存在索引的列上创建一个索引，一个主键和Unique约束不仅仅意味着建立一个索引，常常，有序曲其他索引被创建用来达到最高性能。

·         事实上，有一个主键约束在数据字典中定义。除了数据字典表空间记录约束定义外不需要分配任何空间。然而，索引是主键约束的一部分，它是洋对象，需要占用磁盘空间，可以从数据字典视图USER_OBJECTS and USER_INDEXES, USER_SEGMENTS, and USER_EXTENTS中来查看

·         主键约束可以使用Insert和Update语句来验证，这仅意味着我们可以测试主键冲突，索引通过主键约束创建从而提供了一个有效机制来优化select查询语句。
序列（Sequences）

SQLServer使用Identity来自动生成ID序号，Oracle使用Sequence。序列是一个对象，存储在数据字典中。序列总是有一个状态信息，例如：当前值，而上下文总保留在数据字典中，一旦你创建了一个序列，就可以从数据字典视图User_Sequences查询序列的特性

序列有两个特性, NEXTVAL 和 CURRVAL:

Sequence Attribute
	

Description

sequence_name.NEXTVAL
	

下一个序列值

sequence_name.CURRVAL
	

当前的序列值

创建序列：

CREATE SEQUENCE sample_sequence

  MINVALUE   0

  START WITH 0

  MAXVALUE  20

  NCREMENT BY 5

  NOCACHE

  CYCLE;

MINVALUE   ：序列最小值

START WITH  ：序列起始值

MAXVALUE  ：序列最大值

NCREMENT BY ： 序列步进值，currval 和nextval的差值

NOCACHE / CACHE ： 是否使用序列缓存，CACHE 5表示缓存中存储5个序列值，当这些序列值用尽时，再生成5个新的序列值存在缓存中。NOCACHE表示不使用缓存.

CYCLE / NOCYCLE ：指定达到最大值后是否从新开始循环NOCYCLE 否，CYCLE是

序列命名一般：

       table_name_PK_SEQ

默认CREATE SEQUENCE语句将最大可生成 1027 数值.
Enable, Disable, and Drop

删除主键，同时删除主键索引：

ALTER TABLE <table_name> DROP CONSTRAINT

<constraint_name>;

如果有其他表把主键作为外键引用，Oracle不允许删除。

ALTER TABLE state_lookup DROP CONSTRAINT state_lookup CASCADE;

将删除主键及所有以主键为外键的约束.

 

ALTER TABLE state_lookup

DISABLE CONSTRAINT state_lookup CASCADE;

使主键及把主键作为外键的约束都不可用.

当主键被disabled时，主键索引从数据字典中被删除，然而一旦主键被enabled，主键索引立刻就重新创建

ALTER TABLE state_lookup ENABLE CONSTRAINT pk_state_lookup;

这个Alert table语句重建索引，设置主键为enabled，外键仍然不可用，每一个外键都要用下面语句enbled.

ALTER TABLE students ENABLE CONSTRAINT fk_students_state;

ALTER INDEX pk_state_lookup REBUILD;

Deferrable Option

 

 

 
NOVALIDATE

Novaludate允许不合法的数据被加载保存在数据表中，enabled约束仅用于后来插入的数据。这个选项可以用于数据仓库中，管理用于分析的历史数据。历史数据常常和当前商业规则冲突。

为加载不合法的历史数据，约束必须以deferrable选项定义，加载历史数据前，使约束不可用(disabled)，然后，用NOVALIDATE选项enabled约束，这样，历史数据保留在表中，而所有新插入的数据将被应用于主键约束规则：

CREATE TABLE parent

(parent_id   NUMBER(2),

 parent_desc VARCHAR2(10));

 

ALTER TABLE parent ADD CONSTRAINT pk_parent PRIMARY KEY

(parent_id) DEFERRABLE;

 

ALTER TABLE parent DISABLE CONSTRAINT pk_parent;

 

BEGIN

    INSERT INTO parent values (1,'A');

    INSERT INTO parent values (1,'B');

    INSERT INTO parent values (3,'C');

    INSERT INTO parent values (4,'D');

END;

 

ALTER TABLE parent ENABLE NOVALIDATE CONSTRAINT pk_parent;

 

 
异常处理

主键冲突oracle中定义的异常名称：DUP_VAL_ON_INDEX

 

CREATE OR REPLACE FUNCTION insert_parent

    (v_id NUMBER, v_desc VARCHAR2) RETURN BOOLEAN

IS

BEGIN

    INSERT INTO parent VALUES (v_id, v_desc);

    return TRUE;

EXCEPTION

    WHEN DUP_VAL_ON_INDEX THEN return FALSE;

END;