Can there be constraints with the same name in a DB?

Sql Sql Server Sql Server-2005 Tsql Constraints

Sql Problem Overview

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?

Sql Solutions

Solution 1 - Sql

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.

Information from 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.

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.

Solution 2 - Sql

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.

Solution 3 - Sql

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.

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

Solution 4 - Sql

It depends on the DBMS.

For example on PostgreSQL, the answer is yes :

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

Source : 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.

Solution 5 - Sql

> Does it depend on the DBMS (I'm on 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.

Solution 6 - Sql

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.

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

Content Type	Original Author	Original Content on Stackoverflow
Question	Andrew	View Question on Stackoverflow
Solution 1 - Sql	marc_s	View Answer on Stackoverflow
Solution 2 - Sql	Anthony Geoghegan	View Answer on Stackoverflow
Solution 3 - Sql	Bill Karwin	View Answer on Stackoverflow
Solution 4 - Sql	Guillaume Husta	View Answer on Stackoverflow
Solution 5 - Sql	ChrisW	View Answer on Stackoverflow
Solution 6 - Sql	Niikola	View Answer on Stackoverflow