The Cost of Useless Surrogate Keys in Relationship Tables

What's a good natural key? This is a very difficult question for most entities when you design your schema. In some rare cases, there seems to be an "obvious" candidate, such as a variety of ISO standards, including: ISO 639 language codes ISO 3166 country codes ISO 4217 currency codes But even in those cases, … Continue reading The Cost of Useless Surrogate Keys in Relationship Tables

How to Quickly Rename all Primary Keys in Oracle

Are you working with someone else's schema and they haven't declared nice names for all their constraints? Unfortunately, it is all too easy to create a table like this: CREATE TABLE order1 ( order_id NUMBER(18) NOT NULL PRIMARY KEY ); Or like this: CREATE TABLE order2 ( order_id NUMBER(18) NOT NULL, PRIMARY KEY (order_id) ); … Continue reading How to Quickly Rename all Primary Keys in Oracle

SQL GROUP BY and Functional Dependencies: A Very Useful Feature

Relational databases define the term "Functional Dependency" as such (from Wikipedia): In relational database theory, a functional dependency is a constraint between two sets of attributes in a relation from a database. In other words, functional dependency is a constraint that describes the relationship between attributes in a relation. In SQL, functional dependencies appear whenever … Continue reading SQL GROUP BY and Functional Dependencies: A Very Useful Feature

Subtle SQL differences: IDENTITY columns

As I'm mostly using Oracle, IDENTITY columns were not so important to me up until I started to support them in jOOQ. Then, I found that yet again, there are many differences between various databases in how they handle IDENTITY columns in DDL and in DML. In SQL, there are essentially three orthogonal concepts of … Continue reading Subtle SQL differences: IDENTITY columns

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 … Continue reading Subtle SQL differences: Constraint names