oracle主键和外键

最新推荐文章于 2022-11-20 23:07:00 发布
最新推荐文章于 2022-11-20 23:07:00 发布
阅读量975 收藏
点赞数
分类专栏： oracle 文章标签： oracle table sql insert null delete
CREATE TABLE "SCOTT"."MID_A_TAB"

　　(    "A" VARCHAR2(20 BYTE),

　　"B" VARCHAR2(10 BYTE),

　　"DETPNO" VARCHAR2(10 BYTE)

　　)TABLESPACE "USERS" ;

　　CREATE TABLE "SCOTT"."MID_B_TAB"

　　(    "A" VARCHAR2(20 BYTE),

　　"B" VARCHAR2(10 BYTE),

　　"DEPTNO" VARCHAR2(10 BYTE)

　　)TABLESPACE "USERS" ;

　　--给MID_A_TAB表添加主键

　　alter table mid_a_tab add constraint a_pk primary key (detpno);

　　--给MID_B_TAB表添加主键

　　alter table mid_b_tab add constraint b_pk primary key(a);

　　--给子表MID_B_TAB添加外键,并且引用主表MID_A_TAB的DETPNO列，并通过on delete cascade指定引用行为是级联删除

　　alter table mid_b_tab add constraint b_fk foreign key (deptno) references mid_a_tab (detpno) on delete cascade;

　　--向这样就创建了好子表和主表

　　向主表添加数据记录

　　SQL> insert into mid_a_tab(a,b,detpno)  values('1','1','10');

　　已创建 1 行。

　　已用时间:  00: 00: 00.00

　　向子表添加数据

　　SQL> insert into mid_b_tab(a,b,deptno) values('1','2','6');

　　insert into mid_b_tab values('1','2','6')

　　*

　　第 1 行出现错误:

　　ORA-00001: 违反唯一约束条件 (SCOTT.B_PK)

　　已用时间:  00: 00: 00.00

　　可见上面的异常信息，那时因为子表插入的deptno的值是6，然而此时我们主表中

　　detpno列只有一条记录那就是10，所以当子表插入数据时，在父表中不能够找到该引用

　　列的记录，所以出现异常。

　　但我们可以这样对子表的数据的进行插入(即：在子表的deptno列插入null,因为我们在建表的时候

　　并没有对该列进行not null的约束限制)：

　　SQL> insert into mid_b_tab(a,b,deptno) values('3','2',null);

　　已创建 1 行。

　　已用时间:  00: 00: 00.00

　　现在如果我们把子表mid_b_tab中deptno列加上not null约束。

　　SQL> alter table mid_b_tab modify deptno not null;

　　alter table mid_b_tab modify deptno not null

　　*

　　第 1 行出现错误:

　　ORA-02296: 无法启用 (SCOTT.) - 找到空值

　　已用时间:  00: 00: 00.01

　　上面又出现异常，这是因为现在mid_b_tab表中有了一条记录，就是我们先前添加的

　　那条记录。

　　3，2，null

　　现在我们要把该表的deptno列进行not null约束限制，所以oracle不让我们这样干。

　　那我们就只有把该表给delete或truncate掉，然后在修改deptno列为非空。

　　SQL> delete from mid_b_tab;

　　已删除2行。

　　已用时间:  00: 00: 00.01

　　再次修改子表mid_b_tab表的deptno列为非空。

　　SQL> alter table mid_b_tab modify deptno not null;

　　表已更改。

　　已用时间:  00: 00: 00.01

　　修改成功！

　　我们再次插入数据

　　insert into mid_b_tab(a,b,deptno) values('13','2',null);试试。

　　SQL> insert into mid_b_tab(a,b,deptno) values('13','2',null);

　　insert into mid_b_tab(a,b,deptno) values('13','2',null)

　　*

　　第 1 行出现错误:

　　ORA-01400: 无法将 NULL 插入 ("SCOTT"."MID_B_TAB"."DEPTNO")

　　已用时间:  00: 00: 00.00

　　看见现在oracle不让我们插入空值了。

　　所以我们在创建子表的外键约束时，该表的引用列必须要进行not null限制，也可以在

　　该列创建unique，或primary key约束，并且引用列与被引用列的数据类型必须相同。

