Home > Computers and Internet > Database design

Database design

I’ve been doing some reading about the various key types in databases and have been playing around with them on our Oracle 10g database at work.

Basically you have three types of keys:
1. Primary key – this uniquely identifies each row in the table and must have a value.
2. Unique key – this uniquely identifies a row in the table but can be missing a value.
3. Foreign key – this ties a field in a child table to a key in a parent table.

So I was playing around with null values. I made two tables:

my_test
test_id, test_name, test_age

my_child_test
child_id, parent_name, child_name

  • test_id is primary key
  • test_name is unique key
  • child_id is primary key
  • parent_name is a foreign key to test_name
  • parent_name, child_name is unique key

If I put a couple rows in my_test with null parent_names, Oracle lets it go through, since nulls can’t be compared with each other. Just for fun I inserted some child rows with null for a parent_name. The child rows were inserted because the foreign key was valid, yet I can’t connect the children to their parents with any type of join clause because nulls can’t be compared.

Now the fun part: ADO.NET. Because SQL Server treats null as a distinct value, it only allows one row with a null parent_name. Apparently this behavior is translated to ADO.NET Relations, because all four of my null parents now have the same null children. Magically, null = null. (I also had to disable the unique constraint on my test_name DataColumn)

I’m surprised at this as it violates the fundamental rule of what null represents: something of unknown value. Null != null, but apparently Microsoft thinks it does. What can’t be done with a SQL statement can be done with an ADO.NET Relation object. Setting the unique flag on a DataColumn also prevents me from entering more than one row with null.

I’m curious why Microsoft went this way, which is against how the majority of database manufacturers operate. Even the ANSI SQL standard agrees with Oracle’s implementation and not ADO.NET’s.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: