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);

给出此错误-

推荐答案

正如异常文本所述，不允许在嵌套表列上创建外键约束(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中的嵌套表主键和外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！