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 codesISO 3166 country codesISO 4217 currency codes But even in those cases, there might … Continue reading The Cost of Useless Surrogate Keys in Relationship Tables
Tag: primary key
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