Oracle学习笔记 _09_数据完整性

Oracle学习 第9天

            —— 维护数据的完整性

                数据完整性用于确保数据库数据遵从一定的商业逻辑规则。

                在Oracle数据库中，数据完整性可以使用约束、触发器、应用程序（过程、函数）三种方法实现。

                约束因其易于维护且性能良好，所以作为数据完整性的首选。

约束

 

 

                约束用于确保数据库数据满足特定的规则。

                        在Oracle中，约束包括以下五种：

                                        NOT NULL         ：     非空    ；

                                        UNIQUE             ：     唯一    ；

                                        PRIMARY KEY    ：     主键    ；

                                        FOREIGN KEY    ：      外键   ；

                                        CHECK               ：      检查   ；   

                ①：NOT NULL(非空)

                                用于指定某一列的值，不允许为null值。

CREATE TABLE EMP_TEST(empno number NOT NULL, name varchar2(20) NOT NULL, sex varchar2(2));
SELECT * FROM emp_test;

INSERT INTO emp_test VALUES(123,'test01','男');
SELECT * FROM emp_test;

INSERT INTO emp_test VALUES(124,'test01',NULL);

INSERT INTO emp_test VALUES(124,Null,'女');

★ 错误提示：无法将NULL插入（方案名.表名.字段名）

 

                ②：UNIQUE（唯一）

                                用于指定某列的值，不能重复。但是，可以为空。

CREATE TABLE EMP_TEST2(empno number NOT NULL, name varchar2(20) UNIQUE, sex varchar2(2));
INSERT INTO emp_test2 VALUES(123,'test01','男');
SELECT * FROM emp_test2;

INSERT INTO emp_test2 VALUES(333,'test01','女');

★ 错误提示：违反唯一约束条件（方案名.表名.字段名）

INSERT INTO emp_test2 VALUES(12,NULL,'女');
INSERT INTO emp_test2 VALUES(13,NULL,'女');
SELECT * FROM emp_test2;

★ 注意：Oracle中UNIQUE 可以为空，且可以多个为空。但其他数据库不一定可以多行为空

 

                ③：PRIMARY KEY（主键）

                                唯一标识表行的数据。主键值唯一且不能为空，并且一张表中都应该有且只能有一个主键，但一个主键不一定是一个字段，由多个字段联合组成的主键称为复合主键。主键所在列会自动创建索引。

 

                ④：FOREIGN KEY（外键）

                                定义主表和从表之间的关系。外键约束定义在从表上，主表则必须拥有唯一约束或主键约束（主键约束本身就拥有唯一约束）；当定义外键约束后，要求外键列数据必须在主表的主键列存在或者唯NULL。

★ 注意：  外键定义在从表中；

                指向主表的主键或者其他具有唯一约束的列；

                外键列和主键类的数据类类型要一致。

                建表要先建主表，后建从表；删表要先删从表，后删主表；

                外键列的值可以为空，即暂时不对应主表；但如果非空，则必须在主键列中存在，否则会添加失败

CREATE TABLE USERRIGHT (RIGHTNO PRIMARY KEY, CREATE_RIGHT VARCHAR2(1)); --先建主表
CREATE TABLE USERINFO (USERID NUMBER PRIMARY KEY, NAME VARCHAR2(30) NOT NULL, RIGHTNO NUMBER REFERENCES USERRIGHT(RIGHTNO)); -- 如果先建从表，会建表失败

 

                ⑤：CHECK（检查）

 

                                用于强制数据必须满足的条件。

举例说明：          

CREATE TABLE EMP_TEST3(empno number NOT NULL, name varchar2(20), age NUMBER CHECK(age > 0 AND age < 120));

INSERT INTO emp_test3 VALUES(1,'xiaoming',50);
INSERT INTO emp_test3 VALUES(2,'xiaohong',121);
SELECT * FROM emp_test3;

可见，只有第一条数据插入成功，第二条数据因121 > 120而违反了检查约束条件而插入失败。

 

 

修改约束

                修改表的约束使用 ALTER 和 ADD 关键字。

                增加NOT NULL约束条件使用 MODIFY。

 

ALTER TABLE table_name MODIFY column NOT NULL;
ALTER TABLE table_name ADD CONSTRAINT 约束名 UNIQUE(column); -- 约束名可以是一个自定义名称ALTER TABLE table_name ADD CONSTRAINT 约束名 check(column IN('an name'));

 

列级定义和表级定义

                像上面在创建表时，直接在列的后面指定约束的写法，我们称之为列级定义。

                创建表时，先创建完表的所有列，然后统一的去分别说明约束条件的写法，我们称之为表级定义。

--表级定义示例
CREATE TABLE test (
     字段1 字段类型, 
     字段2 字段类型,
-- 在最后一个字段后，加上逗号，开始定义约束
     CONSTRAINT 约束名 PRIMARY KEY(字段名)
     UNIQUE(字段名)
     NOT NULL(字段名)
     CHECK(字段条件)
)