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