约束命名的目的是什么
命名约束（唯一、主键、外键）的目的是什么？
假设我有一个使用自然键作为主键的表：
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)
);
命名我的 PK 会带来什么好处（如果有）？
例如。 替换：
PRIMARY KEY(LoginName, OrderDateTime)
为：
CONSTRAINT Order_PK PRIMARY KEY(LoginName, OrderDateTime)
抱歉，如果我的数据模型不是最好的，我是新手！
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?
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!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（9）
这是一些非常基本的原因。
(1) 如果查询（插入、更新、删除）违反约束，SQL 将生成包含约束名称的错误消息。如果约束名称清晰且具有描述性，则错误消息将更容易理解；如果约束名称是基于 guid 的随机名称，则不太清楚。特别是对于最终用户来说，他们会（好吧，可能）打电话给您并询问“
FK__B__B_COL1__75435199”的含义。(2) 如果将来需要修改某个约束（是的，确实会发生），如果您不知道它的名称，则很难做到这一点。 (ALTER TABLE MyTable drop CONSTRAINT um...) 如果您“从头开始”创建多个数据库实例并使用系统生成的默认名称，则不会有两个名称匹配。
(3) 如果支持你的代码的人（又名 DBA）必须在周日凌晨 3 点浪费大量无意义的时间来处理案例 (1) 或案例 (2)，那么他们很可能会识别代码的来源并能够做出相应的反应。
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.(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.
(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.
为了在将来识别约束（例如，您想在将来删除它），它应该有一个唯一的名称。如果您没有为其指定名称，数据库引擎可能会为您分配一个奇怪的名称（例如包含随机内容以确保唯一性）。
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.
这让 DBA 很高兴，因此他们可以将您的模式定义放入生产数据库中。
It keeps the DBAs happy, so they let your schema definition into the production database.
当您的代码随机违反某些外键约束时，它肯定会节省调试时间以找出它是哪一个。命名它们可以极大地简化插入和更新的调试。
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.
它可以帮助人们快速了解约束在做什么，而无需查看实际的约束，因为名称为您提供了所需的所有信息。
因此，我知道它是主键、唯一键还是默认键，以及涉及的表和可能的列。
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 correctly naming all constraints, You can quickly associate a particular constraint with our data model. This gives us two real advantages:
通过命名约束，您可以区分对它们的违反。这不仅对管理员和开发人员有用，而且您的程序也可以使用约束名称。这比尝试解析错误消息要健壮得多。通过使用约束名称，您的程序可以根据违反的约束做出不同的反应。
约束名称对于以用户语言显示适当的错误消息也非常有用，指出哪个字段导致了约束冲突，而不是仅仅将神秘的错误消息从数据库服务器转发给用户。
请参阅我关于如何使用 PostgreSQL 和 Java 执行此操作的答案。
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.
虽然OP的示例使用了永久表，但请记住，临时表上的命名约束的行为类似于永久表上的命名约束（即，您不能有多个会话使用完全相同的代码处理临时表，而不会生成错误，因为约束的名称相同）。因为命名约束必须是唯一的，所以如果您绝对必须在临时表上命名约束，请尝试在其末尾使用某种随机 GUID（如 SELECT NEWID() ）来执行此操作，以确保它将在各个会话中具有唯一的名称。
While the OP's example used a permanent table, just remember that named constraints on temp tables behave like named constraints on permanent tables (i.e. you can't have multiple sessions with the exact same code handling the temp table, without it generating an error because the constraints are named the same). Because named constraints must be unique, if you absolutely must name a constraint on a temp table try to do so with some sort of randomized GUID (like
SELECT NEWID()) on the end of it to ensure that it will uniquely-named across sessions.命名约束的另一个好理由是您是否在数据库模式上使用版本控制。在这种情况下，如果您必须使用默认数据库命名（在我的例子中是 SQL Server）删除并重新创建约束，那么您将看到提交的版本和工作副本之间的差异，因为它将具有新生成的名称。为约束指定显式名称将避免将其标记为更改。
Another good reason to name constraints is if you are using version control on your database schema. In this case, if you have to drop and re-create a constraint using the default database naming (in my case SQL Server) then you will see differences between your committed version and the working copy because it will have a newly generated name. Giving an explicit name to the constraint will avoid this being flagged as a change.