blocks|key|1953132|text|你会犯错误的PHP，100%25保证。PHP是程序性的。您想要的是声明性约束。您需要告诉整个堆栈：“这些是数据上的约束，不能违反这些约束。”您不想过多地使用“步骤1.步骤2.步骤3.步骤432+.”作为对数据强制约束的方法，因为|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1953133|你会搞错的|unordered-list-item|1953134|当你以后改变它的时候，你会忘记你现在所做的一切。|1953135|没有人会像你现在知道的那样知道所有这些隐含的约束，包括你未来的自我。|1953136|需要大量的代码才能正确地执行约束，而且一直都是--数据库服务器已经有了这些代码，但是您准备好编写它了吗？|1953137|实际上，问题应该是，“为什么我应该使用PHP来执行这些约束，而我只需要使用MySQL呢？”|1953138|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$]]|$1|E|3|F|5|D|7|Q|8|@]|9|@]|A|$]]|$1|G|3|H|5|D|7|R|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|S|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|T|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|U|8|@]|9|@]|A|$]]]|N|$]]

You are going to make mistakes with PHP, 100% guaranteed. PHP is procedural. What you want are declarative constraints. You want to tell the entire stack: "These are the constraints on the data, and these constraints cannot be violated." You don't want to much around with "Step 1 ... Step 2 ... Step 3 ... Step 432 ...", as your method of enforcing constraints on data, because

<ul>
<li>you're going to get it wrong</li>
<li>when you change it later, you will forget what you did now</li>
<li>nobody else will know all of these implicit constraints like you know them now, and that includes your future self</li>
<li>it takes a lot of code to enforce constraints properly and all the time - the database server has this code already, but are you prepared to write it?</li>
</ul>

The question should actually be worded, "Why should I use PHP to enforce these constraints, when I could just do it with MySQL?"

blocks|key|419113|text|它们是非常重要的。您不希望完全通过PHP来定义模型。如果您的PHP代码中有错误怎么办？您可以很容易地在业务规则中声明不应该使用空列。通过在数据库级别定义它，您至少可以免费获得该检查。如果您的PHP中有bug，或者任何其他工具曾经使用过您的数据库，您会非常讨厌它。你只是在问问题，IMHO。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|419114|请注意，这是故事的很短的版本。|419115|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

They are quite important. You don't want to define your model entirely through PHP. What if there is a bug in your PHP code? You could easily have null'ed columns where your business rules state you should not. By defining it at the database level, you at least get that check for free. You're going to really hate it when there are bugs in your PHP or if any other tool ever uses your database. You're just asking for problem, IMHO.

Be advised, this is the very short version of the story.

blocks|key|1953180|text|在数据库中实现约束是很重要的，因为不可能预测未来！你永远不知道什么时候你的需求会改变。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1953181|还可以考虑让多个开发人员处理同一个应用程序的可能性。您可能知道所有的约束是什么，但是初级开发人员可能不知道。使用数据库上的约束，初级开发人员的代码将生成一个错误，并且他会知道需要修复一些东西。没有约束，代码可能不会失败，数据可能会损坏。|1953182|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

It's important to implement constraints in the database because it's impossible to predict the future! You just never know when your requirements will change. 

Also consider the possibility that you may have multiple developers working on the same application. You may know what all the constraints are, but a junior developer may not. With constraints on the database, the junior developer's code will generate an error, and he'll know that something needs to be fixed. Without the constraints, the code may not fail, and the data could get corrupt.

blocks|key|742873|text|如果您可以为您的生命发誓，任何东西都不会访问DB，尽管有任何其他方法，那么您的PHP页面(当然是没有错误的)就可以单独使用PHP了。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|742874|由于真实世界的场景总是包含一些不确定性，所以让DB服务器监视数据的完整性是很好的。|742875|对于简单的数据库来说，引用完整性约束可能不是绝对的要求，而是一个很好的选择。应用程序越复杂，您可以从它们中得到更多的好处。提前计划会让你以后的生活更轻松。|742876|此外，引用完整性在迫使您以一种更按书的方式设计数据库方面起着重要作用，因为并不是每一次肮脏的攻击都是可能的。这也是一件好事。|742877|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|L|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|M|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

