当前位置:Gxlcms > mysql > OracleDB约束概览_MySQL

OracleDB约束概览_MySQL

时间:2021-07-01 10:21:17 帮助过:23人阅读

bitsCN.com

Oracle DB约束概览

• 约束条件用于在表级别强制执行各种规则。

• 约束条件用于防止在存在相关性时删除表。

• 下列约束条件类型有效:

– NOT NULL :指定该列不能包含空值

– UNIQUE :定一个列或列组合的值对于表中的所有行必须是唯一的

– PRIMARY KEY :唯一地标识表中的每一行

– FOREIGN KEY :在该列和所引用表的列之间建立联系后强制实施引用完整性,这样其中一个表的值与另一个表中的值相匹配

– CHECK :指定必须为真的条件

Oracle Server 使用约束条件来防止将无效的数据输入到表中。

可以使用约束条件完成以下任务:

• 在表中插入、更新或删除某一行时,对表中的数据强制执行各种规则。必须满足约束条件,操作才会成功。

• 防止当某个表与其它表存在相关性时删除该表。

• 为Oracle 工具(例如Oracle Developer)提供规则。

约束条件准则

• 可以为约束条件命名,也可以由Oracle Server 使用SYS_Cn格式生成一个名称。

• 可采用以下任何一种方式创建约束条件:

– 创建表的同时创建约束条件

– 创建表以后

• 可以在列或表级别定义约束条件。

• 可以在数据字典中查看约束条件。

所有约束条件都存储在数据字典中。如果为约束条件指定了一个有意义的名称,则引用时较为容易。约束条件名称必须遵循标准对象命名规则,但是该名称不能与同一用户的另一对象名称相同。如果你没有对约束条件命名,Oracle Server 就会按照SYS_Cn格式生成一个名称,其中n是一个整数,这样约束条件名称是唯一的。

既可以在创建表的同时定义约束条件,也可以在创建表之后定义约束条件。

你可以在列级别或表级别定义约束条件。从功能上来说,表级别约束条件与列级别约束条件的作用是相同的。

定义约束条件

• 语法:

CREATE TABLE [schema.]table

(column datatype[DEFAULT expr]

[column_constraint],

...

[table_constraint][,...]);

• 列级别约束条件语法:

column[CONSTRAINT constraint_name] constraint_type,

• 表级别约束条件语法:

column,...

[CONSTRAINT constraint_name] constraint_type

(column, ...),

示例给出了在创建表时定义约束条件的语法。你可以在列级别或表级别创建约束条件。

定义列时会包括在列级别定义的约束条件。在表定义结束时定义表级别约束条件,必须在一组括号中引用应用了约束条件的列或列组合。这二者主要在语法上有所不同;此外从功能上来说,列级别约束条件和表级别约束条件的作用是相同的。

必须在列级别定义NOT NULL约束条件。

必须在表级别定义适用于多个列的约束条件。

在该语法中:

schema :与所有者的姓名相同

table :是表名称

DEFAULT expr :指定当INSERT语句中省略了值时所使用的默认值

column: 是列名称

datatype :是列的数据类型和长度

column_constraint:是作为列定义一部分的完整性约束条件

table_constraint :是作为表定义一部分的完整性约束条件

• 列级别约束条件示例:

CREATE TABLE employees(

employee_id NUMBER(6) CONSTRAINT emp_emp_id_pk PRIMARY KEY,

first_name VARCHAR2(20),

...);

• 表级别约束条件示例:

CREATE TABLE employees(

employee_id NUMBER(6),

first_name VARCHAR2(20),

...

job_id VARCHAR2(10) NOT NULL,

CONSTRAINT emp_emp_id_pk PRIMARY KEY (EMPLOYEE_ID));

通常会在创建表的同时创建约束条件。可以在创建表之后将约束条件添加到表,也可以临时禁用约束条件。

示例中都对EMPLOYEES表的EMPLOYEE_ID列创建了主键约束条件。

1.第一个示例使用列级别语法定义约束条件。

2.第二个示例使用表级别语法定义约束条件。

NOT NULL约束条件

NOT NULL约束条件可以确保某列不包含空值。默认情况下,没有NOT NULL约束条件的列可以包含空值。

必须在列级别定义NOT NULL约束条件。

在EMPLOYEES表中,EMPLOYEE_ID列继承了NOT NULL约束条件,因为该列已定义为主键。否则,在LAST_NAME、EMAIL、HIRE_DATE和JOB_ID列上强制实施NOT NULL约束条件。

UNIQUE约束条件

UNIQUE关键字完整性约束条件要求一列或一组列(键)中的每个值必须是唯一的,即在指定的列或一组列中,表的任意两行无重复值。UNIQUE关键字约束条件的定义中包括的列(或一组列)被称为唯一关键字。如果UNIQUE约束条件由多个列组成,则该组列被称为组合唯一关键字。

