Oracle中数据库约束学习constraints

2022-11-30 15:54:38

在数据库中使用约束(constraints)是为了在该数据库中实施所谓的“业务规则”其实就是防止非法信息进入数据库,满足管理员和应用开发人员所定义的规则集。
ORACLE使用完整性约束(integrity constraints)防止不合法的数据写入数据库，管理员和开发人员可以定义完整性规则，增强商业规则，限制数据表中的数据。如果一个DML语句执行的任何结果破坏了完整性约束，ORACLE就会回滚语句，返回错误信息。
约束是通过使用CREATE TABLE或ALTER TABLE语句生成的。(建立表时或者表建立后修改都可)如果相关的约束定义在单列上，可以在列这一级指定约束的定义；多列约束必须定义在数据表级，相关的列要在括号中指定，用逗号分隔。如果没有为约束提供一个名字，那么ORACLE会分配一个系统生成的唯一名字，以SYS_开头，你可以使用关键字CONSTRAINTS后面跟随相关的约束名字来为约束指定名字。

约束定义存储在数据字典中,查询USER_CONSTRAINTS可以获得相关信息。

select * from user_constraints;    //查询当前用户的约束
select * from dba_constraints;    //查询所有用户的约束

ORACLE支持五种类型的完整性约束：
NOT NULL (非空)--防止NULL值进入指定的列，在单列基础上定义，默认情况下，ORACLE允许在任何列中有NULL值。
CHECK (检查)--检查在约束中指定的条件是否得到了满足。
UNIQUE (唯一)--保证在指定的列中没有重复值。在该表中每一个值或者每一组值都将是唯一的。
PRIMARY KEY (主键)--用来唯一的标识出表的每一行，并且防止出现NULL值，一个表只能有一个主键约束。
POREIGN KEY (外部键)--通过使用公共列在表之间建立一种父子(parent-child)关系，在表上定义的外部键可以指向主键或者其他表的唯一键。

几种约束并案例：

1.主键约束：
要对一个列加主键约束的话，这列就必须要满足的条件就是分空
因为主键约束：就是对一个列进行了约束，约束为（非空、不重复）
以下是代码要对一个列加主键，列名为id,表名为emp

格式为：
alter table 表格名称 add constraint 约束名称增加的约束类型（列名）

例子：

 alter table emp add constraint ppp primary key (id)

2.check约束：
就是给一列的数据进行了限制
比方说，年龄列的数据都要大于20的
表名（emp）列名（age）

格式：
alter table 表名称 add constraint 约束名称增加的约束类型（列名）

例子：

 alter table emp add constraint xxx check(age>20)

3.unique约束：
这样的约束就是给列的数据追加的不重复的约束类型

格式：
alter table 表名 add constraint 约束名称约束类型（列名）
比方说可以给ename列加个unique，让ename列的数据不重复
例子：

alter table emp add constraint qwe unique(ename);

4.默认约束：
意思很简单就是让此列的数据默认为一定的数据

格式：
alter table 表名称 add constraint 约束名称约束类型默认值） for 列名

比方说：emp表中的gongzi列默认为10000

alter table emp add constraint jfsd default 10000 for gongzi

5.外键约束：
这个有点难理解了，外键其实就是引用
因为主键实现了实体的完整性，
外键实现了引用的完整性，
应用完整性规定，所引用的数据必须存在！

其实就是个引用，
比方说一个表名称叫dept 里面有2列数据一列是ID一列是ENAME
id：表示产品的编号
ename：表示产品的名称

另外一个表格名称是emp 里面有2列数据，一列是ID 一列是DID
id:表示用户号
did：表示购买的产品号

要让emp表中的did列去引用dept表中的id

可以用下面的方法

格式：
alter table 表名 add constraint 约束名称约束类型 (列名) references 被引用的表名称（列名）

例子：

alter table emp add constraint jfkdsj foreign key (did) references dept (id)

学习约束练习：

定义约束
CREATE TABLE [schema.]table
(column datatype [DEFAULT expr]
[column_constraint],
...
[table_constraint][,...]);

CREATE TABLE employees 
            (employee_id NUMBER(6), 
              first_name   VARCHAR2(20), 
              ... 
              job_id       VARCHAR2(10) NOT NULL, 
              CONSTRAINTS emp_emp_id_pk PRIMARY KEY (EMPLOYEE_ID));

列级的约束定义
column [CONSTRAINT constraint_name] constraint_type,
表级约束的定义
column,..
[CONSTRAINT constraint_name] constraint_type (column,...)

NOT NULL约束
只能定义在列级,不能定义在表级.

CREATE TABLE employees 
            (employee_id NUMBER(6), 
              last_name   VARCHAR2(25) NOT NULL, //没有指定名字,将用系统SYS_命名 
              salary       NUMBER(8,2), 
              commission_pct NUMBER(2,2), 
              hire_date   DATE CONSTRAINT emp_hire_date_nn NOT NULL);  //用户定义了名字