If you can swear for the life of you that nothing will ever access the DB though any other means then your (of course bug-free) PHP page, then doing it with PHP alone will be fine.

Since real-world scenarios always contain some uncertainty, it is good to have the DB server watching the integrity of your data.

For simple databases, referential integrity constraints might not be an absolute requirement, but a nice-to-have. The more complex the application gets, the more benefit can you draw from them. Planning them in early makes your life easier later. 

Additionally, referential integrity does it's part in forcing you to design the database in a more by-the-book manner, because not every dirty hack is possible anymore. This is also a good thing.

blocks|key|742887|text|您不能“只”使用PHP，原因与程序员“只是”不能编写无bug代码的原因相同。比你想象的要难。尤其是如果你觉得没那么难的话。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|742888|entityMap|0|LINK|mutability|MUTABLE|url|http://en.wikipedia.org/wiki/PHP^0|8|3|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

You can't "just" do it with <a href="http://en.wikipedia.org/wiki/PHP" rel="noreferrer">PHP</a> for the same reason that programmers "just" can't write bug-free code. It's harder than you think. Especially if you think it's not that hard.

blocks|key|419220|text|对我来说，使用NULL最重要的是文档部分。当我在几个月后返回项目时，我忘记了在哪些列中包含空值是可以接受的。如果列说NULL，那么我知道我永远不必处理其中的潜在null值。如果它允许为null，那么我肯定知道我必须处理它们。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|419221|另一件事是，正如其他人所指出的:你可能在某个地方错过了一些东西，而清理数据则很糟糕，或者完全不可能。最好确保数据库中的所有数据都是一致的。|419222|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

The most important thing about using NOT NULL to me, is more the documentation part. When I return to the project after a few months I forget which columns it is acceptable to have nulls in. If the column says NOT NULL, then I know I will never ever have to deal with potential null values from it. And if it allows null, then I know for sure I have to deal with them.

The other thing is, as others have noted: You may miss something somewhere, and cleaning up data sucks, or may be entirely impossible. It's better to know for sure that all data in your database is consistent.

blocks|key|1953370|text|在MySQL中启用这些约束所需的时间几乎为零。如果由于PHP或其他代码的错误，它们甚至可以将您从一个bug中拯救出来，难道这不值得吗？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1953371|请记住，您将拯救自己的bug是相当令人讨厌的。查找和修复bug本身可能并不困难；最让人讨厌的是，一旦修复了bug，就会留下一堆错误的数据，这些数据甚至可能无法挽救。|1953372|我甚至不会从“PHP以外的东西可能某天会访问您的数据”的角度来处理这个问题。这是真的，但更重要的是，在我的脑海中，头痛，时间(金钱)和数据损失，你可以节省你自己，只需添加一些简单的约束。|1953373|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

Enabling these constraints in MySQL takes almost zero time. If they save you from even a single bug due to faulty PHP or other code, isn't that worth it?

Keep in mind that the sorts of bugs you'll save yourself from can be rather nasty. Finding and fixing the bug itself may not be hard; the nasty part is that once you've fixed the bug you'll be left with a bunch of faulty data that may not even be salvageable.

I wouldn't even approach this problem from the "well, something other than PHP might access your data someday" angle. That's true, but even more important in my mind are the the headaches, time (money) and data loss that you can save yourself simply by adding a few simple constraints.

blocks|key|1953388|text|使用数据库实现结构数据完整性，其余部分使用BR层。并尽早捕获错误。他们一起工作。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1953389|幸运的是，当您的代码成熟时，您将不会遇到数据库RI错误；您可以自豪地宣布自己是第一个。|1953390|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