UNIQUE约束条件允许输入空值,除非你还为同一列定义了NOT NULL约束条件。实际上,因为空值被认为不等于任何值,所以任意数量的行都可以在没有NOT NULL约束条件的列中包括空值。一个列(或组合UNIQUE关键字的所有列)中的空值总是满足UNIQUE约束条件。

注:由于多个列上UNIQUE约束条件的搜索机制所致,在部分空值组合UNIQUE关键字约束条件的非空列中不能有相同的值。

可以在表级别或列级别定义:

CREATE TABLE employees(

employee_id NUMBER(6),

last_name VARCHAR2(25) NOT NULL,

email VARCHAR2(25),

salary NUMBER(8,2),

commission_pct NUMBER(2,2),

hire_date DATE NOT NULL,

...

CONSTRAINT emp_email_uk UNIQUE(email));

可以在列级别或表级别定义UNIQUE约束条件。如果要创建一个组合唯一关键字,则可在表级别定义该约束条件。如果不能使用单个属性来唯一地标识某一行,则需定义组合关键字。在这种情况下,可以创建由两个或两个以上列组成的唯一关键字,其组合值总是唯一的,可用于标识行。

示例中将UNIQUE约束条件应用于EMPLOYEES表的EMAIL列。该约束条件的名称为EMP_EMAIL_UK。

注:Oracle Server 通过对一个或多个唯一关键字列隐式创建一个唯一索引来强制实现UNIQUE约束条件。

PRIMARY KEY约束条件

PRIMARY KEY约束条件用于创建创建表的主键。只能为每一个表创建一个主键。

PRIMARY KEY约束条件是唯一标识表中每一行的一个列或一组列。此约束条件可以强制一个列或列组合是唯一的,还可以确保作为主键一部分的列不包含空值。

注:因为唯一性是主键约束条件定义的一部分,所以Oracle Server 通过对一个或多个主键列隐式创建一个唯一索引来强制实现唯一性。

FOREIGN KEY约束条件

FOREIGN KEY(或引用完整性)约束条件指定一个列或列组合作为外键,并建立与同一表或不同表中主键或唯一关键字的关系。

示例中,DEPARTMENT_ID已被定义为EMPLOYEES表(相关表或子表)中的外键,它引用DEPARTMENTS表(被引用表或父表)的DEPARTMENT_ID列。

准则

• 外键值必须与父表中的现有值相匹配,或为NULL。

• 外键取决于数据值,外键是纯逻辑指针,而不是物理指针。

可以在表级别或列级别定义:

CREATE TABLE employees(

employee_id NUMBER(6),

last_name VARCHAR2(25) NOT NULL,

email VARCHAR2(25),

salary NUMBER(8,2),

commission_pct NUMBER(2,2),

hire_date DATE NOT NULL,

...

department_id NUMBER(4),

CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)

REFERENCES departments(department_id),

CONSTRAINT emp_email_uk UNIQUE(email));

可以在列级别或表级别定义FOREIGN KEY约束条件。必须使用表级别定义来创建组合外键。

示例中使用表级别语法,对EMPLOYEES表的DEPARTMENT_ID列定义一个FOREIGN KEY约束条件。该约束条件的名称为EMP_DEPT_FK。

如果约束条件只是针对单个列,则也可以在列级别定义外键。语法上的不同之处在于没有出现关键字FOREIGN KEY。例如:

CREATE TABLE employees

(...

department_id NUMBER(4) CONSTRAINT emp_deptid_fk

REFERENCES departments(department_id),

...

)

FOREIGN KEY约束条件:关键字

外键是在子表中定义的,而包含被引用列的表是父表。外键是使用以下关键字的组合定义的:

• FOREIGN KEY用于在表约束条件级别定义子表中的列。

• REFERENCES用于标识父表中的表和列。

• ON DELETE CASCADE指出在删除父表中的行时,还删除子表中的相关行。

• ON DELETE SET NULL指出在删除父表中的行时,将外键值设为空值。

默认行为被称为限制规则,该规则可禁止更新或禁止删除被引用的数据。

在没有ON DELETE CASCADE或ON DELETE SET NULL选项时,如果在子表中引用父表中的一行,则不能删除该行。

CHECK约束条件

• 定义每行都必须满足的一个条件

• 以下表达式是不允许的:

– 引用CURRVAL、NEXTVAL、LEVEL和ROWNUM假列的表达式

– 调用SYSDATE、UID、USER和USERENV函数的表达式

– 引用其它行中的其它值的查询

..., salary NUMBER(2)

CONSTRAINT emp_salary_min CHECK (salary> 0),...

CHECK约束条件用于定义每行都必须满足的一个条件。该条件可以使用与查询条件相同的结构,但是以下项除外:

