精华内容
下载资源
问答
  • 主键:一个数据库的一张表有且仅有一个主键,而且主键不能重复 唯一索引:一个数据库的一张表上唯一索引可以有多个,只是所在唯一索引上的值不能重复,这一点和主键一样 下面我们创建一个有主键有唯一...

    首先说一下主键和唯一索引的区别

    主键:一个数据库的一张表有且仅有一个主键,而且主键不能重复

    唯一索引:一个数据库的一张表上唯一索引可以有多个,只是所在唯一索引上的值不能重复,这一点和主键一样


     

    下面我们创建一个有主键有唯一索引的,并且主键是自动增长

    1 create table demo
    2  (id int primary key auto_increment, 
    3 name char(10) unique key, 
    4 age int);
    View Code

    接下来我们插入数据看看


     

    转载于:https://www.cnblogs.com/xiaostudy/p/9373982.html

    展开全文
  • Oracle数据库设计——定义约束 主键 [code] 声明约束 主键(PRIMARY KEY) 一张不一定有主键,但大多数都创建...因此,在STUDENTS中每一行只能确定一个且仅有一个学生。举例来说,一个处理学生学费清单...
    Oracle数据库设计——定义约束 主键 
    [code]
    声明约束
    主键(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;

    [/code]
    展开全文
  • Oracle数据库设计——定义约束 主键1 ...主键(PRIMARY KEY) ... 一张不一定有主键,但大多数都创建了主键主键值必须唯一并且组成主键的各...因此,在STUDENTS中每一行只能确定一个且仅有一个学生...

    声明约束

    主键(PRIMARY KEY)

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

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

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

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

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

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

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

     

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

        事实上,一个主键的表存储在数据字典内,我们看一下数据字典视图USER_TAB_COLUMNS,它是一个查看一个表内字段名的字典视图。我们也有约束的视图,是USER_CONSTRAINTSUSER_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 KeyUniqueForeign Key,或Check约束

    ALTER TABLE temp ADD CONSTRAINT pk_temp PRIMARY KEY

    5)、跟在约束类型后面有几种特殊选项,Primary KeyUnique约束,需要指定约束的列;Check约束需要指定约束规则。

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

    6)Primary KeyUnique约束应当指定索引的表空间名,使用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中来查看

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

    posted on 2010-10-15 14:14 老杨~ 阅读(...) 评论(...) 编辑 收藏

    转载于:https://www.cnblogs.com/qiangqiang/archive/2010/10/15/1852159.html

    展开全文
  • 声明约束 主键(PRIMARY KEY) 一张不一定有主键,但大多数...因此,在STUDENTS中每一行只能确定一个且仅有一个学生。举例来说,一个处理学生学费清单的应用中,每个学生只能有一张且仅有一张学费单 除了商...
    声明约束
    主键(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_lookupDISABLE 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 BOOLEANISBEGIN INSERT INTO parent VALUES (v_id, v_desc); return TRUE;EXCEPTION WHEN DUP_VAL_ON_INDEX THEN return FALSE;END;

    转自:csdn
    作者:steven
    展开全文
  • 数据库设计的范式大纲 第一范式: ...对于表中的每一行,必须...一个主键可以由一个或更多的组成唯一值的列组成。一旦创建,主键无法改变,外键关联一个表的主键。主外键关联意味着一对多的关系. 第三范式: ...
  • MySQL主键约束、非空约束、唯一约束、外键约束 概念:对表中的数据进行约束(限定),保证数据的正确性、有效性和完整性 ...一张表仅有一个字段为主键 主键中记录的唯一标识 在创建时...
  • mysql数据库的约束

    2020-07-20 19:46:18
    注意事项:一个表有且仅只有一个主键约束 1.1 在创建时创建主键约束 格式一: create table 表明( 列名1 数据类型 primary key [auto_increment], -- 设置为主键且自增 列名2 数据类型, 列名3 数
  • 第一范式: 对于中的每一行,必须仅仅有唯一的行值.在一行中的每一列仅有唯一的值并且具有原子性...主键必须有唯 一性的元素,一个主键可以由一个或更多的组成唯一值的列组成。一旦创建,主键无法改变,外键关联...
  • 三大范式 第一范式(1NF):表中的每一个字段只能存放单一的值,而且每条记录都要能利用一个惟一的主键来加以识别。...一个表符合第二范式,当且仅当: 它符合第一范式 所有的非主键字段都依赖于主键 第三范式(3...
  • 第一范式: 对于中的每一行,必须仅仅有唯一的行值.在一行中的每一列仅有唯一的值并且具有原子性. 第一范式是通过把重复的组放...主键必须有唯一性的元素,一个主键可以由一个或更多的组成唯一值的列组成。一旦 ...
  • 第一范式: 对于中的每一行,必须仅仅有唯一的行值.在一行中的每一列仅有唯一的值并且具有原子性....主键必须有唯一性的元素,一个主键可以由一个或更多的组成唯一值的列组成。一旦创建,主键无法改变,外键关联一
  • 数据库设计中的五个范式 第一范式: 对于中的每一行,必须仅仅有唯一的行值.在一行中的每一列仅有唯一的值并且具有原子性. 第一范式是通过把重复的组放到每个独立...主键必须有唯一性的元素,一个主键可...
  • mysql-回查询是什么

    2020-09-26 08:36:30
    MySQL规定,在使用InnoDB存储引擎的时候,必须且仅有一个聚集索引,非聚集索引也就是普通索引就看自己设置的有多少个了 聚集索引和非聚集索引的区别: 1.聚集索引中的非叶子节点存储的是主键,非聚集索引的非...
  • 数据库的常用命令2

    2020-07-14 15:22:00
    每张都会存在主键,并且有且仅有一个主键,主键是这张表唯一标识,可以用来代表中的所有数据,主键不可以重复,并且主键可以是多个字段,多个字段形成的主键称为联合主键。 一般情况下,我们创建的中通常将ID...
  • 第一范式: 对于表中的每一行,必须仅仅唯一的行值....主键必须唯一性的元素,一个主键可以由一个或更多的组成唯一值的列组成。一旦创建,主键无法改变,外键关联一个表的主键。主外键关联意味着一对多的关系.
  • InnoDB---约束

    千次阅读 2019-06-25 17:54:31
    要求每个关系(有且仅有一个主键,每一个主键值必须唯一,而且不允许为“空”(NULL)或重复。 域完整性:保证数据每列的值满足特定的条件。 指数据库中的列必须满足某种特定的数据类型或约束。其中约束又...
  • InooDB与MyISAM

    2020-12-15 00:52:00
    存储引擎 密集索引与稀疏索引 ...inooDB中:每个表必须有且仅有一个密集索引 选取规则1,如果一个主键被定义,该主键则作为一个密集索引 选取规则2.如果没有主键,则表中第一个唯一非空索引作为密集索引 选
  • 数据库六大范式

    2018-11-14 16:14:59
    主键必须唯一性的元素,一个主键可以由一个或更多的组成唯一值的列组成。一旦创建,主键无法改变,外键关联一个表的主键。主外键关联意味着一对多的关系. 第三范式: 前提必须遵循2NF,要求非主键列互不依赖,消除...
  • 第一范式: 对于中的每一行,必须仅仅有唯一的行值.在一行中的每一列仅有唯一的值并且具有原子性。...主键必须有唯一性的元素,一个主键可以由一个或更多的组成唯一值的列组成。一旦创建,主键无法改变,外键...
  • 第一范式: 对于中的每一行,必须仅仅有唯一的行值.在一行中的每一列仅有唯一的值并且具有原子性....主键必须有唯一性的元素,一个主键可以由一个或更多的组成唯一值的列组成。一旦创建,主键
  • 第一范式: 对于表中的...主键必须唯一性的元素,一个主键可以由一个或更多的组成唯一值的列组成。一旦创建,主键无法改变,外键关联一个表的主键。主外键关联意味着一对多的关系. 第三范式: 第三范式要求非主键列互
  • 数据仓库设计

    2019-02-20 08:51:59
    第二范式:在满足第一范式的情况下,每一行有且仅有一个主键,表示该条数据的唯一 第三范式:在满足第二范式的情况下,每一行的每一列都不可出现其他中非主键列。 星型模型: 星型模型是数据集市维度建模中推荐的...
  • 【软考】数据库范式

    2008-09-26 17:20:00
    主键必须唯一性的元素,一个主键可以由一个或更多的组成唯一值的列组成。一旦创建,主键无法改变,外键关联一个表的主键。主外键关联意味着一对多的关系. 第三范式 第三范式要求非主键列互不依赖. 第四范式 ....
  • B+树存储的索引叶子节点,带有表数据行(每张表有且仅有一个聚簇索引),一般会根据主键id构建聚集索引,如果没有主键,那么会根据非空唯一的列创建聚集索引,如果连非空唯一的列都没有的话,那么会使用一个隐藏列...
  • 数据库设计六大范式

    2008-08-26 12:33:08
    第一范式: 对于中的每一行,必须仅仅有唯一的行值.在一行中的每一列仅有唯一的值并且具有原子性. 第二范式: 要求非主键列是主键的子集,非主键列活动必须...主键必须有唯一性的元素,一个主键可以由一个或更多...

空空如也

空空如也

1 2 3 4
收藏数 73
精华内容 29
关键字:

一个表有且仅有一个主键