Use the database for structural data integrity, and use the BR layer for the rest. And catch errors as early as possible. They work together.

With luck, when your code as matured, you won't experience databse RI errors; and you can proudly announce yourself to be the first.

blocks|key|1953414|text|我认为您不能确定您的数据库将只通过PHP访问，如果是的话，开发人员将使用它来尊重数据库的整个生命周期中的这些约束。|type|ordered-list-item|depth|inlineStyleRanges|entityRanges|data|1953415|如果您在模式中包含了这些约束，那么通过研究您的模式，您可以很好地了解数据是如何使用和关联的。如果您只将所有这些都放在代码中，那么就必须有人同时查看数据库和PHP代码。|1953416|但这些不应该出现在设计文档、数据字典和逻辑数据库设计中吗？|unstyled|1953417|是的，但是这些文件因为过时和陈旧而臭名昭著。我知道您永远不会允许这样的情况发生，但是一些对项目没有那么严格的经验的人可能会认为您的项目是这样的，并且希望参考实际的代码和模式，而不是文档。|1953418|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$]]|$1|G|3|H|5|F|7|N|8|@]|9|@]|A|$]]|$1|I|3|-4|5|F|7|O|8|@]|9|@]|A|$]]]|J|$]]

<ol>
<li>I don't think you can be certain that your database will only be accessed by PHP and if so, by developers who will use it to respect those constraints for the entire lifecyle of your database.</li>
<li>If you include these constraints in your schema, then one can get a good idea of how the data is used and related by investigating your schema. If you only put all that in the code, then someone would have to look in both the database and the PHP code.</li>
</ol>

But shouldn't that stuff be in the design documentation, data dictionary, and logical database design?

Yes, but these documents are notorious for getting out of date and stale. I know you would never allow that to happen, but some people who have experience with projects with less discipline may assume this about your project, and want to consult the actual code and schema rather than documentation.

blocks|key|742977|text|我高度赞赏您的问题，因为我深信缺省值规则应该在代码端而不是在数据库端实现，这有一个非常简单的原因:当用户发起数据库更改(插入、选择和更新)时，这些更改应该集成所有业务规则，默认值基本上是业务规则：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|742978|没有发票号码的发票|unordered-list-item|742979|没有没有数量的发票行，0或空是不可接受的。|742980|没有收到的邮件没有接收日期。|742981|等|742982|几年前，我们决定去掉所有这些“数据库端”的人工制品，如"not+null“、”(不允许)允许空字符串“以及其他”默认值“技巧，而且它运行得很好。赞成默认值的参数主要是指一种“安全性”原则(“在数据库端执行它，因为您会忘记在代码端使用它/您的语言不适合在数据库端这样做”)，一旦您选择不在数据库端实现任何默认值，就没有任何意义了:只需检查您的业务规则在调试时是否正确实现。|742983|在过去的两年中，团队中没有人想到在表中声明默认值。我猜我们年轻的见习甚至不知道的一些叫做“默认值”的东西。|offset|length|style|BOLD|742984|编辑:在这里重读一些答案，我最后的评论是:在任何一方，无论是DB还是代码，都要这样做，但是要做出选择，只在一边做！没有什么比在双方都有这样的控制更危险的了，因为最终(1)你永远不会知道双方是否真的在执行相同的规则，这意味着(2)检查规则意味着检查双方，这真的会变得一团糟！当然，最糟糕的情况是，工作的一部分在数据库端完成(即创建数据库时标识的规则)，而另一部分(即新标识的规则)则是在客户端完成的。噩梦..。|742985|entityMap^0|0|0|0|0|0|0|Y|5|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$]]|$1|E|3|F|5|D|7|Y|8|@]|9|@]|A|$]]|$1|G|3|H|5|D|7|Z|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|10|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|11|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|12|8|@$O|13|P|14|Q|R]]|9|@]|A|$]]|$1|S|3|T|5|6|7|15|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|16|8|@]|9|@]|A|$]]]|V|$]]

