Referential integrity problem

[rpresser]

Here is a cut down structure:

CREATE TABLE Sites (
SiteID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
SiteName nvarchar(100) NOT NULL
)

CREATE TABLE Orders (
OrderID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
SiteID UNIQUEIDENTIFIER NOT NULL REFERENCES Sites (SiteID),
OrderNum INT NOT NULL
)

CREATE TABLE OrderItems (
OrderItemID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
OrderID UNIQUEIDENTIFIER NOT NULL REFERENCES Orders (OrderID),
ShippingOptionID UNIQUEIDENTIFIER NULL REFERENCES ShippingOptions (ShippingOptionID),
OrderItemQty INT NOT NULL,
OrderItemDescription NVARCHAR(100) NOT NULL
)

CREATE TABLE ShippingOptions (
ShippingOptionID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
SiteID UNIQUEIDENTIFIER NOT NULL REFERENCES Sites (SiteID),
ShippingOptionDescription NVARCHAR(80)
)


Note that OrderItems points to Orders, and also to ShippingOptions, each of which point to Sites. But it is still possible to have some inconsistency in the sense that an orderitem could point to an order of site X and also a shipping option of site Y.

My question is, is there any way to prevent it, without adding any fields? Is a trigger the right way to do it? Or a CHECK constraint using a UDF? Or something else I don't know about?

[rpresser]

I think I found an answer. Create a view that would return the illegal rows, cross joined to a table that has more than one row, and create a unique index on that view. The view cross joined to a multirow table would result in multiple rows for each illegal row; the unique index then prevents the row from being created.

Idea from this article:
http://www.sqlservercentral.com/articles/Data+Modeling/61529/

[Erland Sommarskog]

Post by rpresser
I think I found an answer. Create a view that would return the illegal
rows, cross joined to a table that has more than one row, and create a
unique index on that view. The view cross joined to a multirow table
would result in multiple rows for each illegal row; the unique index
then prevents the row from being created.

Now, that's wild!

My gut feeling when I look at the problem is that there is something wrong
in the modelling, but I can't point my finger on it. Well, it seems crazy
that different items on an order have different shipping options, but I
guess names have been changed to protect the innocent. And just with
abstract names, it difficult to say whether the model is correct or not.

The solution was amazing nevertheless.

[Gene Wirchenko]

On Wed, 01 Feb 2012 23:42:56 +0100, Erland Sommarskog

Post by Erland Sommarskog

Post by rpresser
I think I found an answer. Create a view that would return the illegal
rows, cross joined to a table that has more than one row, and create a
unique index on that view. The view cross joined to a multirow table
would result in multiple rows for each illegal row; the unique index
then prevents the row from being created.

Now, that's wild!
My gut feeling when I look at the problem is that there is something wrong
in the modelling, but I can't point my finger on it. Well, it seems crazy

I red flag on it, too, but I have not looked at it deeply.

Post by Erland Sommarskog
that different items on an order have different shipping options, but I
guess names have been changed to protect the innocent. And just with
abstract names, it difficult to say whether the model is correct or not.

Why would it be crazy? A heavy part might require a truck. Spare
gaskets might be delivered by the salesperson when visiting.

"And last on the order, we require a supply of gaskets. Actually,
we need those right away. We are running low. Sam Salesperson has an
appointment with us tomorrow morning. Can Sam bring them then?"

Post by Erland Sommarskog
The solution was amazing nevertheless.

Sincerely,

Gene Wirchenko

[rpresser]

The best part of the solution is that by putting a WHERE clause in the view, I can enforce it only on future orders and not have to worry about fixing previous orders. Can't do THAT with a foreign key ;-)

[Erland Sommarskog]

Post by rpresser
The best part of the solution is that by putting a WHERE clause in the
view, I can enforce it only on future orders and not have to worry about
fixing previous orders. Can't do THAT with a foreign key ;-)

A foreign can be applied WITH NOCHECK which means that SQL Server does not
check that the current data is correct. Not that I would ever condone it...