What's the dividing line ...

... between a referential integrity issue and a business logic issue? 

Assume tables A and B with fields A.RefId and B.RecordId.  There's a foreign key relationship from A.RefId --> B.RecordId.

Let's say the values of A.RefId need to be further restricted:  only values of B.RecordId where B.TypeId = 3 can be used.

Is this a business logic issue, or a referential integrity issue?

If it's business logic, does it have any place in the database?

I'd be curious to hear what people think. 

Why can't I ...

... set up a foreign key relationship in SQL Server 2008 where the parent is a view with a unique index set up over it?

Unless I'm much mistaken, you can't do this.

You can achieve the same result with triggers, but:

1. A lot of people hate triggers;
2. You have to code the things, which introduces possible errors, depending on how sharp the coder is and how well he or she knows trigger behavior.  Foreign key relationships are hard to get wrong.
3. ETA:  You also have to set up triggers on both sides of the relationship, if you want to constrain both tables.   With a foreign key, the mutual constraining is automatic. 

Why do I want to do this?  Well, I have a table where a certain field can be any value from the Id column of Table 2, provided [Table 2].TypeId = 3.  I'd love it if the database would enforce this.  But unless I can persuade folks that a trigger is an acceptable choice in this situation, it'll have to be done in code.

Useful Things to Know, #424

This is an oldie I've been meaning to post for years.

Assume a variable n  of an enumerated type t.  Let's say t has the traditional values Red, Blue, and Green. 

n.ToString("g") produces a string equal to the name of the enumeration value:  Green.
n.ToString("d") produces a string equal to the underlying value of the enumeration:  2.

Plain n.ToString() acts like n.ToString("g")

Why is this useful to remember?  Because if you ever have to build a filter string in the form "ColorId = value", and your value is an instance of an enumeration, you can build the string like so: 

string filter = String.Format("ColorId = {0:d}", n);

I've seen people handle this a number of ways, including casting back to Int32 and so forth, but this is definitely the shortest, tidiest way. 

Graceful Failure

I've been thinking lately about the semantics of graceful failure.

If your software is customer-facing, you don't want an internal error to cause it to spew internal details at the consternated user.  That is, among other things, a security flaw.  But, at the extreme other end, you don't want it to be pretend that everything is hunky-dory when it isn't.  It shouldn't say that a transaction completed when it didn't, and it shouldn't pretend that no results were found when it couldn't successfully complete the call to retrieve them. 

If you are designing a web service, and the service does not convey error messages to the user, what should it convey when it is unable (due to internal error) to complete its business?  I would have voted for null, but for Get-type actions, null is sometimes used to mean, "Nothing found" which is not the same thing. 

Perhaps having the web service throw an error for the user to handle *is* the best solution. 

Serializing a Null

What do you suppose happens when you pass a null reference to XmlSerializer.Serialize as the object to be serialized?

You'd probably think an error, or perhaps that nothing would be written to the stream to which the object is to be serialized. Instead, you get something like this, depending on what the type is that you initialized the serializer with (I used a List<string>):

<ArrayOfString xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:nil="true" />

In other words, you get an empty fragment, an element with xsi:nil="true", an empty (in this case) list.

Passing a non-null but empty List<string> gave me this fragment:

<ArrayOfString xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" />

So that xsi:nil is very important: it's the difference between an object with no elements and an object that is itself null.

I would not have guessed you could serialize a null, but you can.