SQL 约束命名的目的是什么
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1397440/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is the purpose of constraint naming
提问by Andrew
What is the purpose of naming your constraints (unique, primary key, foreign key)?
命名约束（唯一、主键、外键）的目的是什么？
Say I have a table which is using natural keys as a primary key:
假设我有一个使用自然键作为主键的表：
CREATE TABLE Order
(
LoginName VARCHAR(50) NOT NULL,
ProductName VARCHAR(50) NOT NULL,
NumberOrdered INT NOT NULL,
OrderDateTime DATETIME NOT NULL,
PRIMARY KEY(LoginName, OrderDateTime)
);
What benefits (if any) does naming my PK bring?
命名我的 PK 有什么好处（如果有的话）？
Eg. Replace:
例如。代替：
PRIMARY KEY(LoginName, OrderDateTime)
With:
和：
CONSTRAINT Order_PK PRIMARY KEY(LoginName, OrderDateTime)
Sorry if my data model is not the best, I'm new to this!
对不起，如果我的数据模型不是最好的，我是新手！
回答by Philip Kelley
Here's some pretty basic reasons.
这是一些非常基本的原因。
(1) If a query (insert, update, delete) violates a constraint, SQL will generate an error message that will contain the constraint name. If the constraint name is clear and descriptive, the error message will be easier to understand; if the constraint name is a random guid-based name, it's a lot less clear. Particulary for end-users, who will (ok, might) phone you up and ask what "FK__B__B_COL1__75435199" means.
(1) 如果查询（插入、更新、删除）违反约束，SQL 将生成包含约束名称的错误消息。如果约束名称清晰且具有描述性，错误信息将更容易理解；如果约束名称是基于 guid 的随机名称，则不太清楚。特别是对于最终用户，他们会（好吧，可能）给您打电话询问“ FK__B__B_COL1__75435199”是什么意思。
(2) If a constraint needs to be modified in the future (yes, it happens), it's very hard to do if you don't know what it's named. (ALTER TABLE MyTable drop CONSTRAINT um...) And if you create more than one instance of the database "from scratch" and use system-generated default names, no two names will ever match.
(2) 如果一个约束在未来需要修改（是的，它发生了），如果你不知道它的名字是什么很难做到。（ALTER TABLE MyTable drop CONSTRAINT 嗯...）如果您“从头开始”创建多个数据库实例并使用系统生成的默认名称，则不会有两个名称匹配。
(3) If the person who gets to support your code (aka a DBA) has to waste a lot of pointless time dealing with case (1) or case (2) at 3am on Sunday, they're quite probably in a position to identify where the code came from and be able to react accordingly.
(3) 如果支持您的代码的人（又名 DBA）不得不在周日凌晨 3 点浪费大量无意义的时间来处理案例 (1) 或案例 (2)，那么他们很可能能够识别代码的来源并能够做出相应的反应。
回答by Mehrdad Afshari
To identify the constraint in the future (e.g. you want to drop it in the future), it should have a unique name. If you don't specify a name for it, the database engine will probably assign a weird name (e.g. containing random stuff to ensure uniqueness) for you.
为了在未来识别约束（例如，你想在未来删除它），它应该有一个唯一的名称。如果您没有为其指定名称，数据库引擎可能会为您分配一个奇怪的名称（例如，包含随机内容以确保唯一性）。
回答by Mehrdad Afshari
It keeps the DBAs happy, so they let your schema definition into the production database.
它让 DBA 感到高兴，因此他们让您的模式定义进入生产数据库。
回答by Tim Howland
When your code randomly violates some foreign key constraint, it sure as hell saves time on debugging to figure out which one it was. Naming them greatly simplifies debugging your inserts and your updates.
当您的代码随机违反某些外键约束时，它肯定会节省调试时间以确定它是哪一个。命名它们极大地简化了调试插入和更新的过程。
回答by James Black
It helps someone to know quickly what constraints are doing without having to look at the actual constraint, as the name gives you all the info you need.
它可以帮助人们快速了解约束正在做什么，而无需查看实际约束，因为名称为您提供了所需的所有信息。
So, I know if it is a primary key, unique key or default key, as well as the table and possibly columns involved.
所以，我知道它是主键、唯一键还是默认键，以及所涉及的表和可能的列。
回答by GG.
By correctly naming all constraints, You can quickly associate a particular constraint with our data model. This gives us two real advantages:
通过正确命名所有约束，您可以快速将特定约束与我们的数据模型相关联。这给了我们两个真正的优势：
- We can quickly identify and fix any errors.
- We can reliably modify or drop constraints.
- 我们可以快速识别并修复任何错误。
- 我们可以可靠地修改或删除约束。
回答by Martin
By naming the constraints you can differentiate violations of them. This is not only useful for admins and developers, but your program can also use the constraint names. This is much more robust than trying to parse the error message. By using constraint names your program can react differently depending on which constraint was violated.
通过命名约束，您可以区分违反它们的情况。这不仅对管理员和开发人员有用，而且您的程序也可以使用约束名称。这比尝试解析错误消息要健壮得多。通过使用约束名称，您的程序可以根据违反的约束做出不同的反应。
Constraint names are also very useful to display appropriate error messages in the user's language mentioning which field caused a constraint violation instead of just forwarding a cryptic error message from the database server to the user.
约束名称对于以用户的语言显示适当的错误消息也非常有用，指出哪个字段导致了约束违规，而不是仅仅从数据库服务器向用户转发一个神秘的错误消息。
See my answer on how to do this with PostgreSQL and Java.
请参阅我关于如何使用 PostgreSQL 和 Java 执行此操作的答案。
