Rails中的Mysql Constraign数据库条目 [英] Mysql Constraign Database Entries in Rails

问题描述

我使用Mysql 5和Ruby On Rails 2.3.1。我有一些验证，偶尔无法防止重复的项目保存到我的数据库。是否可以在数据库级别限制要根据某些参数创建重复条目？

I am using Mysql 5 and Ruby On Rails 2.3.1. I have some validations that occasionally fail to prevent duplicate items being saved to my database. Is it possible at the database level to restrict a duplicate entry to be created based on certain parameters?

我将电子邮件保存到数据库，并且不想保存重复的主题行，正文和发件人地址。有谁知道如何通过迁移对数据库施加这样的限制？

I am saving emails to a database, and don't want to save a duplicate subject line, body, and sender address. Does anyone know how to impose such a limit on a DB through a migration?

推荐答案

您有多个选项可确保将唯一值集插入到表中。让我们考虑1）将责任推送到数据库引擎或2）应用程序的责任。

You have a number of options to ensure a unique value set is inserted into your table. Lets consider 1) Push responsibility to the database engine or 2) your application's responsibilitiy.

向数据库引擎推送责任可能需要在表上创建UNIQUE索引。请参见 MySql创建索引语法。请注意，此解决方案可能会导致在插入重复值的情况下抛出异常。因为你已经确定我推断是三列以确定唯一性（主题行，主体和发件人地址），所以您将创建包含所有三列的索引。自从我与Rails合作以来，已经有一段时间了，因此您可能想要检查插入的记录数。

Pushing responsibility to the database engine could entail the use of creating a UNIQUE index on your table. See MySql Create Index syntax. Note, this solution may result in an exception thrown in case a duplicate value is inserted. As you've identified what I infer to be three columns to determine uniqueness (subject line, body, and sender address) you'll create the index to include all three columns. Its been a while since I've worked with Rails so you may want to check the record count inserted as well.

如果您希望将此职责推送到应用程序软件您需要应对潜在的数据插入冲突。也就是说，假设你有两个用户同时创建一个电子邮件（只在我这里工作），具有相同的主题行，正文和发送地址。如果您的代码对包含文本（在本例中为两个用户都相同）的任何记录进行简单查询，则两者都不会返回任何记录，并会继续插入他们现在违反您的前提的电子邮件。因此，您可以通过表锁或数据库中的其他同步字段来解决这个问题，以确保不会出现重复项。后一种方法可以由具有单个字段的另一个表指示是否有人正在插入记录，一旦完成，它更新该记录以表明其已经完成，然后其他人可以继续。

If you desire to push this responsibility to your application software you'll need to contend with potential data insertion conflicts. That is, assume you have two users creating an email simultaneously (just work with me here) having the same subject line, body, and send address. Should your code simple query for any records consisting of the text (identical for both users in this example) both will return no records found and will proceed along merily inserting their emails which now violate your premise. So, you can address this with perhaps a table lock, or some other syncing field in the database to ensure duplicates don't appear. This latter approach could consist of another table with a single field indicating if someone is inserting a record or not, once completed it updates that record to state it has completed and then others can proceed.

虽然你可以对每个选项的含义进行单独的架构讨论，我将把它留给一个单独的帖子。希望这足以回答您的问题。

While there you can have a separate architectural discussion on the implications of each alternative I'll leave that to a separate post. Hopefully this suffices in answering your question.

这篇关于Rails中的Mysql Constraign数据库条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！