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.
