确保主键和外键永不相交的最佳方法是什么？

Say I have a table with a primary key a_id and foreign key b_id.

假设我有一个主键a_id和外键b_id的表。

It is important that a_id and b_id never intersect, i.e. it should never be the case that there exists an a_id = b_id.

重要的是a_id和b_id永远不会相交,即永远不应该存在a_id = b_id。

What would be the best way to implement this? Could I somehow do this on the database end (mySql), or should this be ensured programmatically? If I ensure this programmatically, is it bad practice to insert a number for a primary key (rather than have the database autoincrement it)? I assume I would just create a routine that checks the latest primary key and simply increments it (and I would also need to ensure that the range of a_id never intersects the range of b_id). Any suggestions?

实现这个的最佳方法是什么?我可以在数据库端(mySql)以某种方式执行此操作,还是应该以编程方式确保?如果我以编程方式确保这一点,那么为主键插入一个数字(而不是让数据库自动增加它)是不好的做法吗?我假设我只是创建一个例程来检查最新的主键并简单地递增它(我还需要确保a_id的范围永远不会与b_id的范围相交)。有什么建议?

5 个解决方案

#1

You could create another table that has the auto-increment field. Upon insertion of a record, it would insert into that "key table" and use the referenced values there. So if you have two globally unique keys in one table each insert would be two key inserts. This solution would scale beyond 2 as well.

您可以创建另一个具有自动增量字段的表。插入记录后,它将插入到“密钥表”中并在那里使用引用的值。因此,如果一个表中有两个全局唯一键,则每个插入都是两个键插入。该解决方案也将扩展到2以上。

But I have to ask: Why?

但我不得不问:为什么?

#2

In Oracle you could implement this behavior using a sequence that was used to assign id's to rows in both tables. Each table would have a trigger on INSERT where the id would be set from the next number in the sequence.

在Oracle中,您可以使用用于将id分配给两个表中的行的序列来实现此行为。每个表在INSERT上都有一个触发器,其中id将从序列中的下一个数字设置。

#3

You can set the AUTO_INCREMENT value such that one table has only odd numbers and the other has only evens.

您可以设置AUTO_INCREMENT值,使一个表只有奇数,另一个表只有均数。

This is likely to be very performant, but it doesn't leave room for adding a 3rd table with yet another unique key.

这可能非常高效,但它不会为添加另一个唯一键的第3个表留下空间。

I'm not 100% sure about MySQL, but with Oracle you can define a sequence, then simply use that same sequence to select all of your values from, which is probably the best option (if it is available with mysql).

我不是百分之百确定MySQL,但是使用Oracle你可以定义一个序列,然后只需使用相同的序列来选择你的所有值,这可能是最好的选择(如果它可用于mysql)。

#4

You could use GUIDs (UUIDs) as unique keys.

您可以使用GUID(UUID)作为唯一键。

I have used this on several projects and it works well for uniqueness, although it isn't the best for index performance.

我已经在几个项目中使用了它,并且它对于唯一性很有效,尽管它不是最好的索引性能。

#5

The easiest way to achieve this would be to use a CHECK constraint. Unfortunately, MySQL being MySQL, it doesn't support CHECK.

实现此目的的最简单方法是使用CHECK约束。不幸的是,MySQL是MySQL,它不支持CHECK。

In order to achieve the same effect in MySQL, you will need to create a BEFORE INSERT and a BEFORE UPDATE trigger to make sure that both values are valid. The FK itself is in charge of making sure the relationship is valid. Here is an example:

为了在MySQL中实现相同的效果,您需要创建BEFORE INSERT和BEFORE UPDATE触发器以确保两个值都有效。 FK本身负责确保关系有效。这是一个例子:

CREATE TRIGGER upd_check BEFORE UPDATE ON sometable
FOR EACH ROW
BEGIN
    IF NEW.a_id = NEW.b_id THEN
        call ERROR_SELFREFERENCING_ID();
    END IF;
END;

More information about MySQL TRIGGERS are available in the MySQL Manual:

有关MySQL TRIGGERS的更多信息,请参阅MySQL手册:

18.3.1: Trigger Syntax

18.3.1:触发语法

EDIT: MySQL doesn't currently support RAISE or SIGNAL in their triggers, so I have to resort to calling a non-existent procedure ERROR_SELFREFERENCING_ID() for it to fail. This will cause the INSERT or UPDATE to fail if a_id = b_id, pretty much the same way if you would set an invalid b_id.

编辑:MySQL目前不支持其触发器中的RAISE或SIGNAL,因此我不得不求助于调用一个不存在的过程ERROR_SELFREFERENCING_ID()来使其失败。如果a_id = b_id,这将导致INSERT或UPDATE失败,如果设置无效的b_id,则几乎相同。

#1