如何用来自另一个表的列的值约束一个列?

This isn't a big deal, but my OCD is acting up with the following problem in the database I'm creating. I'm not used to working with databases, but the data has to be stored somewhere...

这不是什么大问题，但是我的OCD在我创建的数据库中处理了以下问题。我不习惯使用数据库，但数据必须存储在某处……

Problem

问题

I have two tables A and B.

我有两个表A和B。

One of the datafields is common to both tables - segments. There's a finite number of segments, and I want to write queries that connect values from A to B through their segment values, very much asif the following table structure was used:

其中一个datafields对两个表都是通用的。段的数量是有限的，我想写一些查询，通过它们的段值将值从a连接到B，非常类似于下面的表格结构:

However, as you can see the table Segments is empty. There's nothing more I want to put into that table, rather than the ID to give other table as foreign keys. I want my tables to be as simple as possible, and therefore adding another one just seems wrong.

但是，正如您所看到的，表段是空的。我只想把它放到表中，而不是把其他表作为外键。我希望我的表尽可能简单，因此添加另一个表似乎是错误的。

Note also that one of these tables (A, say) is actually master, in the sense that you should be able to put any value for segment into A, but B one should first check with A before inserting.

还请注意，这些表(A，比方说)中有一个实际上是master，从这样的意义上说，您应该能够将段的任何值放入A中，但是在插入之前应该先与A进行检查。

EDIT I tried one of the answers below:

编辑我尝试了以下的一个答案:

create table A(
    id int primary key identity,
    segment int not null
)

create table B(
    id integer primary key identity,
    segment int not null
)

--Andomar's suggestion
alter table B add constraint FK_B_SegmentID
    foreign key (segment) references A(segment)

This produced the following error.

这产生了以下错误。

Maybe I was somehow unclear that segments is not-unique in A or B and can appear many times in both tables.

也许我不太清楚片段在A或B中不是唯一的，并且可能在两个表中出现多次。

Msg 1776, Level 16, State 0, Line 11 There are no primary or candidate keys in the referenced table 'A' that match the referencing column list in the foreign key 'FK_B_SegmentID'. Msg 1750, Level 16, State 0, Line 11 Could not create constraint. See previous errors.

Msg 1776，第16级，状态0，第11行在引用表“A”中没有主键或候选键，它们与外键“FK_B_SegmentID”中的引用列列表匹配。Msg 1750, 16级，状态0，第11行不能创建约束。见以前的错误。

4 个解决方案

#1

You can ensure the segment exists in A with a foreign key:

您可以使用外键确保段存在A中:

alter table B add constraint FK_B_SegmentID
    foreign key (SegmentID) references A(SegmentID)

To avoid rows in B without a segment at all, make B.SegmentID not nullable:

为了避免B中没有段的行，令B。SegmentID不是可以为空:

alter table B alter column SegmentID int not null

There is no need to create a Segments table unless you want to associate extra data with a SegmentID.

不需要创建分段表，除非您希望将额外的数据与分段关联。

#2

You can create a foreign key relationship directly from B.SegmentID to A.SegmentID. There's no need for the extra table.

您可以直接从B创建一个外键关系。SegmentID A.SegmentID。不需要额外的桌子。

Update: If the SegmentIDs aren't unique in TableA, then you do need the extra table to store the segment IDs, and create foreign key relationships from both tables to this table. This however is not enough to enforce that all segment IDs in TableB also occur in TableA. You could instead use triggers.

更新:如果分段在表a中不是唯一的，那么需要额外的表来存储段id，并创建从两个表到此表的外键关系。然而，这还不足以强制表b中的所有段id也出现在表a中。你可以使用触发器。

#3

As Andomar and Mark Byers wrote, you don't have to create an extra table. You can also CASCADE UPDATEs or DELETEs on the master. Be very carefull with ON DELETE CASCADE though!

正如Andomar和Mark Byers所写，您不必创建一个额外的表。还可以对主服务器进行级联更新或删除。不过要非常小心的删除级联!

For queries use a JOIN:

查询请使用连接:

SELECT *
  FROM A
  JOIN B ON a.SegmentID = b.SegmentID

Edit:

You have to add a UNIQUE constraint on segment_id in the "master" table to avoid duplicates there, or else the foreign key is not possible. Like this:

您必须在“master”表中对segment_id添加唯一的约束，以避免在那里重复，否则不可能使用外键。是这样的:

ALTER TABLE A ADD CONSTRAINT UNQ_A_SegmentID UNIQUE (SegmentID);

#4

If I've understood correctly, a given segment cannot be inserted into table B unless it has also been inserted into table A. In which case, table A should reference table Segments and table B should reference table A; it would be implicit that table B ultimately references table Segments (indirectly via table A) so an explicit reference is not required. This could be done using foreign keys (e.g. no triggers required).

如果我理解正确，一个给定的段不能插入到表B中，除非它也被插入到表a中。在这种情况下，表a应该引用表段，表B应该引用表a;表B最终引用表段(通过表A间接引用)是隐含的，因此不需要显式引用。这可以使用外键完成(例如，不需要触发器)。

Because table A has its own key I assume a given segment_ID can appear in table A more than once, therefore for B to be able to reference the segment_ID value in A then a superkey would need to be defined on the compound of A_ID and segment_ID. Here's a quick sketch:

因为表A有它自己的键，所以我假设一个给定的segment_ID可以在表中多次出现，因此B可以在A中引用segment_ID值，然后需要在A_ID和segment_ID的复合上定义超键。这是一个快速草图:

CREATE TABLE Segments 
(
 segment_ID INTEGER NOT NULL UNIQUE
);

CREATE TABLE A 
(
 A_ID INTEGER NOT NULL UNIQUE, 
 segment_ID INTEGER NOT NULL
    REFERENCES Segments (segment_ID), 
 A_data INTEGER NOT NULL, 
 UNIQUE (segment_ID, A_ID)  -- superkey
);

CREATE TABLE B 
(
 B_ID INTEGER NOT NULL UNIQUE, 
 A_ID INTEGER NOT NULL, 
 segment_ID INTEGER NOT NULL, 
 FOREIGN KEY (segment_ID, A_ID)
    REFERENCES A (segment_ID, A_ID), 
 B_data INTEGER NOT NULL
);