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

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.


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.


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.


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.


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.