oracle创建和删除约束

数据完整性约束：主键、外键、各种约束的创建删除语句

ORACLE对数据库完整性的约束：

三种方法维护数据完整性：ORACLE完整性约束，数据库触发器，应用程序代码。

应尽量使用ORACLE完整性约束，可靠性和效率高，容易修改，使用灵活，记录在数据字典。

ORACLE五种约束：

非空 not null,定义 的列不能为空。只能在列级定义

唯一，unique，表中每一行所定义 的列或列值不能相同

主键primary key 不能包括空值，主键唯一标识表中每一行，一列或几列组合为主键

外键foreign key 指明一列或几列的组合为外键以维护从表chilD table和主表 parent table 之间的引用完整性referential integrity

条件约束check，表中每一行要满足约束条件。约束加上表上，创建表时可以定义

1.查询约束

查询表中是否有约束并显示约束名：显示表列所对应的约束的信息

BYS@bys1>select a.constraint_name, a.column_name from user_cons_columns a, user_constraints b where a.table_name = upper('&AA');

Enter value for aa: emp

old 1: select a.constraint_name, a.column_name from user_cons_columns a, user_constraints b where a.table_name = upper('&AA')

new 1: select a.constraint_name, a.column_name from user_cons_columns a, user_constraints b where a.table_name = upper('emp')

CONSTRAINT_NAME COLUMN_NAME

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

PK_EMPNO EMPNO

查表中是否有主键约束

BYS@bys1>select a.constraint_name, a.column_name from user_cons_columns a, user_constraints b where a.constraint_name = b.constraint_name and b.constraint_type = 'P' and a.table_name =upper('&table_name');

Enter value for table_name: emp

old 1: select a.constraint_name, a.column_name from user_cons_columns a, user_constraints b where a.constraint_name = b.constraint_name and b.constraint_type = 'P' and a.table_name =upper('&table_name')

new 1: select a.constraint_name, a.column_name from user_cons_columns a, user_constraints b where a.constraint_name = b.constraint_name and b.constraint_type = 'P' and a.table_name =upper('emp')

CONSTRAINT_NAME COLUMN_NAME

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

PK_EMPNO EMPNO

查当前用户下的所有约束的信息

BYS@bys1>col owner for a10

BYS@bys1>col table_name for a10

BYS@bys1>select * from user_cons_columns;

OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME POSITION

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

BYS PK_OBJ_ID TEST2 OBJECT_ID 1

BYS SYS_C0011203 TEST1 OBJECT_ID 1

BYS PK_EMPNO EMP EMPNO 1

查询当前用户的相关约束的状态信息，可以查dba_constraints或USER_constraints

BYS@bys1>select constraint_name,table_name,constraint_type,status,deferrable,deferred,validated from dba_constraints where owner='BYS';

CONSTRAINT_NAME TABLE_NAME C STATUS DEFERRABLE DEFERRED VALIDATED

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

SYS_C0011203 TEST1 P ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED

PK_EMPNO EMP P ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED

PK_OBJ_ID TEST2 P ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED

SCOTT@bys1>select constraint_name,table_name,constraint_type,status,deferrable,deferred,validated from user_constraints;

CONSTRAINT_NAME TABLE_NAME C STATUS DEFERRABLE DEFERRED VALIDATED

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

FK_DEPTNO EMP R ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED

PK_DEPT DEPT P ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED

PK_EMP EMP P ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED

SYS_C0011265 TEST P ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED

PK_A TEST1 P ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED

2.增加、删除、修改约束

增加与修改：

增删主键及外键

alter table dept add constraint pk_dept primary key(deptno);

alter table dept2 add primary key(dname); 不指定约束名，则由系统自动命令约束名。

alter table emp add constraint fk_deptno foreign key(deptno) references dept(deptno) on delete cascade;

alter table emp add constraint fk_deptno foreign key(deptno) references dept(deptno);

注：on delete cascade和on delete set null的作用是用来处理级联删除问题的，