　　SQL> insert into mid_b_tab(a,b,deptno) values('13','2','10');

　　已创建 1 行。

　　已用时间:  00: 00: 00.01

　　此时数据插入成功，因为此时插入的10，在主表中的被引用列中已经存在了。

　　现在我们一系列的操作：

　　SQL> select * from mid_b_tab ;

　　A                    B          DE

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

　　13                   2          10

　　已用时间:  00: 00: 00.00

　　SQL> select * from mid_a_tab;

　　A                    B          DE

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

　　1                    1          10

　　已用时间:  00: 00: 00.00

　　SQL> delete from mid_a_tab;

　　已删除 1 行。

　　已用时间:  00: 00: 00.01

SQL> select * from mid_b_tab;

　　未选定行

　　从上边的操作中可以看出当我们删除了主表中的记录后，子表中相应的记录

　　也被级联删除掉了。

　　通过引用行为可以确定如何处理子表中的外键字段。引用类型包括3中类型：

　　1.on delete cascade;--级联删除

　　2.on set null;--删除主表中的记录后，子表中的相应记录的列被设置为null(但子表的该字段必须支持null值)。

　　3.on no action;--不允许删除主表中被引用的数据，该操作会被禁止。

　　如果有on delete cascade，而且没有在子表上加索引：那么每删除主表中的一行

　　都会对子表做一个全表扫描。而且如果从父表删除多行，那么每从父表中删除一行

　　就要扫描一次子表。

　　SQL> select * from mid_a_tab a,mid_b_tab b where

　　2  a.detpno=b.deptno;

　　A                    B          DETPNO     A                    B          DEPTNO

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

　　1                    2          12         2                    2          12

　　1                    2          12         1                    1          12

　　2                    22         13         22                   212        13

　　3                    33         14         55                   6666       14

　　已用时间:  00: 00: 00.00

　　执行计划

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

　　0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=4 Bytes=208

　　)

　　1    0   HASH JOIN (Cost=7 Card=4 Bytes=208)

　　2    1     TABLE ACCESS (FULL) OF 'MID_A_TAB' (TABLE) (Cost=3 Card=

　　3 Bytes=78)

　　3    1     TABLE ACCESS (FULL) OF 'MID_B_TAB' (TABLE) (Cost=3 Card=

　　4 Bytes=104)

　　统计信息

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

　　210  recursive calls

　　0  db block gets

　　74  consistent gets

　　0  physical reads

　　0  redo size

　　748  bytes sent via SQL*Net to client

　　512  bytes received via SQL*Net from client

　　2  SQL*Net roundtrips to/from client

　　8  sorts (memory)

　　0  sorts (disk)

　　4  rows processed

　　SQL> create index mid_b_index on mid_b_tab(deptno);

　　索引已创建。

　　已用时间:  00: 00: 00.00

　　SQL> select * from mid_a_tab a,mid_b_tab b where

　　2   a.detpno=b.deptno;

　　A                    B          DETPNO     A                    B          DE

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

　　1                    2          12         2                    2          12

　　1                    2          12         1                    1          12

　　2                    22         13         22                   212        13

　　3                    33         14         55                   6666       14

　　已用时间:  00: 00: 00.01

　　执行计划

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

　　0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=4 Bytes=208

　　)

　　1    0   TABLE ACCESS (BY INDEX ROWID) OF 'MID_B_TAB' (TABLE) (Cost

　　=1 Card=1 Bytes=26)

　　2    1     NESTED LOOPS (Cost=6 Card=4 Bytes=208)

　　3    2       TABLE ACCESS (FULL) OF 'MID_A_TAB' (TABLE) (Cost=3 Car

　　d=3 Bytes=78)

　　4    2       INDEX (RANGE SCAN) OF 'MID_B_INDEX' (INDEX) (Cost=0 Ca

　　rd=4)

　　统计信息

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

　　10  recursive calls --访问数据字典得到元数据。第二次执行相同语句，递归调用基本为零

　　0  db block gets  -- 指DML语句所得到的数据块个数   

　　31  consistent gets  --重要！！指select语句所得到的数据块个数 

　　0  physical reads --硬盘上读出的数据 

