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

user_157566 0
Viewed 35118

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-server-2005 constraints tsql sql-server sql
6 Answers

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.

user_16406610
Related