• 引用CURRVAL、NEXTVAL、LEVEL和ROWNUM假列的表达式

• 调用SYSDATE、UID、USER和USERENV函数的表达式

• 引用其它行中的其它值的查询

一个列可以有多个CHECK约束条件,这些约束条件将在其定义中引用该列。可以按需要,对一个列定义任意数量的CHECK约束条件。

可以在列级别或表级别定义CHECK约束条件。

CREATE TABLE employees

(...

salary NUMBER(8,2) CONSTRAINT emp_salary_min CHECK (salary > 0),

...

示例中显示用于创建HR方案中EMPLOYEES表的语句

CREATE TABLE employees

( employee_id NUMBER(6)

CONSTRAINT emp_employee_id PRIMARY KEY

, first_name VARCHAR2(20)

, last_name VARCHAR2(25)

CONSTRAINT emp_last_name_nn NOT NULL

, email VARCHAR2(25)

CONSTRAINT emp_email_nn NOT NULL

CONSTRAINT emp_email_uk UNIQUE

, phone_number VARCHAR2(20)

, hire_date DATE

CONSTRAINT emp_hire_date_nn NOT NULL

, job_id VARCHAR2(10)

CONSTRAINT emp_job_nn NOT NULL

, salary NUMBER(8,2)

CONSTRAINT emp_salary_ck CHECK (salary>0)

, commission_pct NUMBER(2,2)

, manager_id NUMBER(6)

CONSTRAINT emp_manager_fk REFERENCES

employees (employee_id)

, department_id NUMBER(4)

CONSTRAINT emp_dept_fk REFERENCES

departments (department_id));

通过以下查询表中哪列有哪些约束,上面创建表的语句对应下面的约束。

hr@TEST0924> l

1 select a.owner,a.TABLE_NAME,b.COLUMN_NAME,a.CONSTRAINT_NAME,a.CONSTRAINT_TYPE,a.SEARCH_CONDITION

2 from USER_CONSTRAINTS a,USER_CONS_COLUMNS b

3* where a.CONSTRAINT_NAME=b.CONSTRAINT_NAME and A.table_name='EMPLOYEES'

hr@TEST0924> /

OWNER TABLE_NAME COLUMN_NAM CONSTRAINT_NAME C SEARCH_CONDITION

---------- ------------------------------ ---------- ------------------------------ - --------------------

HR EMPLOYEES SALARY EMP_SALARY_MIN C salary > 0

HR EMPLOYEES EMAIL EMP_EMAIL_UK U

HR EMPLOYEES EMPLOYEE_ID EMP_EMP_ID_PK P

HR EMPLOYEES DEPARTMENT_ID EMP_DEPT_FK R

HR EMPLOYEES JOB_ID EMP_JOB_FK R

HR EMPLOYEES MANAGER_ID EMP_MANAGER_FK R

HR EMPLOYEES LAST_NAME EMP_LAST_NAME_NN C "LAST_NAME" IS NOT NULL

HR EMPLOYEES EMAIL EMP_EMAIL_NN C "EMAIL" IS NOT NULL

HR EMPLOYEES HIRE_DATE EMP_HIRE_DATE_NN C "HIRE_DATE" IS NOT NULL

HR EMPLOYEES JOB_ID EMP_JOB_NN C "JOB_ID" IS NOT NULL

10 rows selected.

违反约束条件

当对列设置了约束条件后,如果试图违反约束条件规则,则会返回一条错误。例如,如果尝试更新一条记录,但该记录中的值受完整性约束条件所约束,则会返回一条错误。示例中,因为父表DEPARTMENTS中不存在部门55,所以你会收到违例ORA-02291“parent key not found(未找到父关键字)”。

hr@TEST0924> update employees set department_id=55 where department_id=110;

update employees set department_id=55 where department_id=110

*

ERROR at line 1:

ORA-02291: integrity constraint (HR.EMP_DEPT_FK) violated - parent key not found

如果某行中包含用作其它表中的外键的主键,则不能删除该行。

hr@TEST0924> delete from departments where department_id=60;

delete from departments where department_id=60

*

ERROR at line 1:

ORA-02292: integrity constraint (HR.EMP_DEPT_FK) violated - child record found

如果试图删除一条记录,但该记录中的值受完整性约束条件所约束,则会返回一条错误。

示例中试图从DEPARTMENTS表中删除部门60,但此操作导致一个错误,因为该部门编号已用作EMPLOYEES表中的外键。如果试图删除具有子记录的父记录,则会收到违例ORA-02292“child record found(存在子记录)”。

由于部门210 中没有任何雇员,因此下面的语句有效:

hr@TEST0924> delete from departments where department_id=210;

1 row deleted.

bitsCN.com

人气教程排行