oracle 培训笔记 约束
来源:互联网 发布:excel数据分析pdf 编辑:程序博客网 时间:2024/04/29 00:01
约束分类:
primary key
not null
foreign key
check
unique
--查询约束状态
drop table t01 purge;
create table t01(id number primary key,name varchar2(20));
select * from user_constraints;
status
deferrable
deferred
validated
延迟
deferrable:可延迟,检查方式:immediate,deferred
--约束可延迟,立即检查
create table t01(id number primary key deferrable
,name varchar2(20));
insert into t01 values(1,'a');
insert into t01 values(1,'b');
--约束可延迟,延迟到提交检查
create table t02(id number primary key deferrable initially deferred
,name varchar2(20));
insert into t02 values(1,'a');
insert into t02 values(1,'b');
commit;
--查看表t01,t02,t03主键带索引
create table t03(id number primary key
,name varchar2(20));
select * from user_indexes;
uniqueness
约束状态
enable disable
validate novalidate
enable validate: 启用约束,校验数据(新,旧)
enable novalidate:启用约束,校验数据(新)
disable novalidate:停用约束
disable validate:停用约束,
--向有主键表中插入重复数据
步骤1:让主键约束对应的索引一定非唯一
办法1:建主键时指定deferrable
create table t01(id number primary key deferrable ,name varchar2(20));
办法2:加主键前先建一个非唯一索引,然后加主键
create table t01(id number,name varchar2(20));
create index t01i on t04(id);
alter table t01 add constraint t04i primary key(id);
步骤2:禁用约束
alter table t01 modify constraint xxxx disable;
insert into t01 values(1,'a');
insert into t01 values(1,'b');
commit;
步骤3:启用约束,但不校验已存在数据
alter table t01 modify constraint SYS_C002990 enable novalidate;
select * from t01;
primary key
not null
foreign key
check
unique
--查询约束状态
drop table t01 purge;
create table t01(id number primary key,name varchar2(20));
select * from user_constraints;
status
deferrable
deferred
validated
延迟
deferrable:可延迟,检查方式:immediate,deferred
--约束可延迟,立即检查
create table t01(id number primary key deferrable
,name varchar2(20));
insert into t01 values(1,'a');
insert into t01 values(1,'b');
--约束可延迟,延迟到提交检查
create table t02(id number primary key deferrable initially deferred
,name varchar2(20));
insert into t02 values(1,'a');
insert into t02 values(1,'b');
commit;
--查看表t01,t02,t03主键带索引
create table t03(id number primary key
,name varchar2(20));
select * from user_indexes;
uniqueness
约束状态
enable disable
validate novalidate
enable validate: 启用约束,校验数据(新,旧)
enable novalidate:启用约束,校验数据(新)
disable novalidate:停用约束
disable validate:停用约束,
--向有主键表中插入重复数据
步骤1:让主键约束对应的索引一定非唯一
办法1:建主键时指定deferrable
create table t01(id number primary key deferrable ,name varchar2(20));
办法2:加主键前先建一个非唯一索引,然后加主键
create table t01(id number,name varchar2(20));
create index t01i on t04(id);
alter table t01 add constraint t04i primary key(id);
步骤2:禁用约束
alter table t01 modify constraint xxxx disable;
insert into t01 values(1,'a');
insert into t01 values(1,'b');
commit;
步骤3:启用约束,但不校验已存在数据
alter table t01 modify constraint SYS_C002990 enable novalidate;
select * from t01;
- oracle 培训笔记 约束
- Oracle课程培训笔记
- oracle 培训笔记[1]
- oracle 培训笔记[2]
- oracle 培训笔记[3]
- oracle 培训笔记[4]
- Oracle 培训笔记
- Oracle培训笔记
- oracle笔记(约束)
- Oracle笔记十: 约束
- Oracle培训笔记 8.6 用户权限
- Oracle培训笔记 8.7 审计
- Oracle 培训笔记 9.4 闪回
- Oracle 培训笔记 9.4 rman
- Oracle优化培训笔记-第一节
- 【Oracle】 盖国强太原培训笔记
- Oracle笔记(十) 约束
- Oracle笔记(十) 约束
- oracle中判断一个字符串是否全部为数字的函数-translate
- C#的基本概念
- 远程加载与卸载DLL
- Oracle 培训笔记 8.27 redo undo checkpoint
- windows下安装,配置gcc编译器
- oracle 培训笔记 约束
- js操作dom
- linux中模块的编写
- java线程机制
- FIFO 两个进程同时写同一个FIFO
- IOS拉伸圆角图片
- 缩略图算法
- iModel插件介绍之DupChecker_ForTable
- delphi 判断SQL Server 服务器是否安装与开启