不允许在MySQL中使用反向组合主键 [英] Don't allow reversed composite primary key in MySQL

问题描述

我正在开发一个应用程序，该应用程序需要保存有关两个城市之间的距离的数据.

I'm developing an application which needs to hold data about distances between two cities.

我在Mysql数据库中创建了一个距离表，其中包含两个城市的名称以及它们之间的距离.我已将两个城镇列作为复合主键.

I have created a distance table in the Mysql database which holds the name of the two cities and the distance between them. I have made the two town columns a composite primary key.

我希望数据库限制应用程序进行重复的反向输入，如屏幕快照所示，以防止具有不同的距离值.

I'd like the database to restrict the application from making duplicated reversed entries like shown on the screenshot to prevent having different distance values.

解决这个问题的最佳解决方案是什么?

What would be the best solution to solve this problem?

推荐答案

您可以创建一个存储过程以插入到该表中.

You could create a stored procedure to insert into this table.

DELIMITER $$
CREATE PROCEDURE insert_distance(IN p_town1 varchar(50), IN p_town2 varchar(50), IN p_distance int)
BEGIN
INSERT INTO distance(town1, town2, distance)
SELECT LEAST(p_town1, p_town2), GREATEST(p_town1, p_town2), p_distance;
END $$
DELIMITER ;

仅使用此过程插入，即可确保当该条目已经存在时，将引发错误.而且您不会以错误的顺序意外插入城镇.

Using only this procedure to insert you make sure, that an error is thrown, when the entry already exists. And you don't insert the towns accidently in the wrong order.

这篇关于不允许在MySQL中使用反向组合主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！