Lewies Blog

I found some problems when restoring a backed up database. It appears that many foreign keys were missing. I came back to the database developer and explained that she was missing some foreign key relationships. She then went on to show me that the relationships did exist in the original database entity-relationship model.

My co-worker said that it would have been impossible to get the data out of a data table without the foreign key relationships defined in the database. We proceeded to debate over it, for I didn’t agree. She asked if I wanted to bet lunch and I shook hands on it.

Later today she called me into her office with two small tables in Microsoft Access. She told me to try and select data from both tables without a relationship. I started using an “Inner Join” but she said I wasn’t allowed. So instead, I used a simple “Cross Join” and used the where clause to compare the two values. From what I understand, Oracle only allows cross joins and this is what she was used to seeing.

My query worked. She was awestruck and argued that it wouldn’t have been possible to do that in Oracle unless a relationship existed first between the two tables. I haven’t worked with Oracle, but I’m doubting that the argument is valid. My explanation to her is that relationships are primarily used for data integrity along with cascading updates and deletes. Other then that, you can run queries against multiple tables without relationships in place.

She turned to me and said “I guess I owe you a lunch”. My reply - “I knew that the moment I shook your hand”. Sometimes it is so easy that I feel guilty.

This entry was posted on Wednesday, February 23rd, 2005 at 7:02 pm and is filed under Miscellaneous. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.