Oracle 中的嵌套表主键和外键

发布于 2024-12-28 20:47:20 字数 919 浏览 1 评论 0原文

我正在尝试向嵌套表添加主键和外键，但很难知道如何操作。

这就是我所拥有的；

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;

现在问题是这部分，尝试执行外键 -

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

给出此错误 -

*命令中从第 3 行开始出错：
更改表 profile_storage_tbl 添加约束 fk_userID 外键（用户 ID）参考用户(id)
错误报告：
SQL 错误：ORA-30730：嵌套表列上不允许引用约束 30730.00000 - “嵌套表列不允许引用约束”
*原因：尝试定义嵌套的引用约束表格列。
操作：不要对嵌套表列指定引用约束。

原文

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

This is what I have;

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

Gives this error -

*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.