约束命名的目的是什么 [英] What is the purpose of constraint naming

问题描述

命名约束(唯一、主键、外键)的目的是什么?

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)
);

命名我的 PK 有什么好处(如果有的话)?

What benefits (if any) does naming my PK bring?

例如.替换:

    PRIMARY KEY(LoginName, OrderDateTime)

与:

    CONSTRAINT Order_PK PRIMARY KEY(LoginName, OrderDateTime)

对不起，如果我的数据模型不是最好的，我是新手！

Sorry if my data model is not the best, I'm new to this!

推荐答案

这里有一些非常基本的原因.

Here's some pretty basic reasons.

(1) 如果查询(插入、更新、删除)违反约束，SQL 将生成包含约束名称的错误消息.如果约束名称清晰且具有描述性，错误信息将更容易理解；如果约束名称是基于 guid 的随机名称，则不太清楚.特别是对于最终用户，他们会(好吧，可能)给您打电话询问FK__B__B_COL1__75435199"是什么意思.

(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) 如果一个约束在未来需要修改(是的，它发生了)，如果你不知道它的名字是什么很难做到.(ALTER TABLE MyTable drop CONSTRAINT 嗯...)如果您从头开始"创建多个数据库实例并使用系统生成的默认名称，则不会有两个名称匹配.

(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) 如果支持您的代码的人(又名 DBA)不得不在周日凌晨 3 点浪费大量无意义的时间来处理案例 (1) 或案例 (2)，那么他们很可能处于确定代码来自何处并能够做出相应反应的位置.

(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.

这篇关于约束命名的目的是什么的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！