Don't allow reversed composite primary key in

问题:

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

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?

回答1:

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.

回答2:

MariaDB, a MySQL fork, offers virtual columns. If you were using MariaDB, you could create such a virtual column and declare it to be unique.

But, you probably don't have MariaDB (yet). So it's going to be hard for you to implement the business rule that prevents duplicate entries. You certainly could use a lookup scheme that would systematically ignore the prohibited entries (in which town_2 < town_1).

 SELECT town_1, town_2, distance
   FROM distance
  WHERE town_2 > town_1

will retrieve all the valid entries. This query will retrieve the distance between two towns furnished as query parameters even if the two town names are offered in the wrong order. (The monkey business with the JOIN allows each town name parameter to be presented only once to the query, even though it is used more than once.)

SELECT town_1, town_2, d.distance
  FROM (
    SELECT ? AS a, ? AS b
  ) cities
  JOIN distance AS d ON
       ( town_1 = LEAST(a,b) AND town_2 = GREATEST(a,b))