I highly appreciate your question, as I am deeply convinced that default-value rules should be implemented on the code-side, not on the database-side, and this for a very simple reason: when users are the one that initiate database changes (INSERTS, SELECTS and UPDATES), these changes shall integrate all business rules, and default values are basically business rules:

<ul>
<li>There is no invoice without invoice number</li>
<li>There is no invoice line without a quantity, and 0 or nulls are not acceptable</li>
<li>There is no incoming mail without date of reception</li>
<li>etc</li>
</ul>

We have decided a few years ago to get rid of all these "database-side" artefacts like "not null", "(do not) allow empty strings", and other "default value" tricks, and it works perfectly. Arguments in favor of the default value mainly refer to a kind of "security" principle ("do it on the database side because you will forget to to it on the code side / your language is not made for that/it's easier to do it on the database side") that does not make any sense once you have chosen not to implement any default value on the database side: just check that your business rules are properly implemented while debugging.

For the last 2 years, nobody in the team ever thought of declaring a default value in a table. I guess that our younger trainee does not even know about something that is called "default value".

EDIT: rereading some of the answers here, my final comment would be: do it on any side, either DB or code, but make your choice and do it on one side only! There is nothing more dangerous than having such controls on both sides, because eventually (1) you'll never know if both sides are really implementing the same rule, meaning that (2) checking the rules will mean checking both sides, which can really become a mess! The worst situation is of course when one part of the job is done on the database side (ie the rules that were identified when the database was created) and the other part (ie the newly identitified rules) done on the client side ... nightmare ....

blocks|key|743482|text|我通常赞成在数据库中声明约束。支持限制的论据：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|743483|声明性代码比命令式代码更容易实现无bug。即使应用程序代码包含bug，也会强制执行约束。|unordered-list-item|743484|如果您有多个应用程序或代码模块访问同一个数据库，并且需要统一执行业务规则，则支持“不要重复自己”原则。如果你需要改变约束，你可以在一个地方做，即使你有很多应用程序。|743485|即使当人们试图绕过应用程序，使用特殊的查询工具来修补数据库时，也会增强数据的完整性。|743486|强制一致性，这意味着您可以始终确定数据在任何数据更新之前和之后都处于有效状态。如果不使用约束，则可能需要运行定期查询，以检查是否有中断的引用并清理它们。|743487|您可以使用约束轻松地对级联更新/删除进行建模。在应用程序代码中做同样的事情既复杂又低效，无法原子地应用更改(尽管建议使用事务隔离)，并且容易出现错误。|743488|约束有助于数据库进行更多的自文档化，正如列名和SQL数据类型的帮助一样。|743489|反对限制的论据：|743490|更复杂的业务规则不能通过声明性约束来建模，因此您必须在应用程序空间中实现一些规则。既然如此，为什么不在一个地方(您的应用程序)和同一种语言中实现所有业务规则呢？这使得调试、测试和跟踪代码修订变得更加容易。|743491|约束通常涉及索引，在插入/更新期间会产生一定的开销。另一方面，即使不声明约束，也可能需要索引，因为该列可能经常用于搜索条件或联接条件。|743492|约束会使您试图“清理”数据中的错误变得更加复杂。|743493|在当前的项目中，MyISAM与InnoDB在引用约束方面的不兼容性导致了一些问题。|743494|entityMap^0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|11|8|@]|9|@]|A|$]]|$1|E|3|F|5|D|7|12|8|@]|9|@]|A|$]]|$1|G|3|H|5|D|7|13|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|14|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|15|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|16|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|17|8|@]|9|@]|A|$]]|$1|Q|3|R|5|D|7|18|8|@]|9|@]|A|$]]|$1|S|3|T|5|D|7|19|8|@]|9|@]|A|$]]|$1|U|3|V|5|D|7|1A|8|@]|9|@]|A|$]]|$1|W|3|X|5|D|7|1B|8|@]|9|@]|A|$]]|$1|Y|3|-4|5|6|7|1C|8|@]|9|@]|A|$]]]|Z|$]]

