I've come across the issue where I want an optional 1:1 relationship

AccountTransaction 1:0..1 NominalTransaction

(AccountTransaction does not have to have a related NominalTransaction but if it does it is unique)

I've read about your (many) comments that Sql Server doesn't support this because a UniqueConstraint is required and SQL Server UniqueConstraints treat null as a comparable value.

For Sql Server 2008 and above, this article http://improvingsoftware.com/2010/03/26/creating-a-unique-constraint-that-ignores-nulls-in-sql-server/ uses a filtered Unique Constraint. A Unit Test that previously failed due to duplicate nulls now runs correctly.

Do you guys have any issues with this method? If not, can this be incorporated into the generated code?

You mean filtered index (the first option in that link) ?

The option is ok, with one caveat: the driver at the moment doesn't use unique indexes as unique constraints. So using this and then reverse engineering the model will not see the unique index as a UC, as it doesn't pick up unique indexes.

If you work model first, it's ok: simply change the DDL SQL that's being generated to your liking before it's ran on the RDBMS.

Its not as quite simple as that though. No problem with the Create script (as long as those accursed Guid restraint names don't change ) I have a post-Create script to run.

But all Update Scripts generated by LLBLGen seem to drop all constraints before making changes and reapplying them after. If I've replaced that constraint with a filtered index then the update scripts are going to have to be tweaked every time.

That's why I was hoping it could be built-in. Just like you can choose from a list of supported sequence types, choose which method of ensuring uniqueness you want: unique constraint (as now) /filtered index (when Sql Server 2008 and higher).

Actually, I wonder if I could pick your brains as to the best way of doing this.

AccountsTransaction 1:0..1 NominalTransaction 1:m NominalEntry

(The NominalTransaction/NominalEntry can be considered atomic. My post-create script replaces the FK constraint with an ON DELETE CASCADE one)

So an AccountsTransaction (target per entity root, int ID with SCOPE_IDENTITY()) is standalone. At some point (for certain inheritors), or immediately when created (for other inheritors,) a 'posting' is made which creates a corresponding NominalTransaction. I have a virtual CreateNominalPosting method which each inheritor overrides and produces a NominalTransaction tree.

Also, there is the situation where an AccountsTransaction will 'repost' so that means deleting the existing NominalTransaction tree and replacing it with a new one.

Currently, NominalTransaction has its own int PK with SCOPE_IDENTITY(). But since it can't (or shouldn't!) exist without an AccountsTransaction, I wondering if I can 'reuse' the ID somehow from the AccountsTransaction to ensure it can't get orphaned. That plus an ON DELETE CASCADE so that whenever an AccountsTransaction is deleted, the NominalTransaction is automatically deleted.

What do you think?

simmotech wrote:

Its not as quite simple as that though. No problem with the Create script (as long as those accursed Guid restraint names don't change ) I have a post-Create script to run.

Yeah the guid's are a blessing and a curse at the same time.

But all Update Scripts generated by LLBLGen seem to drop all constraints before making changes and reapplying them after. If I've replaced that constraint with a filtered index then the update scripts are going to have to be tweaked every time.

The UC's are dropped because if we keep them, some operations fail, so to avoid a tremendous amount of dependency checking on various actions, we simply drop the UCs and the FKs first, then run the actions, then re-create them (SSMS does this too btw).

That's why I was hoping it could be built-in. Just like you can choose from a list of supported sequence types, choose which method of ensuring uniqueness you want: unique constraint (as now) /filtered index (when Sql Server 2008 and higher).

It's a nice idea/feature to have, agreed. Just not that simple to add, I'm afraid. I'll add it to the list of stuff to look at for future versions, though it won't make it to v4.0, but maybe after that

Will answer your other post later today or tomorrow.

simmotech wrote:

Actually, I wonder if I could pick your brains as to the best way of doing this.

AccountsTransaction 1:0..1 NominalTransaction 1:m NominalEntry

(The NominalTransaction/NominalEntry can be considered atomic. My post-create script replaces the FK constraint with an ON DELETE CASCADE one)

So an AccountsTransaction (target per entity root, int ID with SCOPE_IDENTITY()) is standalone. At some point (for certain inheritors), or immediately when created (for other inheritors,) a 'posting' is made which creates a corresponding NominalTransaction. I have a virtual CreateNominalPosting method which each inheritor overrides and produces a NominalTransaction tree.

Also, there is the situation where an AccountsTransaction will 'repost' so that means deleting the existing NominalTransaction tree and replacing it with a new one.

Currently, NominalTransaction has its own int PK with SCOPE_IDENTITY(). But since it can't (or shouldn't!) exist without an AccountsTransaction, I wondering if I can 'reuse' the ID somehow from the AccountsTransaction to ensure it can't get orphaned. That plus an ON DELETE CASCADE so that whenever an AccountsTransaction is deleted, the NominalTransaction is automatically deleted.

What do you think?

It can't get orphaned, as the FK value in NominalTransaction points to an existing AccountsTransaction. Removing the AccountsTransaction will then fail if the corresponding NominalTransaction isn't removed first (or it requires a cascade delete action). Or am I missing something?

Removing an AccountsTransaction would be a rare occurrence so lets leave that.

The problem is the 'post'/'repost' and whether the NominalTransaction should point to the AccountsTransaction that it belongs to and/or whether the AccountsTransaction should point to the NominalTransaction that belongs to it. It is a 1:0..1 I suppose.

From the query point of view, fetching AccountsTransactions would probably want to fetch their NominalTransaction where present. But fetching NominalTransactions might also need to fetch their AccountsTransaction. So which side would the FK be better on? And could/should the AccountsTransaction PK be 'reused'?

Cascade Delete Actions are fine to use if they help.

This is bordering on consultancy I suppose so feel free to tell me to sod off and work it out for myself if you like.

Cheers Simon

I've seen some people solving this issue on the database side using a 1:n relation, while the FK side has a Unique key/index on the FK field.

Implementing the above a AccountsTransaction can exist without a corresponding NominalTransaction.

And NominalTransaction can only exists corresponding to a specific AccountsTransaction.