The various SQL product vendors implement subtle differences in the way they interpret SQL. In this case, I’ve been examining the reuse of constraint names within a schema / database (which is yet another story: what’s a schema, what’s a database?). Here’s the summary:
Constraint names are unique within a schema
- Derby
- H2
- HSQLDB
- Ingres
- MySQL
- Oracle
- SQL Server
Constraint names are unique within a table
- DB2
- SQLite
- Sybase SQL Anywhere
The “weird ones”
- Postgres: Foreign Key names can be reused. Unique / Primary Key names cannot
- Sybase ASE: Unique / Primary Key names can be reused. Foreign Key names cannot
For most compatibility across databases, it is never a good idea to re-use names. Keep your constraint names unique across a schema.
From http://lukaseder.wordpress.com/2011/12/10/subtle-sql-differences-constraint-names/
{{ parent.title || parent.header.title}}
{{ parent.tldr }}
{{ parent.linkDescription }}
{{ parent.urlSource.name }}