I'm usually in favor of declaring constraints in the database. Arguments for constraints:

<ul>
<li>Declarative code is easier to make bug-free than Imperative code. Constraints are enforced even if app code contains bugs.</li>
<li>Supports the "Don't Repeat Yourself" principle, if you have multiple applications or code modules accessing the same database and you need business rules to be enforced uniformly. If you need to change the constraint, you can do it in one place, even if you have many apps.</li>
<li>Enforces data integrity even when people try to bypass the application, using ad hoc query tools to tinker with the database.</li>
<li>Enforces consistency which means that you can always be certain the data is in a valid state before and after any data update. If you don't use constraints, you may need to run periodic queries to check for broken references and clean them up.</li>
<li>You can model cascading update/delete easily with constraints. Doing the same thing in application code is complex and inefficient, cannot apply changes atomically (though using transaction isolation is recommended), and is susceptible to bugs.</li>
<li>Constraints help databases be more self-documenting, just as column names and SQL data types help.</li>
</ul>

Arguments against constraints:

<ul>
<li>More complex business rules cannot be modeled by declarative constraints, so you have to implement some in application space anyway. Given that, why not implement all business rules in one place (your app) and in the same language? This makes it easier to debug, test, and track code revisions.</li>
<li>Constraints often involve indexes, which incur some amount of overhead during inserts/updates. On the other hand, even if you don't declare a constraint, you probably need an index anyway, because the column may be used in search criteria or join conditions frequently.</li>
<li>Constraints can complicate your attempts to "clean up" mistakes in the data.</li>
<li>In your current project, the incompatibility of MyISAM vs. InnoDB with respect to referential constraints is causing some grief.</li>
</ul>

blocks|key|743510|text|恐怕这是个宗教话题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|743511|从纯粹的观点来看，您希望数据库能够完成引用的完整性.当您有多个应用程序访问数据库时，这是非常理想的，因为约束位于一个位置。不幸的是，现实世界并不理想。|743512|如果您必须强制执行某种引用完整性，根据我的经验，您的应用程序将需要知道如何做到这一点。无论是最终仲裁者还是数据库检查都是如此。即使数据库确实执行了引用完整性，如果数据库拒绝更新，应用程序也必须知道该如何做，因为引用完整性将被侵犯.|743513|作为一个侧面，设置MySQL以支持外键约束有点像一个过程，因为您需要转移到InnoDB。如果您只是这样做，您可以通过将innodb_flush_log_at_tx_commit设置为1来获得很大的性能，但是如果您能够重新设计您的站点以提高对事务的感知，可能会更好。然后，您将从InnoDB中获得两个好处。|offset|length|style|CODE|743514|entityMap|0|LINK|mutability|MUTABLE|url|http://en.wikipedia.org/wiki/InnoDB^0|0|0|0|1N|T|11|6|0|0^^$0|@$1|2|3|4|5|6|7|T|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|U|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|V|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|W|8|@$H|X|I|Y|J|K]]|9|@$H|Z|I|10|1|11]]|A|$]]|$1|L|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|M|$N|$5|O|P|Q|A|$R|S]]]]

I'm afraid this is a religious topic. 

From a puristic point-of-view, you want the database to do the referential integrity. This is ideal when you have a multiplicity of applications accessing the database, because the constraints are in one place. Unfortunately, the real world is not ideal.

If you have to enforce some sort of referential integrity, in my experience, your application will need to know how to do this. This is regardless of whether it is the final arbiter, or the database checks it as well. And even if the database does do the referential integrity, then the application has to know what to do if the database rejects an update, because referential integrity would be violated...

