Re: Referential integrity problem

#1

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/arti...odeling/61529/

#2

rpresser (rpresser (AT) gmail (DOT) com) writes:

Quote:

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.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

#3

On Wed, 01 Feb 2012 23:42:56 +0100, Erland Sommarskog
<esquel (AT) sommarskog (DOT) se> wrote:

Quote:

rpresser (rpresser (AT) gmail (DOT) com) writes:
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.

Quote:

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?"

Quote:

The solution was amazing nevertheless.

Sincerely,

Gene Wirchenko

#4

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 ;-)

#5

rpresser (rpresser (AT) gmail (DOT) com) writes:

Quote:

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...

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Referential integrity problem

microsoft.public.sqlserver.programming microsoft.public.sqlserver.programming

Re: Referential integrity problem - 02-01-2012 , 01:32 PM

Re: Referential integrity problem - 02-01-2012 , 04:42 PM

Re: Referential integrity problem - 02-01-2012 , 05:22 PM

Re: Referential integrity problem - 02-01-2012 , 05:42 PM

Re: Referential integrity problem - 02-02-2012 , 03:22 AM