sql - 表定义中的多引用外键？

Question

概括

如何让非程序员更容易编写如下查询？

select
    table_name.*
  , foreign_table_1.name
  , foreign_table_2.name
from
  table_name
left outer join foreign_table foreign_table_1 on foreign_table_1.id = foreign_1_id
left outer join foreign_table foreign_table_2 on foreign_table_2.id = foreign_1_id
;

语境

我有这样的情况:

create table table_name (
    id integer primary key autoincrement not null
  , foreign_key_1_id integer not null
  , foreign_key_2_id integer not null
  , some_other_column varchar(255) null
);

create table foreign_table (
    id integer primary key autoincrement not null
  , name varchar(255) null
);

...其中 foreign_key_1_id和 foreign_key_2_id引用 foreign_table . (显然，这是简化和抽象的。)要查询并获取相应的值，我可能会执行以下操作:

select
    table_name.*
  , foreign_table_1.name
  , foreign_table_2.name
from
  table_name
left outer join foreign_table foreign_table_1 on foreign_table_1.id = foreign_1_id
left outer join foreign_table foreign_table_2 on foreign_table_2.id = foreign_1_id
;

(也就是说，连接中的别名 foreign_table 以正确链接事物。)这工作正常。但是，我的一些客户想使用 SQL Maestro查询表。该程序使用外键信息通过一个相当简单的界面(“Visual Query Builder”)链接表。例如，用户可以选择多个表，SQL Maestro 将填写连接，如下所示:


(来源: sqlmaestro.com)

(这是他们网站上的图表，仅用于说明。)

只要外键只引用一个表，这种策略就很有效。多重引用的情况似乎让它感到困惑，因为它会像这样生成 SQL:

SELECT 
  table_name.some_other_column,
  foreign_table.name
FROM
  table_name
  INNER JOIN foreign_table ON (table_name.foreign_key_1_id = foreign_table.id)
  AND (table_name.foreign_key_2_id = foreign_table.id)

...因为外键定义如下:

create table table_name (
    id integer primary key autoincrement not null
  , foreign_key_1_id integer not null
  , foreign_key_2_id integer not null
  , some_other_column varchar(255) null

  ---------------------------
  -- The part that changed:
  ---------------------------
  , foreign key (foreign_key_1_id) references foreign_table(id)
  , foreign key (foreign_key_2_id) references foreign_table(id)
);

create table foreign_table (
    id integer primary key autoincrement not null
  , name varchar(255) not null
);

这是个问题，因为你只能得到 1 foreign_table.name值返回，而通常有 2 个单独的值。

问题

我将如何定义外键来处理这种情况？ (这样做有可能还是有意义？我认为这不会对约束检查产生很大影响，所以我认为这是我找不到任何信息的原因。)我的结局目标是让我的客户自己轻松查询这些信息，虽然这种情况并非每天都发生，但每次出现时都必须帮助人们完成它是耗时/令人沮丧的。

如果没有办法以这种方式解决我的外键问题，你能提出任何替代方案吗？我已经有一些方法可以通过 View 让人们获得这些信息，但是人们通常需要比这更大的灵 active 。

Answer 1

在我看来，您的定义很好，并且 SQL Maestro 错误地将同一个表的两个外键解释为同一个外键，所以我会提醒他们注意这一事实，以便他们可以修复它。