DB中是否存在同名的约束?

This is a follow-on question from the one I asked here.

这是我在这里问的问题的后续问题。

Can constraints in a DB have the same name?

DB中的约束能有相同的名称吗?

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上)?如果这是不允许的，有人对如何解决这个问题有什么建议吗?

6 个解决方案

#1

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_”(子表)_(父表)来命名约束，并对命名约定非常满意。

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 defined as:

约束名称必须是模式特有的。同一个数据库中的两个不同的模式都可以包含具有相同名称的约束)没有显式记录。相反，您需要假设数据库对象的标识符必须在包含的模式中是唯一的，除非另有规定。约束名称的定义是:

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.

它显式地缩小标识符的范围。

#2

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?"

其他的答案都很好，但是我想我应该在题目中加上一个答案。，“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的答案是肯定的——但前提是约束在不同的模式中。约束名称必须在模式中是唯一的。

#3

I was always puzzled why constraint names must be unique in the database, since they seem like they're associated with tables.

我总是搞不懂为什么约束名在数据库中必须是唯一的，因为它们似乎与表相关联。

Then I read about SQL-99's ASSERTION constraint, 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的断言约束，它就像一个检查约束，但它与任何一个表都不存在。断言中声明的条件必须与任何其他约束一样一致地得到满足，但是断言可以引用多个表。

AFAIK no SQL vendor implements ASSERTION constraints. But this helps explain why constraint names are database-wide in scope.

没有SQL供应商实现断言约束。但是这有助于解释为什么约束名称在数据库范围内。

#4

Does it depend on the DBMS (I'm on SQL Server 2005)?

它是否依赖于DBMS(我在SQL Server 2005上)?

Yes, apparently it does depend on the 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(“Compact Edition”)数据库，在这个数据库中，我不小心成功地在两个表中创建了两个FK约束，并且名称相同。

#5

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.

好的实践是在开始时创建指定表名的索引和约束名。有两种方法，索引/约束类型在开头或结尾)。

UQ_TableName_FieldName

或

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 eg的形式给出表名。

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

#6

It depends on the 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.

我看到在同一个模式中，在两个不同的表上，外键约束名称等于。

#1