Oracle数据完整性和约束性

逃离我推掉我的手 2021-09-14 06:36 362阅读 0赞

数据库不仅仅是存储数据，它还必须保证所有存储数据的正确性，因为只有正确的数据才能提供有价值的信息。如果数据不准确或不一致，那么该数据的完整性就可能受到破坏，从而给数据库本身的可靠性带来问题。为了维护数据库中数据的完整性，在创建表时常常需要定义一些约束。约束可以限制列的取值范围，强制列的取值来自合理的范围。在Oracle 11g系统中，约束的类型包括非空约束、主键约束、唯一约束、外键约束、检查约束和默认约束。

创建BOOKS（图书）表，用于实例的操作使用。

CREATE TABLE BOOKS
    (
      BOOKNO NUMBER(4) NOT NULL,           --图书编号
      BOOKNAME VARCHAR2(20),               --图书名称
      AUTHOR VARCHAR2(10),                 --作者   
      SALEPRICE NUMBER(9,2),               --定价     
      PUBLISHERNO VARCHAR2(4) NOT NULL,    --出版社编号
      PUBLISHDATE DATE,                    --出版日期
      ISBN VARCHAR2(20) NOT NULL          --ISBN
    );

# 1、非空约束 #

非空约约束就是限制必须为某个列提供值。空值（NULL）是不存在值，它既不是数字0，也不是空字符串，而是不存在、未知的情况。

**【实例】**为BOOKS（图书）表中的BOOKNAME字段设置非空约束。

ALTER TABLE BOOKS MODIFY BOOKNAME NOT NULL;

**【实例】**删除BOOKS（图书）表中关于BOOKNAME列的非空约束。

ALTER TABLE BOOKS MODIFY BOOKNAME NULL;

# 2、主键约束 #

主键约束用于唯一地标识表中的每一行记录。在一个表中，最多只能有一个主键约束，主键约束既可以由一个列组成，也可以由两个或两个以上的列组成（这种称为联合主键）。对于表中的每一行数据，主键约束列都是不同的，主键约束同时也具有非空约束的特性。

如果主键约束由一列组成时，该主键约束被称为行级约束。如果主键约束由两个或两个以上的列组成时，则该主键约束被称为表级约束。若要设置某个或某些列为主键约束，通常使用CONSTRAINT…PRIMARY KEY语句来定义。

**【实例】**使用ALTER TABLE…ADD语句为BOOKS（图书）表增加主键约束。

ALTER TABLE BOOKS ADD CONSTRAINT BOOKS_PK PRIMARY KEY(BOOKNO);
    
    --或者
    ALTER TABLE BOOKS ADD PRIMARY KEY(BOOKNO);

说明：如果构建主键约束的列有多个（即创建表级约束），则多个列之间使用英文输入法下的逗号分隔。

# 3、唯一性约束 #

唯一性（UNIQUE）约束强调所在的列不允许有相同的值。但是，它的定义要比主键约束弱，即它所在的列允许空值（但主键约束列是不允许为空值的）。唯一性约束的主要作用是在保证除主键列外，其它列值的唯一性。

在一个表中，根据实际情况可能有多个列的数据都不允许存在相同值。例如，各种“会员表”的QQ、Email等列的值是不允许重复的（但用户可能不提供，这样就必须允许为空值），但是由于在一个表中最多只能由一个主键约束存在，那么如何解决这种多个列都不允许重复数据存在的问题呢？这就是唯一性约束的作用。若要设置某个列为UNIQUE约束，通常使用CONSTRAINT…UNIQUE标记该列。

**【实例】**为BOOKS（图书）表的ISBN列添加唯一约束。

ALTER TABLE BOOKS ADD CONSTRAINT ISBN_UK UNIQUE(ISBN);

**说明：**如果要为现有表的多个列同时添加UNIQUE约束，则在括号内使用逗号分隔多个列。

**【实例】**删除BOOKS（图书）表的ISBN列的唯一约束。

ALTER TABLE BOOKS DROP CONSTRAINT ISBN_UK;

# 4、外键约束 #

外键约束比较复杂，一般的外键约束会使用两个表进行关联（当然也存在同一个表自连接的情况）。外键是指“当前表”（即外键表）引用“另外一个表”（即被引用表）的某个列或某几个列，而“另外一个表”中被引用的列必须具有主键约束或者唯一性约束。在“另外一个表”中，被引用列中不存在的数据不能出现在“当前表”对应的列中。一般情况下，当删除被引用表中的数据时，该数据也不能出现在外键表的外键列中。如果外键列存储了被引用表中将要被删除的数据，那么对被引用表的删除操作将失败。

最典型的外键约束时HR模式中的EMPLOYEES和DEPARTMENT表，在该外键约束中，外键表EMPLOYEES中的外键列DEPARTMENT\_ID将引用被引用表DEPARTMENTS中的DEMPARTMENT\_ID列，而该列也是DEPARTMENTS表的主键。

**【实例】**在HR模式中，创建一个新表EMPLOYEES\_TEMP（该表的结构复制自EMPLOYEES），并为其添加一个与DEPARTMENTS表关联的外键约束。

CREATE TABLE EMPLOYEES_TEMP
    AS SELECT * FROM EMPLOYEES
    WHERE DEPARTMENT_ID=30;
    
    ALTER TABLE EMPLOYEES_TEMP
    ADD CONSTRAINT TEMP_DEPARTID_FK
    FOREIGN KEY(DEPARTMENT_ID)
    REFERENCES DEPARTMENTS(DEPARTMENT_ID);

# 5、检查约束 #

检查约束用来指定某列的可取值的范围，它通过限制输入到列中的值来强制域的完整性。

**【实例】**为BOOKS（图书）表的SALEPRICE字段添加检查约束，定价必须大于0且小于1200。

ALTER TABLE BOOKS ADD CONSTRAINT SALEPRICE_CK CHECK(SALEPRICE>0 AND SALEPRICE<1200);

# 6、默认约束 #

**【实例】**为BOOKS（图书）表的PUBLISHERNO列添加默认约束。

ALTER TABLE BOOKS MODIFY PUBLISHERNO DEFAULT '1001';

# 7、删除约束 #

如果不再需要某个约束时，则可以将其删除。可以使用带DROP CONSTRAINT子句的ALTER TABLE语句删除约束。删除约束与禁用约束不同，禁用的约束时可以激活的，但是删除的约束在表中就完全消失了。使用ALTER TABLE语句删除约束的语法格式如下：

ALTER TABLE TABLE\_NAME DROP CONSTRAINTCON\_NAME;

TABLE\_NAME：表示要删除约束的表名称。

CON\_NAME：表示要删除的约束名称。

**【实例】**删除BOOKS（图书）表的SALEPRICE字段的检查约束。

ALTER TABLE BOOKS DROP CONSTRAINT SALEPRICE_CK;

# 8、创建数据表同时创建约束 #

**【实例】**创建STUDENT\_INFO（学生信息）表同时创建约束。

--创建“学生信息”数据表
    CREATE TABLE STUDENT_INFO
    (
      STU_ID INT PRIMARY KEY,                             --学号,并设置主键约束
      STU_NAME VARCHAR2(8) NOT NULL,                      --姓名,并设置非空约束
      EMAIL VARCHAR2(20) CONSTRAINT EMAIL_UK UNIQUE,      --邮箱,并设置唯一性约束
      SEX CHAR(2) DEFAULT '女',                           --性别，并设置默认约束
      AGE INT CONSTRAINT AGE_CK CHECK(AGE>0 AND AGE<120)  --年龄，并设置检查约束
    );