UNIQUE约束
用来保护一个表中的一个或者多个列没有任何两行在收到保护的列中具有重复的数据.ORACLE在唯一键列上自动生成一个唯一索引以实现唯一性

CREATE TABLE employees 
            (employee_id NUMBER(6), 
              last_name   VARCHAR2(25) NOT NULL, 
              salary       NUMBER(8,2), 
              commission_pct NUMBER(2,2), 
              hire_date DATE NOT NULL, 
              CONSTRAINT emp_email_uk UNIQUE(email));

PRIMARY KEY约束
唯一键的所有特征都适用于主键约束,只是在主键列中不允许有NULL值.一个表只能有一个主键.

CREATE TABLE departments 
            (department_id NUMBER(4), 
              department_name VARCHAR2(30) CONSTRAINT dept_name_nn NOT NULL, 
              manager_id     NUMBER(6), 
              location_id       NUMBER(4), 
              CONSTRAINT dept_id_pk PRIMARY KEY(department_id));

FOREIGN KEY 约束
用来保护一个表中的一个或者多个列,它会通过一个主键主键或者唯一键保证对于每个非NULL值在数据库的其他地方都有一个数据可用.这个外部键就是在生成此约束的表(子表)中的一个或多个列,在父级表和子表中,相关列的数据类型必须匹配.外部键列和引用键(reference key)列可以位于相同的表中(自引用完整性约束).

CREATE TABLE employees 
            (employee_id NUMBER(6), 
              last_name   VARCHAR2(25) NOT NULL, 
              salary       NUMBER(8,2), 
              commission_pct NUMBER(2,2), 
              hire_date DATE NOT NULL, 
              deparment_id NUMBER(4), 
              CONSTRAINT emp_dept_fk FOREIGN KEY(department_id) REFERENCES departments(department_id), 
              CONSTRAINT emp_email_uk UNIQUE(email));

上例中是在表级定义外部键约束,如果在列级定义,不同的是:

CREATE TABLE employees 
            (..., 
              department_id NUMBER(4) CONSTRAINT emp_deptid_fk REFERENCES departments(department_id), 
            ...); 
            //没有关键字FOREIGN KEY

FOREIGN KEY约束还有两个关键字是
ON DELETE CASCADE --当删除所引用的父表记录时,删除子表中相关的记录
ON DELETE SET NULL--与上面不同的是删除时,转换子表中相关记录为NULL值
默认情况下,如果没有指定以上两个中任一,则父表中被引用的记录将不能被删除。

CHECK 约束
[CONSTRAINT ] CHECK (
这里CHECK子句中的CONDITION应该求值为一个布尔值结果,并且可以引用相同行中其他列的值;不能包含子查询,序列,环境函数(SYSDATE,UID,USER,USERENV)和伪列(ROWNUM,LEVEL,CURRVAL,NEXTVAL),一个列上可以定义多个CHECK约束,如果所定义的条件为FALSE,则语句将回滚.

CREATE TABLE employees 
            (..., 
              salary NUMBER(8,2) CONSTRAINT emp_salary_min CHECK (salary>0), 
              ...);

添加约束

ALTER TABLE employees 
ADD CONSTRAINT emp_manager_fk FOREIGN KEY(manager_id) REFERENCES employees(employee_id);

删除约束

ALTER TABLE employees 
DROP CONSTRAINT emp_manager_fk; 
ALTER TABLE departments 
DROP PRIMARY KEY CASCADE; //由于departments被employees.department_id引用了

对于NOT NULL约束,用ALTER TABLE MODIFY子句来删除

ALTER TABLE employees MODIFY last_name NULL;

关闭约束

ALTER TABLE employees 
DISABLE CONSTRAINT emp_emp_id_pk CASCADE;  //如果没有被引用则不需CASCADE关键字

当你生成一个约束时,约束自动打开(除非你指定了DISABLE子句0,当用DISABLE关闭UNIQUE或者PRIMARY KEY约束时,ORACLE会自动删除相关的唯一索引,再次打开时,ORACLE又会自动建立的.

打开约束

ALTER TABLE employees 
ENABLE CONSTRAINT emp_emp_id_pk; //注意,打开一个先前关闭的被引用的主键约束,并不能自动打开相关的外部键约束

可以从USER_CONSTRAINTS表和USER_CONS_COLUMNS视图中查询约束的信息

SELECT constraint_name,constraint_type,search_condition 
FROM user_constraints 
WHERE  table_name='EMPLOYEES';

约束类型
C--CHECK和NOT NULL都算为C TYPE
P--PRIMARY KEY
R--REFERENTIAL INTEGRITY就是外部键约束
U--UNIQUE

SELECT constraint_name,column_name 
FROM user_cons_columns 
WHERE  table_name='EMPLOYEES';

码农公寓

学习约束练习：

相关文章