postgresql中的双外键

发布于 2025-01-05 08:10:21 字数 674 浏览 1 评论 0原文

我正在尝试使用双主键作为外键。

Create table AAA (
   AAA_id int primary key
);

create table BBB (
   AAA_id int,
   BBB_name character varying(20),
   primary key (AAA_id, BBB_name)
);

create table CCC (
    AAA_id,
    BBB_name,
    DDD_id,

    ... ???
);

表 AAA 是一个对象

表 BBB 与 AAA 是多对一的，并且保存 AAA 的别名

我正在尝试创建一个数据透视表 CCC，它在 DDD 和 BBB 之间保存多对一。

我想我想要类似

create table CCC (
    AAA_id,
    BBB_name,
    DDD_id,
    foreign key (AAA_id, BBB_name) references BBB(AAA_id, BBB_name) on update cascade
);

AAA_id 和 BBB_name 都是外键的东西，但它们也总是引用 BBB 中的同一行。

但这当然是无效的。在 postgreSQL 中产生这种行为的最佳方法是什么？

原文

I am trying to use a double primary key as a foreign key.

Create table AAA (
   AAA_id int primary key
);

create table BBB (
   AAA_id int,
   BBB_name character varying(20),
   primary key (AAA_id, BBB_name)
);

create table CCC (
    AAA_id,
    BBB_name,
    DDD_id,

    ... ???
);

table AAA is an object

table BBB is many to one with AAA, and holds aliases of AAA

I am trying to create a pivot table, CCC which holds a many to one between DDD and BBB.

I guess I want something like

create table CCC (
    AAA_id,
    BBB_name,
    DDD_id,
    foreign key (AAA_id, BBB_name) references BBB(AAA_id, BBB_name) on update cascade
);

where both AAA_id and BBB_name are foreign keys, but they are also always referring to the same row in BBB.

but of course that's not valid. what is the best way to produce this type of behavior in postgreSQL?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

楠木可依 2025-01-12 08:10:21

Create temp table AAA (
   AAA_id int primary key
);

create temp table BBB (
   AAA_id int not null references AAA (AAA_id),
   BBB_name character varying(20) not null,
   primary key (AAA_id, BBB_name)
);

create temp table CCC (
    AAA_id int not null,
    BBB_name character varying(20) not null,
    DDD_id integer not null,
    -- Guessing at the primary key.
    primary key (AAA_id, BBB_name, DDD_id),
    foreign key (AAA_id, BBB_name) references BBB (AAA_id, BBB_name) 
        on update cascade
);

由于 {AAA_id, BBB_name} 唯一标识 BBB 中的一行，因此 CCC 中的外键 {AAA_id, BBB_name} 也将引用 BBB 中的一个唯一行。

Create temp table AAA (
   AAA_id int primary key
);

create temp table BBB (
   AAA_id int not null references AAA (AAA_id),
   BBB_name character varying(20) not null,
   primary key (AAA_id, BBB_name)
);

create temp table CCC (
    AAA_id int not null,
    BBB_name character varying(20) not null,
    DDD_id integer not null,
    -- Guessing at the primary key.
    primary key (AAA_id, BBB_name, DDD_id),
    foreign key (AAA_id, BBB_name) references BBB (AAA_id, BBB_name) 
        on update cascade
);

Since {AAA_id, BBB_name} uniquely identify a row in BBB, the foreign key {AAA_id, BBB_name} in CCC will also reference one unique row in BBB.

回复收藏 0 原文

~没有更多了~