As a sidenote, setting up MySQL to support foreign key constraints is a bit of a process because you need to shift to <a href="http://en.wikipedia.org/wiki/InnoDB" rel="nofollow noreferrer">InnoDB</a>. If you do just that, you can get a lot of performance back by setting <code>innodb_flush_log_at_tx_commit</code> to 1. But it probably would be better if you can instead re-engineer your site to be transaction-aware. Then you get two benefits of InnoDB.

blocks|key|419477|text|让数据层通过约束强制数据一致性有助于确保数据保持一致，并在应用程序中提供廉价的运行时错误检查。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|419478|如果您认为约束是不值得的，那么您要么有一个小/非任务关键系统，要么您正在放弃一个巨大的机会来提高您的系统的质量。这一点再怎么强调也不为过。|offset|length|style|BOLD|419479|选择包括:选择不同的RDBMS、重新创建您自己的元数据系统或手动管理约束。在没有元数据系统的查询中，由于模式/系统复杂性的增加，并且不可避免地使不断发展的模式复杂化，因此对查询的手动管理很快就不可能正确地维护和审计。|419480|我的建议是选择不同的RDBMS。|419481|一致性检查比你想象的要困难得多。例如，MySQL使用事务性读取一致性，这意味着您要检查的值可能不是另一个事务范围内的相同值。对于并发访问，如果不直接绑定到数据层，则很难得到正确的一致性语义。|419482|当所有的事情都说了又做了，即使在手工检查上付出了一定的努力，可能的结果是，一个人仍然能够驾驶一辆卡车穿过你没有考虑到的或者在成形过程中犯了错误的箱子。|419483|关于你不虚的问题..。显而易见的数据字段要求是一个很好的起点。下面是定义列空性时需要考虑的其他几件事情。|419484|它提供了一个在编写查询时非常有用的保证。各种联接可以使用空条件来显示与空值分离的表行的不匹配，如果条件允许为空，则不能假定空值。(如果允许null，则匹配可能意味着该行不匹配或行匹配，但列值为null。)|419485|NULL的使用还有助于为更简单的查询定义匹配值的规则。因为如果value1和value2都为NULL，则不能说"WHEN+value1+=+value2“，因此计算结果仍然是假的。|419486|entityMap^0|0|14|3|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|X|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Y|8|@$D|Z|E|10|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|11|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|12|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|13|8|@]|9|@]|A|$]]|$1|N|3|O|5|6|7|14|8|@]|9|@]|A|$]]|$1|P|3|Q|5|6|7|15|8|@]|9|@]|A|$]]|$1|R|3|S|5|6|7|16|8|@]|9|@]|A|$]]|$1|T|3|U|5|6|7|17|8|@]|9|@]|A|$]]|$1|V|3|-4|5|6|7|18|8|@]|9|@]|A|$]]]|W|$]]

Having your data tier enforce data consistency through constraints helps to ensure your data remains consistent and provides cheap runtime bug checking within your application.

If you think constraints are not worthwhile you either have a small/non mission critical system or you are passing up a huge opportunity to improve the quality of your system. This cannot be understated.

Choices include: choosing a different RDBMS, reinvent your own metadata system or manually manage constraints. Manual management in queries without a metadata system quickly becomes infeasible to maintain and audit properly as schema/system complexity grows and unecessarily complicates an evolving schema.

My recommendation is to choose a different RDBMS.

Consistency checking is much harder than you may think. For example MySQL uses transactional read consistency which means the values you are checking against may not be the same values in the scope of another transaction. Consistency scemantics for concurrent access are very very difficult to get right if not bound directly to the data tier.

When all is said and done, even with a modest amount of effort put into manual checking, the likely outcome is that one would still be able to drive a truck through the corner cases you have failed to consider or committed an error in forming.

On your NOT NULL question... The obvious data field requirements are a good starting point. Here are a couple of other things to consider when defining column nullability.

