Subtle SQL differences: Constraint names

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/