单个列上的 FK 引用复合 PK 中的列

2022-01-20 00:00:00 foreign-keys oracle database-design

无法创建/查找逻辑以在子表中引用父表的复合 PK 中的列的列上应用 FK.

Not able to create /find the logic to apply FK on a column in child table referencing a column from composite PK of parent table.

create table product(prod_id number,
    prod_name varchar2(20),
    price number,
    constraint PK12 primary key(prod_id,prod_name));

表已创建.

create table purchase(prod_id number,
    purchase_price number,
    constraint FK12 foreign key(prod_id) references product(prod_id));
create table purchase(prod_id number,
    purchase_price number,
    constraint FK12 foreign key(prod_id) references product(prod_id))



ERROR at line 1:
ORA-02270: no matching unique or primary key for this column-list

Kinldy 建议我如何结合这个逻辑.

Kinldy suggest how i can incorporate this logic.

谢谢.

推荐答案

你不能.

正如错误所说，该列列表没有匹配的主键；你必须拥有一个.您有三个选择:

As the error says there's no matching primary key for that column list; you must have one. You have three options:

  1. 从 PRODUCT 的主键中删除 PROD_NAME.从表面上看，这似乎是合乎逻辑的解决方案，如果为了使主键唯一，这不是必需的.

  1. Remove PROD_NAME from the primary key of PRODUCT. On the face of it this seems like the logical solution, if this is not required in order to make the primary key unique.

将 PROD_NAME 添加到 PURCHASE 表中.

Add PROD_NAME to the PURCHASE table.

在 PURCHASE.PROD_ID 上创建唯一索引.如果它无论如何都是主键候选者，这似乎有点过分了.

Create a unique index on PURCHASE.PROD_ID. This seems excessive if it would be a primary key candidate anyway.

相关文章