It provides a guarantee that can very helpful when writing queries. Various joins may use NULL conditions to show a non-match of a table row separate from a NULL value that cannot be assumed if the condition allows nulls. (If NULLs are allowed, a match can mean either the row did not match or the row did match but the column value is null.)

The use of NOT NULL also helps define the rules for simpler queries matching values. Since you cannot say "WHEN value1 = value2" if both value1 and value2 are NULL the result of the evaluation is still false.

blocks|key|1953594|text|即使您的PHP代码完全没有bug，它也可能停止中间脚本(内存错误、某些库中的分段错误等)，在数据库中留下半插入的内容，因此使用InnoDB和事务的重要性。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1953595|当然，对于约束，您应该有适当的表单验证，并在其后面有数据库约束来捕获bug。|1953596|数据库约束很容易指定，在应用程序中查找bug很难，而没有约束就更难了。|1953597|我的经验是，不适当地限制数据库，以及任何使用MyISAM的数据库，在使用几个月后都会有不确定的数据，很难找到它的来源。|1953598|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|L|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|M|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

Even if your PHP code is perfectly bug-free, it may stop mid-script (out of memory error, segfault in some library, etc), leaving half-inserted stuff in the database, hence the importance of using InnoDB and transactions.

Same for constraints, of course you should have proper form validation, and database constraints behind it to catch bugs.

Database constraints are easy to specify, finding bugs in the application is hard, and even harder without constraints.

My experience has been that improperly constrained databases, and anything that uses MyISAM, WILL have inconssitent data after a few months of use, and it is very hard to find where it came from.

blocks|key|743565|text|在数据库级别实现默认值和约束；规则将为任何消费应用程序生成可接受的数据。这使你远离诚信问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|743566|然后，在应用程序级别实现更好的默认值和约束。如果在技术上禁止您(访问数据库外部的API)实现约束，或者在数据库级别生成默认值，则需要在应用程序中这样做。这将导致更好的默认值。但是，分段错误或应用程序的一般故障不会导致不可接受的数据被持久化。|743567|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

Implement default values and constraints at the database level; rules that will result in acceptable data to any consuming application. This insulates you from integrity issues.

Then, implement better default values and constraints at the application level. If you are prohibited technically (access to APIs external to the database) from implementing a constraint, or generating a default value at the database level, the application is where you'll need to do it. This will result in a better default value. However a segfault, or general failure of the application will not result in unacceptable data being persisted.

I am trying to create a column in a table that's a foreign key, but in MySQL that's more difficult than it should be. It would require me to go back and make certain changes to an already-in-use table. So I wonder, how necessary is it for MySQL to be sure that a certain value is appropriate? Couldn't I just do that with a language like PHP, which I'm using to access this database anyway?

Similarly with NOT NULL. If I only access this database with PHP, couldn't I simply have PHP ensure that no null value is entered?

Why should I use MySQL to do enforce these constraints, when I could just do it with PHP?

<hr>

I realize that NOT NULL is a very stupid part to neglect for the above reasons. But MySQL doesn't enforce foreign keys without a serious degree of monkeying around.

In your opinion, would it still be bad to use the "fake" foreign keys, and simply check if the values to be entered are matched in other tables, with PHP?

How important are constraints like NOT NULL and FOREIGN KEY if I'll always control my database input with PHP?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我正试图在表中创建一个作为外键的列，但在MySQL中，这比应该创建的要困难得多。这需要我回去对一个已经在用的桌子做一些修改。所以我想知道，，MySQL是否有必要确保某个值是合适的？我就不能用一种像PHP这样的语言来访问这个数据库anyway 类似地，使用NULL。如果我只使用PHP访问这个数据库，难道我不能让PHP确保...

问如果我总是用PHP控制我的数据库输入，那么像NULL和外键这样的约束有多重要？
EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如果我总是用PHP控制我的数据库输入，那么像NULL和外键这样的约束有多重要？EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如果我总是用PHP控制我的数据库输入，那么像NULL和外键这样的约束有多重要？
EN