Subtle SQL differences: Constraint names
Subtle SQL differences: Constraint names
Join the DZone community and get the full member experience.
Join For FreeDownload Microservices for Java Developers: A hands-on introduction to frameworks and containers. Brought to you in partnership with Red Hat.
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/
Download Building Reactive Microservices in Java: Asynchronous and Event-Based Application Design. Brought to you in partnership with Red Hat.
Like This Article? Read More From DZone
Opinions expressed by DZone contributors are their own.
{{ parent.title || parent.header.title}}
{{ parent.tldr }}
{{ parent.linkDescription }}
{{ parent.urlSource.name }}