create table t_hash_partition_parent(a int primary key,b int)  2  partition by hash(a)  3  partitions 2  4SQL> /Table cr..."/>
溫馨提示×

溫馨提示×

您好,登錄后才能下訂單哦!

密碼登錄×
登錄注冊×
其他方式登錄
點(diǎn)擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》

oracle reference partition引用分區(qū)(一)

發(fā)布時間:2020-08-10 23:59:05 來源:ITPUB博客 閱讀:183 作者:路途中的人2012 欄目:建站服務(wù)器
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.
---報(bào)錯原因:a int后應(yīng)有,號
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.
-----分表的分區(qū)數(shù)
SQL> select partition_name from user_tab_partitions where table_name='T_HASH_PAR
TITION_PARENT';
PARTITION_NAME
------------------------------------------------------------
SYS_P43
SYS_P44

---引用分區(qū)的分區(qū)表,結(jié)論:引用分區(qū)的分區(qū)表取決于父分區(qū)表的分區(qū)個數(shù)
SQL> select partition_name from user_tab_partitions where table_name='T_REFERENC
E_PARTITION';
PARTITION_NAME
------------------------------------------------------------
SYS_P45
SYS_P46

-----引用分區(qū)之constraint使用說明:
---引用分區(qū)必須要引用外鍵約束,且主分區(qū)表的父鍵必須構(gòu)建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;即在操作父表時,不能同時設(shè)置匹配子表的記錄為空
When you specify the constraint, you cannot specify the ON DELETE SET NULL clause of the references_clause.
 
---所引用的父表必須是分區(qū)表;且父表分表方法不能是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.
 
---引用分區(qū)和父分區(qū)表所引用的外鍵及父鍵絕不能包含基于pl sql function or lob列的虛擬列
The foreign and parent keys cannot contain any virtual columns that reference PL/SQL functions or LOB columns.
 

---引用分區(qū)的操作限制:

-----引用分區(qū)約束取決于父分區(qū)表的分區(qū)策略,即父分區(qū)是什么分區(qū)類型,它就隸屬于什么分區(qū)類型
Restrictions for reference partitioning are derived from the partitioning strategy of the parent table.
 
----iot,外部表,域索引存儲表不能使用引用分區(qū)
You cannot specify this clause for an index-organized table, an external table, or a domain index storage table.
 
----父分區(qū)表也可以是引用分區(qū),但約束不能是自包含
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.
 
----如父分區(qū)表啟用了行移動,引用分區(qū)也須啟行移動
If ROW MOVEMENT is enabled for the parent table, it must also be enabled for the child table.
 
 
向AI問一下細(xì)節(jié)

免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI