SQL 数据库中可以有同名的约束吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1397671/
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
Can there be constraints with the same name in a DB?
提问by Andrew
This is a follow-on question from the one I asked here.
这是我在这里提出的问题的后续问题。
Can constraints in a DB have the same name?
数据库中的约束可以具有相同的名称吗？
Say I have:
说我有：
CREATE TABLE Employer
(
EmployerCode VARCHAR(20) PRIMARY KEY,
Address VARCHAR(100) NULL
)
CREATE TABLE Employee
(
EmployeeID INT PRIMARY KEY,
EmployerCode VARCHAR(20) NOT NULL,
CONSTRAINT employer_code_fk FOREIGN KEY (EmployerCode) REFERENCES Employer
)
CREATE TABLE BankAccount
(
BankAccountID INT PRIMARY KEY,
EmployerCode VARCHAR(20) NOT NULL,
Amount MONEY NOT NULL,
CONSTRAINT employer_code_fk FOREIGN KEY (EmployerCode) REFERENCES Employer
)
Is this allowable? Does it depend on the DBMS (I'm on SQL Server 2005)? If it is not allowable, does anyone have any suggestions on how to work around it?
这是允许的吗？它是否取决于 DBMS（我使用的是 SQL Server 2005）？如果不允许，有人对如何解决它有任何建议吗？
回答by marc_s
No - a constraint is a database object as well, and thus its name needs to be unique.
不 - 约束也是一个数据库对象，因此它的名称需要是唯一的。
Try adding e.g. the table name to your constraint, that way it'll be unique.
尝试将例如表名添加到您的约束中，这样它就会是唯一的。
CREATE TABLE BankAccount
(
BankAccountID INT PRIMARY KEY,
EmployerCode VARCHAR(20) NOT NULL,
Amount MONEY NOT NULL,
CONSTRAINT FK_BankAccount_Employer
FOREIGN KEY (EmployerCode) REFERENCES Employer
)
We basically use "FK_"(child table)_(parent table)" to name the constraints and are quite happy with this naming convention.
我们基本上使用 "FK_"(child table)_(parent table)" 来命名约束，并且对这种命名约定非常满意。
Information from MSDN
来自 MSDN 的信息
That constraint names have to be unique to the schema (ie. two different schemas in the same database can both contain a constraint with the same name) is not explicitly documented. Rather you need to assume the identifiers of database objects must be unique within the containing schema unless specified otherwise. So the constraint name is definedas:
约束名称对于模式必须是唯一的（即，同一数据库中的两个不同模式都可以包含具有相同名称的约束）没有明确记录。相反，除非另有说明，否则您需要假设数据库对象的标识符在包含的模式中必须是唯一的。所以约束名称定义为：
Is the name of the constraint. Constraint names must follow the rules for identifiers, except that the name cannot start with a number sign (#). If constraint_name is not supplied, a system-generated name is assigned to the constraint.
是约束的名称。约束名称必须遵循标识符规则，但名称不能以数字符号 (#) 开头。如果未提供constraint_name，系统生成的名称将分配给约束。
Compare this to the name of an index:
将其与索引名称进行比较：
Is the name of the index. Index names must be unique within a table or view but do not have to be unique within a database. Index names must follow the rules of identifiers.
是索引的名称。索引名称在表或视图中必须唯一，但在数据库中不必唯一。索引名称必须遵循标识符规则。
which explicitly narrows the scope of the identifier.
这明确缩小了标识符的范围。
回答by Anthony Geoghegan
The other answers are all good but I thought I'd add an answer to the question in the title, i.e., "can there be constraints with the same name in a DB?"
其他答案都很好，但我想我会为标题中的问题添加一个答案，即“数据库中是否可以存在具有相同名称的约束？”
The answer for MS SQL Server is yes – but only so long as the constraints are in different schemas. Constraint names must be unique within a schema.
MS SQL Server 的答案是肯定的——但前提是约束在不同的模式中。约束名称在模式中必须是唯一的。
回答by Bill Karwin
I was always puzzled whyconstraint names must be unique in the database, since they seem like they're associated with tables.
我一直很困惑为什么约束名称在数据库中必须是唯一的，因为它们似乎与表相关联。
Then I read about SQL-99's ASSERTIONconstraint, which is like a check constraint, but exists apart from any single table. The conditions declared in an assertion must be satisfied consistently like any other constraint, but the assertion can reference multiple tables.
然后我读到 SQL-99 的ASSERTION约束，它就像一个检查约束，但存在于任何单个表之外。断言中声明的条件必须像任何其他约束一样一致地得到满足，但断言可以引用多个表。
AFAIK no SQL vendor implements ASSERTIONconstraints. But this helps explain why constraint names are database-wide in scope.
AFAIK 没有 SQL 供应商实施ASSERTION约束。但这有助于解释为什么约束名称的范围是数据库范围的。
回答by Guillaume Husta
It depends on the DBMS.
这取决于 DBMS。
For example on PostgreSQL, the answer is yes:
例如在PostgreSQL 上，答案是肯定的：
Because PostgreSQL does not require constraint names to be unique within a schema (but only per-table), it is possible that there is more than one match for a specified constraint name.
由于 PostgreSQL 不要求约束名称在模式中是唯一的（但仅针对每个表），因此指定的约束名称可能有多个匹配项。
Source : https://www.postgresql.org/docs/current/static/sql-set-constraints.html
来源：https: //www.postgresql.org/docs/current/static/sql-set-constraints.html
I've seen Foreign Keys constraint names equals on 2 different tables within the same schema.
我已经看到外键约束名称等于同一架构中的 2 个不同表。
回答by ChrisW
Does it depend on the DBMS (I'm on SQL Server 2005)?
它是否取决于 DBMS（我使用的是 SQL Server 2005）？
Yes, apparently it does depend on the DBMS.
是的，显然它确实取决于 DBMS。
Other answers say it's not permitted, but I have a MS SQL CE ("Compact Edition") database in which I accidentally successfully created two FK contraints, in two tables, with the same contraint name.
其他答案说这是不允许的，但我有一个 MS SQL CE（“精简版”）数据库，我不小心在其中成功创建了两个 FK 约束，在两个表中，具有相同的约束名称。
回答by Niikola
Good practice is to create index and constraint names specifying table name at the beginning. There's 2 approaches, with index/constraint type at the beginning or at the end) eg.
好的做法是在开始时创建指定表名的索引和约束名称。有 2 种方法，在开头或结尾使用索引/约束类型），例如。
UQ_TableName_FieldName
or
或者
TableName_FieldName_UQ
Foreign keys names should also contain names of referenced Table/Field(s).
外键名称还应包含引用表/字段的名称。
One of good naming conventions is to give table names in form of FullName_3LetterUniqueAlias eg.
良好的命名约定之一是以 FullName_3LetterUniqueAlias 的形式给出表名，例如。
Employers_EMR
Employees_EMP
BankAccounts_BNA
Banks_BNK
This give you opportunity to use "predefined" aliases in queries which improves readability and also makes Naming of foreign keys easier, like:
这使您有机会在查询中使用“预定义”别名，从而提高可读性并使外键命名更容易，例如：
EMPEMR_EmployerCode_FK
BNKEMR_EmployerCode_FK
