一个外键引用多个主键

时间：2021-12-09 11:31:45

I have two tables with single primary key columns in each of those two tables and another table (table 3) which has a foreign key that refers both of those above primary key's columns.

我有两个表,在这两个表的每一个中都有一个主键列,另一个表(表3)有一个外键,它引用了主键上面的两个列。

Now I want to insert records into table 3 if it present in either of those two primary key tables.

现在我想将记录插入到表3中,如果它存在于这两个主键表中的任何一个中。

Note: I don't want to create a new table that is combination of primary key tables and add reference to that newly created table

注意:我不想创建一个新表,它是主键表的组合,并添加对新创建的表的引用

2 个解决方案

#1

2  

As Erwin Brandstetter stated here

正如Erwin Brandstetter在这里所说的那样

Rules for FK constraints

To answer the question in the title and at the end of your text:

要回答标题和文字末尾的问题:

"I would still like to know how to have one foreign key referencing two primary keys."

“我仍然想知道如何让一个外键引用两个主键。”

That's impossible.

  • A FOREIGN KEY constraint can only point to one table and each table can only have one PRIMARY KEY constraint.

    FOREIGN KEY约束只能指向一个表,每个表只能有一个PRIMARY KEY约束。

  • Or you can have multiple FOREIGN KEY constraints on the same column(s) referencing one PRIMARY KEY of a (different) table each. (Rarely useful.)

    或者,您可以在同一列上有多个FOREIGN KEY约束,每个引用一个(不同)表的一个PRIMARY KEY。 (很少用。)

However, a single PK or FK can span multiple columns.
And a FK can reference any explicitly defined unique (set of) column(s) in the target, not just the PK. The manual:

但是,单个PK或FK可以跨越多个列。并且FK可以引用目标中任何明确定义的唯一(一组)列,而不仅仅是PK。手册:

A foreign key must reference columns that either are a primary key or form a unique constraint.

外键必须引用作为主键或形成唯一约束的列。

A multicolumn PK or UNIQUE constraint can only be referenced by a multicolumn FK constraint with matching column types.

多列PK或UNIQUE约束只能由具有匹配列类型的多列FK约束引用。

Basic advice:

#2

-1  

insert into table3 (col1, col2 ...)
(select col1, col2 ... from table1
union
select col1, col2 ... from table2);

You can optionally put where clauses or split the SQL into 2, instead of a union.

您可以选择放置where子句或将SQL拆分为2,而不是联合。

This is standard ANSI SQL and should work on any DBMS

这是标准ANSI SQL,应该适用于任何DBMS

#1

2  

As Erwin Brandstetter stated here

正如Erwin Brandstetter在这里所说的那样

Rules for FK constraints

To answer the question in the title and at the end of your text:

要回答标题和文字末尾的问题:

"I would still like to know how to have one foreign key referencing two primary keys."

“我仍然想知道如何让一个外键引用两个主键。”

That's impossible.

  • A FOREIGN KEY constraint can only point to one table and each table can only have one PRIMARY KEY constraint.

    FOREIGN KEY约束只能指向一个表,每个表只能有一个PRIMARY KEY约束。

  • Or you can have multiple FOREIGN KEY constraints on the same column(s) referencing one PRIMARY KEY of a (different) table each. (Rarely useful.)

    或者,您可以在同一列上有多个FOREIGN KEY约束,每个引用一个(不同)表的一个PRIMARY KEY。 (很少用。)

However, a single PK or FK can span multiple columns.
And a FK can reference any explicitly defined unique (set of) column(s) in the target, not just the PK. The manual:

但是,单个PK或FK可以跨越多个列。并且FK可以引用目标中任何明确定义的唯一(一组)列,而不仅仅是PK。手册:

A foreign key must reference columns that either are a primary key or form a unique constraint.

外键必须引用作为主键或形成唯一约束的列。

A multicolumn PK or UNIQUE constraint can only be referenced by a multicolumn FK constraint with matching column types.

多列PK或UNIQUE约束只能由具有匹配列类型的多列FK约束引用。

Basic advice:

#2

-1  

insert into table3 (col1, col2 ...)
(select col1, col2 ... from table1
union
select col1, col2 ... from table2);

You can optionally put where clauses or split the SQL into 2, instead of a union.

您可以选择放置where子句或将SQL拆分为2,而不是联合。

This is standard ANSI SQL and should work on any DBMS

这是标准ANSI SQL,应该适用于任何DBMS

标签：

相关文章