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

Question

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?

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

Marc

Answer 2

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.

Answer 3

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

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.

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