Can There Be Constraints with the Same Name in a Db

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.

What is the purpose of constraint naming

Here's some pretty basic reasons.

(1) If a query (insert, update, delete) violates a constraint, SQL will generate an error message that will contain the constraint name. If the constraint name is clear and descriptive, the error message will be easier to understand; if the constraint name is a random guid-based name, it's a lot less clear. Particulary for end-users, who will (ok, might) phone you up and ask what "FK__B__B_COL1__75435199" means.

(2) If a constraint needs to be modified in the future (yes, it happens), it's very hard to do if you don't know what it's named. (ALTER TABLE MyTable drop CONSTRAINT um...) And if you create more than one instance of the database "from scratch" and use system-generated default names, no two names will ever match.

(3) If the person who gets to support your code (aka a DBA) has to waste a lot of pointless time dealing with case (1) or case (2) at 3am on Sunday, they're quite probably in a position to identify where the code came from and be able to react accordingly.

Can I name my constraints with JPA?

As of JPA 2.1 it is possible to give a name to foreign key.
E.g.

@ManyToOne
@JoinColumn(foreignKey=@ForeignKey(name="MY_FANCY_FK_NAME"))
Account account;

Just make sure it is used within @JoinColumn. JavaDoc: https://docs.oracle.com/javaee/7/api/javax/persistence/ForeignKey.html#name%28%29

Postgres constraint name need to be unique across single table or entire schema?

Why the name my_constraint_1 can be used as the same in different tables, while my_constraint_2 must be unique

Constraint 2 has underlying index with the same name, while constraint 1 is simple check contraint on table level.

EXCLUDE
Exclusion constraints are implemented using an index, so each specified operator must be associated with an appropriate operator class (see Section 11.10) for the index access method index_method.

CREATE INDEX my_constraint_2 ON public.table_1 USING gist (daterange(date_start, COALESCE(date_end, 'infinity'::date), '[]'::text))

db<>fiddle demo

Will unnamed constraints be generated with different placeholder names each time a script is run on a different server?

Your thinking is correct. You cannot rely on the generated names of constraints.

There is no guarantee that a constraint name will be generated the same on two systems or even on the same system if you were to drop and re-create a constraint. You should always try to use named constraints.

Is it okay for the foreign key field to have a different name than the field it references in the other table?

Yes it is ok. Read sqlite foreign key document. It gives similar example. As you can see, column names are not same, but foreign key added nonetheless.

CREATE TABLE artist(
  artistid    INTEGER PRIMARY KEY, 
  artistname  TEXT
);
CREATE TABLE track(
  trackid     INTEGER, 
  trackname   TEXT, 
  trackartist INTEGER,
  FOREIGN KEY(trackartist) REFERENCES artist(artistid)
);

Is autogenerated constraint name in SQL Server same?

I got the answer from one blog. Here is the link http://www.databasejournal.com/features/mssql/article.php/1570801/Beware-of-the-System-Generated-Constraint-Name.htm

Short summary: System generated constraint name can be different so if you want to refer it in script, explicitly provide names to constraint and use it later on.

---

Related Topics