如果你需要删除的数据被其他数据所参照(主、外键），那么你应该决定到底希望oracle怎么处理那些参照这些即将要删除数据的数据的.

你可以有三种方式：

禁止删除，这也是oracle默认的。

将那些参照本值的数据的对应列赋空，使用on delete set null关键字。即删除本表的列中的值，对本列有外键引用的表的相应的列有相应值行会被修改为NULL。

将那些参照本值的数据一并删除，使用on delete cascade关键字，即删除本表的列中的值，对本列有外键引用的表的相应的列有相应值行会被删除

增加CHECK 约束:

alter table dept add constraint valid_deptno check (deptno

SQL>alter table customer add constraint abc check (address in (’海淀’,’朝阳’,’东城’,’西城’,’通州’,’崇文’,’昌平’)); 增加客户的住址只能是’海淀’,’朝阳’,’东城’,’西城’,’通州’,’崇文’,’昌平’；

增加惟一约束

alter table customer add constraint aa

a unique(cardId);

增加非空约束--注意增加非空约束时用的是关键字modify，其它四种约束都是ADD

SQL>alter table dept modify dname not null;

删除

对非空约束的删除：因为非空约束不能指定约束名，应该先通过查询表和列所对应的的约束信息，找出约束名，再删除。如下：

BYS@bys1>alter table dept drop constraint SYS_C0011725;

修改约束--大可以删除了重建

alter table dept2 modify constraint pk_d2 initially immediate;

删除指定名字的约束---可以是主键、外建或其它约束的名字

alter table emp drop constraint PK_EMPNO;

alter table scott.event drop constraint evtid_pk; 删除其它用户的约束--需要DBA权限

不用指定约束名，直接删除表中主键

altertable emp drop primary key cascade; 删除主键约束的时候，如果在些主键上的外键创建时未指定on delete cascade参数，直接删除主键报错，要加上cascade参数。

重命令约束

alter table scott.emp rename constraint pk_emp to emp_empno_pk;

3.修改完整性约束的状态

完整性约束的四种状态

为满足临时违反CONSTRAINT的数据需要，引入states概念

CONSTRAINT可以ENABLE/DISABLE, ENABLE表示数据在INSERT/UPDATE前会被检查，DISABLE则不作检查。

disable novalidate 禁止而无效，新入数据和旧有数据都未经CONSTRAINT检测。即关闭了约束。

enable novalidate 激活而无效 ， 即旧有数据状态未知，不一定满足CONSTRAINT。新入数据需经CONSTRAINT检测。

disable validate 禁止而有效，表中由不能做任何更改，即无法对表中约束所限制的列进行任何DML操作，因为操作可能会使已VALIDATE的数据失去一致性。

enable validate 激活而有效 即新入数据和旧有数据都经CONSTRAINT检测，满足CONSTRAINT条件。 这是默认参数。

延迟性DEFERRED约束，仅在事务TRANSACTION提交时进行数据完整性检查，如违反，整个事务进行回滚。

非延迟性NONDEFERRED约束。立即性约束IMMEDIATE，在每个DML语句结束时进行完整性检查，违反条件的语句将被回滚。默认参数。

示例语句：

BYS@bys1>alter table dept2 add constraint valid_deptno check (deptno

BYS@bys1>select constraint_name,table_name,constraint_type,status,deferrable,deferred,validated from user_constraints;

CONSTRAINT_NAME TABLE_NAME C STATUS DEFERRABLE DEFERRED VALIDATED

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

VALID_DEPTNO DEPT2 C ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED

修改约束，关闭。

alter table scott.person disable novalidate constraint person_personid_pk;

开启

alter table scott.person enable novalidate constraint person_personid_pk;

4.建表并定义约束的之表级定义与列级定义

概念:表级定义 列级定义

列级定义是在定义列的同

时定义约束。

如果在department表定义主键约束

create table department4(dept_id number(12) constraint pk_department primary key,

name varchar2(12),loc varchar2(12));

表级定义:表级定义是指在定义了所有列后，再定义约束。这里需要注意：not null约束只能在列级上定义。

以在建立employee2表时定义主键约束和外键约束为例：

create table employee2(emp_id number(4), name varchar2(15),dept_idnumber(2), constraint pk_employee primary key (emp_id),

constraint fk_department foreign key (dept_id) references department4(dept_id));

5.创建表并使用约束示例

SQL>create table goods(goodsId char(8) primary key, --主键，也可以指定主键名。不指定则系统自动命名。

goodsName varchar2(30)，

unitprice number(10,2) check(unitprice>0),

category varchar2(8), provider varchar2(30) );

SQL>create table customer( customerId char(8) primary key, --主键

name varchar2(50) not null, --不为空

address varchar2(50),

email varchar2(50) unique,

sex char(2) default '男' check(sex in ('男'，'女'))， -- 一个char能存半个汉字，两位char能存一个汉字

cardId char(18) );

SQL>create table purchase( customerId char(8) references customer(customerId),

goodsId char(8) references goods(goodsId),nums number(10) check (nums between 1 and 30));

数据完整性约束：主键、外键、各种约束的创建删除语句

ORACLE对数据库完整性的约束：

三种方法维护数据完整性：ORACLE完整性约束，数据库触发器，应用程序代码。

应尽量使用ORACLE完整性约束，可靠性和效率高，容易修改，使用灵活，记录在数据字典。

ORACLE五种约束：

非空 not null,定义 的列不能为空。只能在列级定义

唯一，unique，表中每一行所定义 的列或列值不能相同

主键primary key 不能包括空值，主键唯一标识表中每一行，一列或几列组合为主键

外键foreign key 指明一列或几列的组合为外键以维护从表chilD table和主表 parent table 之间的引用完整性referential integrity

条件约束check，表中每一行要满足约束条件。约束加上表上，创建表时可以定义

1.查询约束

查询表中是否有约束并显示约束名：显示表列所对应的约束的信息

BYS@bys1>select a.constraint_name, a.column_name from user_cons_columns a, user_constraints b where a.table_name = upper('&AA');

Enter value for aa: emp

old 1: select a.constraint_name, a.column_name from user_cons_columns a, user_constraints b where a.table_name = upper('&AA')

new 1: select a.constraint_name, a.column_name from user_cons_columns a, user_constraints b where a.table_name = upper('emp')

CONSTRAINT_NAME COLUMN_NAME

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

PK_EMPNO EMPNO

查表中是否有主键约束

BYS@bys1>select a.constraint_name, a.column_name from user_cons_columns a, user_constraints b where a.constraint_name = b.constraint_name and b.constraint_type = 'P' and a.table_name =upper('&table_name');

Enter value for table_name: emp

old 1: select a.constraint_name, a.column_name from user_cons_columns a, user_constraints b where a.constraint_name = b.constraint_name and b.constraint_type = 'P' and a.table_name =upper('&table_name')

new 1: select a.constraint_name, a.column_name from user_cons_columns a, user_constraints b where a.constraint_name = b.constraint_name and b.constraint_type = 'P' and a.table_name =upper('emp')

CONSTRAINT_NAME COLUMN_NAME

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

PK_EMPNO EMPNO

查当前用户下的所有约束的信息

BYS@bys1>col owner for a10

BYS@bys1>col table_name for a10

BYS@bys1>select * from user_cons_columns;

OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME POSITION

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

BYS PK_OBJ_ID TEST2 OBJECT_ID 1

BYS SYS_C0011203 TEST1 OBJECT_ID 1

BYS PK_EMPNO EMP EMPNO 1

查询当前用户的相关约束的状态信息，可以查dba_constraints或USER_constraints

BYS@bys1>select constraint_name,table_name,constraint_type,status,deferrable,deferred,validated from dba_constraints where owner='BYS';

CONSTRAINT_NAME TABLE_NAME C STATUS DEFERRABLE DEFERRED VALIDATED

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

SYS_C0011203 TEST1 P ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED

PK_EMPNO EMP P ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED

PK_OBJ_ID TEST2 P ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED

SCOTT@bys1>select constraint_name,table_name,constraint_type,status,deferrable,deferred,validated from user_constraints;

CONSTRAINT_NAME TABLE_NAME C STATUS DEFERRABLE DEFERRED VALIDATED

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

FK_DEPTNO EMP R ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED

PK_DEPT DEPT P ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED

PK_EMP EMP P ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED

SYS_C0011265 TEST P ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED

PK_A TEST1 P ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED

2.增加、删除、修改约束

增加与修改：

增删主键及外键

alter table dept add constraint pk_dept primary key(deptno);

alter table dept2 add primary key(dname); 不指定约束名，则由系统自动命令约束名。

alter table emp add constraint fk_deptno foreign key(deptno) references dept(deptno) on delete cascade;

alter table emp add constraint fk_deptno foreign key(deptno) references dept(deptno);

注：on delete cascade和on delete set null的作用是用来处理级联删除问题的，

如果你需要删除的数据被其他数据所参照(主、外键），那么你应该决定到底希望oracle怎么处理那些参照这些即将要删除数据的数据的.

你可以有三种方式：

禁止删除，这也是oracle默认的。

将那些参照本值的数据的对应列赋空，使用on delete set null关键字。即删除本表的列中的值，对本列有外键引用的表的相应的列有相应值行会被修改为NULL。

将那些参照本值的数据一并删除，使用on delete cascade关键字，即删除本表的列中的值，对本列有外键引用的表的相应的列有相应值行会被删除

增加CHECK 约束:

alter table dept add constraint valid_deptno check (deptno

SQL>alter table customer add constraint abc check (address in (’海淀’,’朝阳’,’东城’,’西城’,’通州’,’崇文’,’昌平’)); 增加客户的住址只能是’海淀’,’朝阳’,’东城’,’西城’,’通州’,’崇文’,’昌平’；

增加惟一约束

alter table customer add constraint aa

a unique(cardId);

增加非空约束--注意增加非空约束时用的是关键字modify，其它四种约束都是ADD

SQL>alter table dept modify dname not null;

删除

对非空约束的删除：因为非空约束不能指定约束名，应该先通过查询表和列所对应的的约束信息，找出约束名，再删除。如下：

BYS@bys1>alter table dept drop constraint SYS_C0011725;

修改约束--大可以删除了重建

alter table dept2 modify constraint pk_d2 initially immediate;

删除指定名字的约束---可以是主键、外建或其它约束的名字

alter table emp drop constraint PK_EMPNO;

alter table scott.event drop constraint evtid_pk; 删除其它用户的约束--需要DBA权限

不用指定约束名，直接删除表中主键

altertable emp drop primary key cascade; 删除主键约束的时候，如果在些主键上的外键创建时未指定on delete cascade参数，直接删除主键报错，要加上cascade参数。

重命令约束

alter table scott.emp rename constraint pk_emp to emp_empno_pk;

3.修改完整性约束的状态

完整性约束的四种状态

为满足临时违反CONSTRAINT的数据需要，引入states概念

CONSTRAINT可以ENABLE/DISABLE, ENABLE表示数据在INSERT/UPDATE前会被检查，DISABLE则不作检查。

disable novalidate 禁止而无效，新入数据和旧有数据都未经CONSTRAINT检测。即关闭了约束。

enable novalidate 激活而无效 ， 即旧有数据状态未知，不一定满足CONSTRAINT。新入数据需经CONSTRAINT检测。

disable validate 禁止而有效，表中由不能做任何更改，即无法对表中约束所限制的列进行任何DML操作，因为操作可能会使已VALIDATE的数据失去一致性。

enable validate 激活而有效 即新入数据和旧有数据都经CONSTRAINT检测，满足CONSTRAINT条件。 这是默认参数。

延迟性DEFERRED约束，仅在事务TRANSACTION提交时进行数据完整性检查，如违反，整个事务进行回滚。

非延迟性NONDEFERRED约束。立即性约束IMMEDIATE，在每个DML语句结束时进行完整性检查，违反条件的语句将被回滚。默认参数。

示例语句：

BYS@bys1>alter table dept2 add constraint valid_deptno check (deptno

BYS@bys1>select constraint_name,table_name,constraint_type,status,deferrable,deferred,validated from user_constraints;

CONSTRAINT_NAME TABLE_NAME C STATUS DEFERRABLE DEFERRED VALIDATED

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

VALID_DEPTNO DEPT2 C ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED

修改约束，关闭。

alter table scott.person disable novalidate constraint person_personid_pk;

开启

alter table scott.person enable novalidate constraint person_personid_pk;

4.建表并定义约束的之表级定义与列级定义

概念:表级定义 列级定义

列级定义是在定义列的同

时定义约束。

如果在department表定义主键约束

create table department4(dept_id number(12) constraint pk_department primary key,

name varchar2(12),loc varchar2(12));

表级定义:表级定义是指在定义了所有列后，再定义约束。这里需要注意：not null约束只能在列级上定义。

以在建立employee2表时定义主键约束和外键约束为例：

create table employee2(emp_id number(4), name varchar2(15),dept_idnumber(2), constraint pk_employee primary key (emp_id),

constraint fk_department foreign key (dept_id) references department4(dept_id));

5.创建表并使用约束示例

SQL>create table goods(goodsId char(8) primary key, --主键，也可以指定主键名。不指定则系统自动命名。

goodsName varchar2(30)，

unitprice number(10,2) check(unitprice>0),

category varchar2(8), provider varchar2(30) );

SQL>create table customer( customerId char(8) primary key, --主键

name varchar2(50) not null, --不为空

address varchar2(50),

email varchar2(50) unique,

sex char(2) default '男' check(sex in ('男'，'女'))， -- 一个char能存半个汉字，两位char能存一个汉字

cardId char(18) );

SQL>create table purchase( customerId char(8) references customer(customerId),

goodsId char(8) references goods(goodsId),nums number(10) check (nums between 1 and 30));


相关文章

  • Oracle丢失归档日志文件的数据库恢复方法

    • Oracle 丢失归档日志文件的数据库恢复方法 丢失归档日志文件的数据库恢复方法,从一个不能正常打开的数据库(由于一个/多个数据库文件与其他文件不一致) 中提取数据.场景:一个磁盘损坏了并且丢失了一个数据库文件.从一周前的热备转储数据文件, ...查看


  • IT168-服务器专区:更改Oracle数据库表的表空间

    • 更改Oracle数据库表的表空间 作者:chinaitlab 佚名   更新时间:2005-11-17    收藏此页 [IT168 服务器学院]在Oracle数据库管理系统中,创建库表(table)时要分配一个表空间(tablespace ...查看


  • 数据库设计规范

    • 修订历史记录 发放范围:产品研发部 1. 概述 2. 数据库设计的基本原则 3. 数据库建模 3.1 数据分析 3.2 数据关系分析 3.3 数据量分析 3.4 扩展性分析 3.5 数据字典(参考) 3.5.1 数据项 3.5.2 数据结构 ...查看


  • 达内java15.12第二次月考(附答案).doc

    • 在线考试 本次考试得分:98.0 ∙ 1. (单选)查询cost表中资费名称,月固定费用,月包在线时长,单位费用,下列sql语句正确的是: o A. select name/base_duration/base_cost/unit_cost ...查看


  • ORACLE全面内容管理(PDF)

    • ORACLE 数据表 ORACLE 全面内容 管理 主要特性和优势 主要特性 · 以内部文件格式管理业务内容 · 自动转换为 Web 格式以便于查看 · 控制整个内容生命周期 · 简化了内容的查找.访问和重复使用 · 快速构建和管理多个网站 ...查看


  • 软件需求分析说明书

    • 学生信息管理系统 需求分析说明书 1.引言 1.1编写目的 确定学生信息管理系统功能的有效性需求:以供本系统的开发人员参考. 1.2项目背景 开发软件名称:学生信息管理系统. 用户:教学办公室 项目和其他软件:系统的关系. 本项目采用客户机 ...查看


  • 2017年全国计算机三级数据库考点知识大全

    • 2017年全国计算机三级数据库考点知识大全 1.ISP(internet 服务提供商) 是用户接入internet 的入口点,一方面他为用户提供接入internet 服务,另一方面,他也为用户提供各类信息资源.一般用户接入internet ...查看


  • 甲骨文用户手册

    • Oracle WebCenter Sites 11g 第 1 版 提供者指导教程:创建页面和管理内容 Oracle WebCenter Sites 11g 是以前称为 FatWire Content Server 的产品的最新和最完善版本. ...查看


  • 医院管理系统需求分析说明文档

    • 医院管理系统 2016年需求分析说明文档 [1**********] [1**********] 学生学号 [1**********] [1**********] [1**********] 王凯,姜可,徐洋洋,夏学生姓名 辉,王维 学生班 ...查看


热门内容