ITPUB首页 |  论坛 | 认证专区 | 博客 登录 | 注册

苍雪明南

低调做人,高调做事。

  • 博客访问: 445156
  • 博文数量: 492
  • 用 户 组: 普通用户
  • 注册时间: 2015-01-08 17:42
  • 认证徽章:
文章分类

全部博文(492)

文章存档

2018年(25)

2017年(209)

2016年(242)

2015年(16)

分类: Linux

2017-10-09 21:16:12

SQL> create table t_hash_partition_parent(a int primary key,b int)
  2  partition by hash(a)
  3  partitions 2
  4
SQL> /
Table created.
---报错原因:a int后应有,号
SQL> create table t_reference_partition(id int primary key,a int constraint fk_a
 foreign key(a), references t_hash_partition_parent(a))
  2  partition by reference(fk_a)
  3  /
create table t_reference_partition(id int primary key,a int constraint fk_a fore
ign key(a), references t_hash_partition_parent(a))
                                                            *
ERROR at line 1:
ORA-02253: constraint specification not allowed here
---扫错原因:外键列必须是not null
SQL> ed
Wrote file afiedt.buf
  1  create table t_reference_partition(id int primary key,a int constraint fk_a
 foreign key(a), references t_hash_partition_parent(a))
  2* partition by reference(fk_a)
SQL> create table t_reference_partition(id int primary key,a int,constraint fk_a
 foreign key(a) references t_hash_partition_parent(a))
  2  partition by reference(fk_a)
  3  /
partition by reference(fk_a)
                       *
ERROR at line 2:
ORA-14652: reference partitioning foreign key is not supported

SQL> ed
SQL> create table t_reference_partition(id int primary key,a int not null,constr
aint fk_a foreign key(a) references t_hash_partition_parent(a))
  2  partition by reference(fk_a)
  3  /
Table created.
-----分表的分区数
SQL> select partition_name from user_tab_partitions where table_name='T_HASH_PAR
TITION_PARENT';
PARTITION_NAME
------------------------------------------------------------
SYS_P43
SYS_P44

---引用分区的分区表,结论:引用分区的分区表取决于父分区表的分区个数
SQL> select partition_name from user_tab_partitions where table_name='T_REFERENC
E_PARTITION';
PARTITION_NAME
------------------------------------------------------------
SYS_P45
SYS_P46

-----引用分区之constraint使用说明:
---引用分区必须要引用外键约束,且主分区表的父键必须构建pk或unique constraint,同时要满足enable validate not deferenable
You must specify a referential integrity constraint defined on the table being created, which must refer to a primary key or unique constraint on the parent table.
The constraint must be in ENABLE VALIDATE NOT DEFERRABLE state, which is the default when you specify a referential integrity constraint during table creation.
 
---所有引用的外键必须定义为not null
All foreign key columns referenced in constraint must be NOT NULL.
 
---如指定约束,不能再指定on delete set null;即在操作父表时,不能同时设置匹配子表的记录为空
When you specify the constraint, you cannot specify the ON DELETE SET NULL clause of the references_clause.
 
---所引用的父表必须是分区表;且父表分表方法不能是interval mode
The parent table referenced in the constraint must be an existing partitioned table. It can be partitioned by any method except interval partitioning.
 
---引用分区和父分区表所引用的外键及父键绝不能包含基于pl sql function or lob列的虚拟列
The foreign and parent keys cannot contain any virtual columns that reference PL/SQL functions or LOB columns.
 

---引用分区的操作限制:

-----引用分区约束取决于父分区表的分区策略,即父分区是什么分区类型,它就隶属于什么分区类型
Restrictions for reference partitioning are derived from the partitioning strategy of the parent table.
 
----iot,外部表,域索引存储表不能使用引用分区
You cannot specify this clause for an index-organized table, an external table, or a domain index storage table.
 
----父分区表也可以是引用分区,但约束不能是自包含
The parent table can be partitioned by reference, but constraint cannot be self-referential. The table being created cannot be partitioned based on a reference to itself.
 
----如父分区表启用了行移动,引用分区也须启行移动
If ROW MOVEMENT is enabled for the parent table, it must also be enabled for the child table.
 
 
阅读(43) | 评论(0) | 转发(0) |
相关热门文章
  • PL/SQL Challenge 每日一题:2...
  • PL/SQL Challenge 每日一题:2...
  • 11g OCM考试归来 之 第八和九...
  • 11g OCM考试归来 之 第七场景...
  • 11g OCM考试归来 之 第六场景...
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册

友情链接:guoqibee.com  万达主管QQ  万达招商  guoqibee.com  万达主管  万达娱乐开户  万达娱乐直属QQ  万达娱乐直属  万达娱乐注册  万达娱乐主管QQ