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

Verify, standardize, and correct the Big 4 + more– name, email, phone and global addresses – try our Data Quality APIs now at Melissa Developer Portal!

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/

Developers! Quickly and easily gain access to the tools and information you need! Explore, test and combine our data quality APIs at Melissa Developer Portal – home to tools that save time and boost revenue. Our APIs verify, standardize, and correct the Big 4 + more – name, email, phone and global addresses – to ensure accurate delivery, prevent blacklisting and identify risks in real-time.

Topics:

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}