69

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?

Community
  • 1
  • 1
Andrew
  • 11,068
  • 17
  • 52
  • 62

6 Answers6

78

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.

Richard
  • 106,783
  • 21
  • 203
  • 265
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • +1: We used the first letters of the words that make the table name as a prefix. – OMG Ponies Sep 09 '09 at 05:21
  • 2
    This is only partially true, as uniqueness must is forced at schema level, not database level. See [this answer below](https://stackoverflow.com/a/19832399/2780791). – Alexei - check Codidact Jul 13 '21 at 08:39
  • @Alexei-checkCodidact I agree with you but @marc_s is opting for a perfectly reasonable and not-incorrect but conservative interpretation of the official documentation for `table_constraint` which does not mention schemas and the scope of object names _at all_ (even today, in 2022, 13 years later, seriously: Ctrl+F it!); so it's _possible_ that Microsoft might decide to require `CONSTRAINT` object names to be db-wide unique instead of schema-unique in the next release of SQL Server. That said, if I had more gall I'd edit marc's answer to change "No" to "Technically yes, but probably no". – Dai Mar 05 '22 at 07:28
35

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.

Anthony Geoghegan
  • 11,533
  • 5
  • 49
  • 56
  • What schema does a foreign-key belong to though? An FK constraint can reference tables in multiple schemas... I assume it's the schema of the table where the constraint is declared, but is that always true? – Dai Sep 19 '20 at 17:50
  • This is actually true. EF Core just created `PK_Foo` for two tables and I was wondering how that can be. I realized that the two tables belong to different schemas (`dbo` and `arc`). I think this should be the accepted answer. – Alexei - check Codidact Jul 13 '21 at 08:38
15

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • 1
    well, even if the assertion constraint isn't implemented: would you raelly want to have three foreign key constraints by the same name? If you get a FK violation error stating the name of the FK - how do you know which of the three it really is? I think enforcing unique constraint names is a "Good Thing (tm)" :-) – marc_s Sep 09 '09 at 20:50
  • Yes, definitely. I was making the point that "it's in the ANSI SQL standard that way," but your point is even more practical. – Bill Karwin Sep 09 '09 at 21:08
  • 2
    @marc_s If the constraint names were table-scoped, the error message would contain the table name as well. That's what Postgres does, for example. – Bergi Aug 13 '19 at 08:39
  • 1
    @marc_s Ideally, I'd like the error to tell me what table. Although, about 99% or more of the time I could probably figure it out quite easily on my own if I had to. – BVernon Dec 11 '19 at 04:17
  • also, requiring the table name eats up a lot of space in the key name (which has a max allowable length of 64) which makes it painful when you have very long table names. – dave Sep 17 '21 at 18:31
8

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.

Guillaume Husta
  • 4,049
  • 33
  • 40
2

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.

ChrisW
  • 54,973
  • 13
  • 116
  • 224
1

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
Niikola
  • 1,452
  • 1
  • 10
  • 12