　　0  redo size --产生的日志

　　748  bytes sent via SQL*Net to client  --网络流量指标   

　　512  bytes received via SQL*Net from client --网络流量指标 

　　2  SQL*Net roundtrips to/from client

　　2  sorts (memory)

　　0  sorts (disk)

　　4  rows processed

　　大家可以从上面的执行计划中看出，向子表添加索引前后查询的差别。如果两表中的数据量再大点的话那么效果可能会更明显。


 

 

 

kyle8525_nsn
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
oracle主键和外键[文].pdf
10-11
oracle主键和外键[文].pdf
ORA-02296: cannot enable (ELAPPADM.) - null values found
JAVA海洋
05-03 7359
表示字段有空值,不能设置为空值，解决的方法：可以用update语句，将字段批量设置一个值，然后再改变属性
plsql_ora_02296
weixin_34364071的博客
03-22 298
这个错误是因为你要修改你的字段约束为不为空，但是你表中的有该字段为空的记录，所以你修改为不能为空会报错，不能执行 只需将该条记录删掉后执行即可。 修改表中字段不能位空 alter table sys_oa_qjglsq modify qjlb not null; 转载于:https://www.cnblogs.com/CrisZjie180228/p/8621770.html...
oracle截断列长度的方法：alter
枫。晨夕的专栏
10-28 1910
<br />Create  table  t_temp (keyed number(4)notnull);<br />将字段设改为可空<br />Altertablemodify(keyid number(4)null)--null必须写<br />将字段设改为非空<br />Altertablemodify(keyid number(4)notnull);<br />这里必须先保证字段中各个值非空，否则会报如下错误 ora-02296<br /> <br /> <br />ora-02296:cannot
Oracle的主键和外键
C语言技术网-码农有道
03-25 734
文章目录一、表的主键二、表的外键1、外键的概念2、外键的作用3、外键约束4、创建外键5、删除外键6、示例脚本三、版权声明 一、表的主键 在现实世界中，很多数据具有唯一的特性，例如身份证号码，在国家人口基本信息表中，一定不会存在多个人用同一个身份证号码的情况，再例如手机号码、QQ号码、银行帐号等等，还有学生管理系统，学生的年级、班级和学号三个字段组合起来是唯一的标识。 如果表中一个字段或多个字段组合...
Oracle中遇到的错误
weixin_30247307的博客
04-27 933
1.ORA-00937: 不是单组分组函数 和 不是group by表达式 --select count(corp_tn),state_code from t_oa_main where corp_tn='jiayuanshu' and oa_code='QJ_PT_0005_2017_3_0004' group by corp_tn; --不是group by表达式--sele...
24.Oracle的主键和外键1
08-08
2、外键的作用合理的数据结构设计，表中的数据一定有一致性约束，使用外键，让数据库去约束数据的一致，不给任何人出错的机会 3、外键约束1）当对从表进行操作时，数据
oracle表主键和外键的区别
10-30
主键和外键的区别 主键是定义一个表中起主要作用的数据项，这些数据项的数据在表中是唯一的，同时系统按主键为表建立索引。 外键是定义一个表中的某数据项的数据，要参照另一个表的主键数据。既没有在另一个表的...
oracle建表练习（主键外键）
08-28
简单的建表联系，包括主键外键，帮助新手快速熟悉建表的语句操作
ARTools.Oracle抽取指定库的主键、外键、索引
10-11
另附以下功能 * 生成目录树 * 生成迁移脚本 * 查找所有System.out,.err * 批量替换多关键字 详情见 http://blog.csdn.net/amosryan/article/details/6684465
Oracle非空约束
实践求真知
11-22 4382
一 约束的作用 1、定义规则 2、确保完整性  二 非空约束 1、在创建表时设置非空约束 CREATE TABLE table_name( column_name datatype NOT NULL,... ); SQL> create table userinf_1 2(id number(6,0), 3 username varchar2(20)notnull,
升级过程中的问题
weixin_45909428的博客
07-17 1279
使用升级工具升级过程中遇到的问题 1、 “ORA-00947：没有足够的值” ***问题分析：***经查看后台报错日志得知，在进行thisentrusthgmortagage表数据插入时，thisentrusthgmortagage表相对于原表TENTRUSTHGMORTAGAGE缺少两个字段（l_bondlending_amount，l_deal_bondlending_amount）。 ***解决办法：***为新表添加新列解决问题。 sql：alter table thisentrusthgmorta
了解 Oracle 中的主键和外键
KevinChen2019的博客
11-20 4040
例如超女基本信息表，编号的字段名是id，在超女选秀活动中，每个超女的编号肯定是唯一的，不可能存在两个编号相同的超女，否则会引起混乱，我们可以把id字段设置为T_GIRL表的主键，后面的工作交给数据库，如果试图往表中插入多条id相同的记录，数据库将拒绝。在Oracle数据库中，虽然主键不是必需的，但是最好为每个表都设置一个主键，不管是单字段主键还是多字段主键（复合主键），它的存在代表了表结构的完整性，主键还可以用于其他表的外键关联，外键的知识下面再介绍。对超女基本信息表来说，赛区代码是该表的外键。
Oracle某表某字段存在空值，如何将其属性强制为“非空"？
panshaobinSB的博客
07-18 806
什么意思？如果要将该字段加上非空约束 SQL> create table tttt as select 1 id,2 num from dual 2 union all select 2,null from dual; Table created SQL> alter table tttt modify num not null; alter table t...
SQL修改列为非空属性
e小王同学V的博客
10-21 2万+
语句很简单，但经常容易忽略一个事实，执行语句时报下面的错误： ORA-02296: 无法启用 (SYSTEM.) - 找到空值 导致修改不能成功。
Oracle中修改某个字段可以为空
andybbc的博客
04-22 3万+
待修改字段假定为：shuifen 当该字段为空时，可直接修改： alter table reportqymx modify shuifen null; 当待修改字段不为空时： 新增一列 把要改变的列的值转移过去并清空原列的值 改变列的数据类型 把值转移回来，并删除新增的列 alter table reportqymx add shuifen_temp number(10);
sql 修改某列字段为非空 not null
热门推荐
u012045045的博客
11-04 4万+
修改数据库表的已存在的某列为非空属性   alter table table_name modify column_name not null; 例如： 添加一个字段 ALTER TABLE jw_user_role ADD zk_env VARCHAR(16); 修改字段为not null，还要把原来的类型也写出来 ALTER TABLE jw_user_role MODIFY  ...
ORA 各种oraclesql错误
winter2summer的博客
04-16 6861
ORA-00001: 违反唯一约束条件 (.)  ORA-00017: 请求会话以设置跟踪事件  ORA-00018: 超出最大会话数  ORA-00019: 超出最大会话许可数  ORA-00020: 超出最大进程数 ()  ORA-00021: 会话附属于其它某些进程；无法转换会话  ORA-00022: 无效的会话 ID；访问被拒绝  ORA-00023: 会话引用进程私用内存
oracle 查看主键信息,Oracle中查询表字段基本信息、主键、外键（整理）
最新发布
06-02
要查看Oracle表的主键信息，可以使用以下SQL语句： ``` SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner FROM all_constraints cons, all_cons_columns cols WHERE cols.table_name = '表名' AND cons.constraint_type = 'P' AND cons.constraint_name = cols.constraint_name AND cons.owner = cols.owner ORDER BY cols.table_name, cols.position; ``` 其中，'表名'需要替换为具体的表名。 这个SQL语句会返回表中主键的名称、所包含的列名、列的位置信息、主键状态（是否启用）、主键所属的用户等信息。 如果想要查询表的字段基本信息和外键信息，可以使用以下SQL语句： ``` SELECT cols.column_name, cols.data_type, cols.data_length, cols.nullable, cons.constraint_type, cons.constraint_name FROM all_constraints cons, all_cons_columns cols WHERE cols.table_name = '表名' AND cons.constraint_name = cols.constraint_name AND cons.owner = cols.owner ORDER BY cols.table_name, cols.position; ``` 这个SQL语句会返回表中所有列的名称、数据类型、数据长度、是否可为空以及列所属的外键信息（如果有的话）。注意，这个SQL语句不会返回主键信息，如果需要查询主键信息，还需要结合上面的SQL语句进行查询。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值