Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> 0..1 relation question
 

Pages: 1
LLBLGen Pro Runtime Framework
0..1 relation question
Page:1/1 

  Print all messages in this thread  
Poster Message
simmotech
User



Location:

Joined on:
01-Feb-2006 15:43:00
Posted:
1006 posts
# Posted on: 21-Mar-2018 17:01:18.  
LLBLGen v4.2/SQL Server

I have a table called Loan and a table called Fine (think library)
Loan has a field called FineID and is nullable.
Each Loan row *may* reference a single FineRow.
The FineRow should not exist if a Fine row is not related to it.

The relationship I setup was
1 Loan is related to 0..1 Fine.

LLBL allows this but barfs when two Loans exists that don't have Fines - unique constraint violation.
I have come across this before and got around it with this

Code:
----------------------------------------------------------------------------------------------------
-- Special index to ensure that Fines are unique but can also be null
----------------------------------------------------------------------------------------------------
ALTER TABLE [dbo].[Loan]
DROP CONSTRAINT [UC_1e8362e4732b7e8f8dc45127cff]

--DROP INDEX [UC_1e8362e4732b7e8f8dc45127cff] ON [Loan]

CREATE UNIQUE NONCLUSTERED INDEX [UC_1e8362e4732b7e8f8dc45127cff]
ON [dbo].[Loan]
(
    [FineID]
)
WHERE [FineID] IS NOT NULL
GO

----------------------------------------------------------------------------------------------------
-- Special Trigger to delete Fine rows when Loan rows get deleted or LoanID becomes NULL
----------------------------------------------------------------------------------------------------
CREATE TRIGGER [dbo].[RemoveOldFineTrigger]
ON [dbo].[Loan]
AFTER UPDATE, DELETE
AS
BEGIN
    DELETE
    FROM Fine
    WHERE ID IN
    (
        SELECT
            d.FineID
        FROM deleted d
        LEFT JOIN inserted i ON i.ID = d.ID
        WHERE d.FineID IS NOT NULL AND (i.FineID IS NULL OR i.FineID <> d.FineID)
    )
END
GO


This deletes the LLBLGen unique constraint and replace it with a conditional index.
It also adds a trigger to ensure that the Fine row is deleted no longer references from Loan.

This all worked fine until I had call to run "Refresh Relational Model Data from a Database..." (I have to do this to update from a Typed View change), then LLBLGen decided that it should be a Many to 1 relationship and change it!

I changed it back but the LLBL-generated UC Guid has gone and been replaced with another so my script above no longer works!

So what exactly should my relationship be?

  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14480 posts
# Posted on: 22-Mar-2018 04:23:25.  
I think the DB Schema is better to be as follows:

Loan
------
ID (PK)
Other Fields


Fine
------
ID (PK)(FK to Loan.ID)
Other Fields


  Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.