Oracle中的嵌套表主键和外键 [英] Nested table primary and foreign key in Oracle

问题描述

我试图将主键和外键添加到嵌套表中，但想知道如何做.

I'm trying to add a primary and foreign key to a nested table, struggling to know how.

这就是我所拥有的；

create or replace type profile as object 
(
id VARCHAR2(10), --- Suppose to be Primary Key
userID VARCHAR2(10) --- Suppose to be Foreign Key for user table
);

create or replace type profile_nest as table of profile;

CREATE OR REPLACE TYPE user_t UNDER group_T
(profile profile_nest_ty,);


CREATE TABLE user OF user_t
(id NOT NULL,
PRIMARY KEY (id), 
nested table profile store as profile_storage_tbl;

现在的问题是这部分，试图做一个外键-

Now the problem is this part, trying to do a foreign key -

alter table profile_storage_tbl add CONSTRAINT fk_userID FOREIGN KEY (userID)
REFERENCES user(id);

给出此错误-

*在命令的第3行开始出现错误:
更改表profile_storage_tbl添加CONSTRAINT fk_userID FOREIGN KEY(用户ID) 参考用户(id)
错误报告:
SQL错误:ORA-30730:嵌套表列上不允许引用约束 30730. 00000-嵌套表列上不允许引用约束"
*原因:试图在嵌套中定义引用约束 表列.
操作:不要在嵌套表列上指定引用约束.

*Error starting at line 3 in command:
alter table profile_storage_tbl add CONSTRAINT fk_userID FOREIGN KEY (userID) REFERENCES user(id)
Error report:
SQL Error: ORA-30730: referential constraint not allowed on nested table column 30730. 00000 - "referential constraint not allowed on nested table column"
*Cause: An attempt was made to define a referential constraint on a nested table column.
Action: Do not specify referential constraints on nested table columns.

推荐答案

正如异常文本所述，不允许在嵌套表列上创建外键约束(Oracle 11).

It is just as the exception text says, creating a foreign key constraint on nested table columns is not allowed (Oracle 11).

此处介绍了一种解决方法: http://ksun-oracle.blogspot.com/2011/05/foreign-key-on-nested-table.html .但是并不能保证这将在下一个oracle版本中起作用.

There is sort of a workaround described here: http://ksun-oracle.blogspot.com/2011/05/foreign-key-on-nested-table.html. But there is no guarantee, that this would work on the next oracle release.

这篇关于Oracle中的嵌套表主键和外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！