Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Subtle SQL differences: Constraint names

DZone's Guide to

Subtle SQL differences: Constraint names

· Java Zone
Free Resource

Learn how to troubleshoot and diagnose some of the most common performance issues in Java today. Brought to you in partnership with AppDynamics.

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/

Understand the needs and benefits around implementing the right monitoring solution for a growing containerized market. Brought to you in partnership with AppDynamics.

Topics:

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}