Using UniqueConstraint as "dentifying fields"

Posts   
 
    
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 15-Aug-2013 10:17:27   

I have an entity/table that looks like this:-

CREATE TABLE [dbo].[Memento](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [TypeID] [smallint] NOT NULL,
    [UserID] [int] NULL,
    [TargetID] [int] NULL,
    [Value] [xml] NOT NULL,
 CONSTRAINT [PK_c7c20fc4ff69be32bf2368c603f] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [UC_fb6297f4d6e9afb5fb800f5f51d] UNIQUE NONCLUSTERED 
(
    [TargetID] ASC,
    [TypeID] ASC,
    [UserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


Now the ID column and associated PrimaryKey are completely redundant and I want to remove them and just use the UniqueConstraint exclusively.

But the designer is insisting that Identifying Fields are specified.

I've looked through the forums about not having a primary key specified and I see the answers generally say a PK is required to be able to support select/update scenarios which is fair enough. I also saw a reply by Frans about setting IdentifyingFieldsFollowDBPrimaryKeyConstraints to false which I don't want to do since all the other tables do have PKs,

However I don't understand why a Unique Constraint cannot be used as "Identifying Fields". As I understand it, it serves exactly the same purpose as a composite PK but allows nulls also.

LLBLGen Pro v4.0 Final (July 18th, 2013)

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 15-Aug-2013 10:38:13   

Because a table also can have a PK, and UC and a PK, or multiple UCs and a PK. Which one should be used as the identifying fieldset for the entity type? the PK? that's what's done now. The UC?, then that one has to be used always, not just for 1 relationship. We support UCs but not for identifying purposes. If they're 'sometimes' used as identifying keys, it's a sign the model is flawed: an entity type has 1 set of identifying fields. Not multiple. That set is the PK. One can identify rows (entity instances) in multiple ways, but the PK is the one which uniquely identifies the row. Having multiple uniquely identifying sets is therefore redundant. UCs are meant to have unique values for a field across all entity instances. That you can also use them for identifying rows is irrelevant. You can also identify rows without using UCs, e.g. when accidentally a unique set of values across fields is unique for all rows.

Why not define the UC as a PK on the table?

For entities we do require to have a PK, because we don't support readonly entities. It's therefore to prevent the user from running into crashes at runtime where the entity is saved or deleted. I know, it's likely you'll now ask why not use the UC as the PK, but that would mean the UC is not seen as a UC but as a PK. But that would mean it has to keep track of which UC is not to be seen as a UC but as a PK and which one is. furthermore, UCs might allow Nulls, PKs don't.

So the designer requires a PK to be set, the fields chosen will be marked as a PK on the table. If you're using a UC as a PK in the DB, simply define them as a PK. If you don't want to do this, define a view on the table and map the entity onto the view. Views don't need a PK, so the PK chosen in the designer isn't marked as a PK on the view. (but that's creating more work, so I'd go for defining the proper PK on the table)

Frans Bouma | Lead developer LLBLGen Pro
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 15-Aug-2013 12:33:33   

Wow - a wall of text to read. stuck_out_tongue_winking_eye

furthermore, UCs might allow Nulls, PKs don't

Exactly!

I don't think my model is flawed since both database and entities support it - only the designer doesn't. What I am trying to achieve database-wise is - a single UC (which is used always, not for just 1 relationship) - no PK - no relations (UserID and TargetID are not FKs) - uses adapter.FetchEntityUsingUniqueConstraint(entity, entity.ConstructFilterForUCTypeIDUserIDTargetID()) - the only reason for using a UC rather than a PK is that two of the three fields are nullable which is by design

This all actually works fine at the moment - I can read and write without using the ID at all. The only problem is that the int ID fields stored and brought back with every fetch are completely redundant. Now that I am planning to add potentially many thousands of records to this table, I thought it prudent to investigate removing that redundancy to save space and fetch time.

you'll now ask why not use the UC as the PK, but that would mean the UC is not seen as a UC but as a PK

I'm fine with it being called a PK as long as it doesn't lose the nullability of its fields. The database is perfectly happy having no PK and a UC. Seems to me that a PK is just a UC with all non-nullable fields. They are both capable of identifying rows uniquely "Identifying Fields" is insisting on a PK when a UC would do just as well.

The Entity to View workaround you mention - not marking the View with a PK is only half the problem - the PK still wants non-nullable fields so I don't think it will work.

So the DB supports UCs, entities can work using only UCs, its seemingly only the designer that isn't allowing this.

Why can't LLBLGEN support nominating a UC for identifying purposes? The UC is already generated in the DB, internally it could be stored in exactly the same way as a composite PK.

If it is possible but just not worth the effect then I'm fine with that but it would be a nice feature not only for my use case but would also help those users with legacy DBs as seen in the forums.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 16-Aug-2013 00:43:06   

If it is possible but just not worth the effect then I'm fine with that

It's by design that we opted for PKs, and over and above I'd recommend an Aritifical Key over a Natural Key, whether composite or not. As Natural Keys can change due to change in the business requirements.

simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 16-Aug-2013 15:04:33   

Walaa wrote:

It's by design that we opted for PKs, and over and above I'd recommend an Aritifical Key over a Natural Key, whether composite or not. As Natural Keys can change due to change in the business requirements.

I can see why you opted to use a PK as the Identifying Field, but why limit it to that?

I have already presented a good use case where an artificial key is not required and the Identifying Fields cannot be a PK due to their nullness.

If it were possible to allow a PK OR a 'single UC' as the Identifying Fields then everyone would be happy. Multiple UCs are a red-herring since multiple entities could be mapped each with a different UC as Identifying Fields in that extreme case.

I don't know the internals but a maybe a couple of template changes so that the usual ctor with a PK parameter is not generated. FetchEntityUsingUniqueConstraint is already there as is the ConstructFilterForUCXXXX. And a couple of designer changes on the form that selects IdentifyingFields - maybe a list of UCs as well as the Fields list. Or just detect that the Fields selected include one or more nullable fields and prompt - "do you want to make them non-nullable and create a PK or leave them nullable and use a UC instead".

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 17-Aug-2013 09:09:16   

Hi there. My two cents: What you are asking for was pointed before on forums, however IMHO and based on the answers on other threads, this is something you wont see as a supported feature. The reason is an architectural decision, nullability, ensure uniqueness, etc. Maybe in the future this will be considered, but in my opinion you should consider the proposed workaround instead.

Another workaround: take a dev DB that have those fields as 'NOT NULL' and construct your model on that, including those fields as Identifying Fields. Then as production, connect to your real legacy DB.

David Elizondo | LLBLGen Support Team
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 17-Aug-2013 12:37:38   

Hi Daelmo

What I am saying is that I don't think that that architectural limitation is still there since the introduction of model-first development. At least not as restrictive as before.

Your workaround indicates database-first development so is not practical in this case.

There isn't a 'proposed workaround' that I can see, (other than create a redundant identity column for a PK).

I can't see a will to add useful functionality here, so I'll leave it.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 19-Aug-2013 11:33:27   

I explained why this is and how to fix it (in model first). You insist in doing it differently. THat's fine, but in the end that's also your choice. THis whole problem isn't hard, it's very simple: you define the PK fields and the PK fields are marked as such in the DB table, using an update script. You want to keep these fields with a UC. That exact requirement is something we won't support. For the life of me I don't really see why this friction is necessary, to be honest: simply go with the PK, move on. Functionality wise, there's nothing different for you, the only thing different is that you currently have a UC containing table, and afterwards you have a PK containing table.

Frans Bouma | Lead developer LLBLGen Pro
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 20-Aug-2013 10:59:45   

Otis wrote:

Functionality wise, there's nothing different for you, the only thing different is that you currently have a UC containing table, and afterwards you have a PK containing table.

No, Frans that simply isn't the case, there is a clear difference:

I CANNOT use a PK (for the identifying fields) because the key is composite and two of the three fields MUST be nullable. This was explained in the first message.

I am not using a UC for fun - it is necessary for this use case. The use case is valid and works very well.

LLBLGen already supports fetching by UC, but the designer doesn't support fetching by UC exclusively - you have to add an artificial and redundant PK to satisfy the LLBL designer. That takes up database space and slows down query times.

If you don't want to remove that limitation (or it can't be removed due to some internal working that I don't know about) then thats fine - I realise it is not something that is going to affect many people.

But please don't tell me I'm wrong in "insisting on doing it differently" and "causing friction". The original question was misunderstood and the messages since have all been based on that misunderstanding.

Cheers Simon

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 20-Aug-2013 23:38:16   

simmotech wrote:

Otis wrote:

Functionality wise, there's nothing different for you, the only thing different is that you currently have a UC containing table, and afterwards you have a PK containing table.

No, Frans that simply isn't the case, there is a clear difference:

I CANNOT use a PK (for the identifying fields) because the key is composite and two of the three fields MUST be nullable. This was explained in the first message.

I am not using a UC for fun - it is necessary for this use case. The use case is valid and works very well.

LLBLGen already supports fetching by UC, but the designer doesn't support fetching by UC exclusively - you have to add an artificial and redundant PK to satisfy the LLBL designer. That takes up database space and slows down query times.

No it doesn't. The PK is needed, the UC isn't sufficient for this. You might disagree with this, but I have no time for endless debates on this. I have explained this clearly, an entity on a table needs a pk, I definitely won't change that, and I have given the reasons.

That the PK takes up DB space is silly: the PK in general is a clustered index, making it more efficient to retrieve rows. The UC is in general not clustered. IMHO, this whole debate is redundant. E.g. Your UC has 2 nullable fields. Databases threat nulls in UC differently, sql server accepts just 1 null, others multiple. Thats's not going to work for pk's or for pk behavior. The engine needs pk behavior from the identifying fields, this is deliberately designed that way and that won't change

Frans Bouma | Lead developer LLBLGen Pro
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 21-Aug-2013 11:33:37   

Databases threat nulls in UC differently, sql server accepts just 1 null, others multiple.

Ah, I might have found our confusion here!

What you just described is what I understand a UniqueIndex to be on Sql Server - only one null allowed in the column.

What I have been describing allows null (or any value) to appear in the same column multiple times but still ensures that the rows are unique:-

TypeID UserID TargetID 1 NULL NULL 1 NULL NULL

so whilst I can only have (1, null, null) once in the table, I can have (1, 1, null) and (1, null, 1) and (1, 1, 1)

I've not made this up (though now I look in the SQL Server docs, it doesn't explain this clearly), here is the generated creation code.

ALTER TABLE [dbo].[Memento] ADD CONSTRAINT [UC_fb6297f4d6e9afb5fb800f5f51d] UNIQUE NONCLUSTERED ( [TargetID] ASC, [TypeID] ASC, [UserID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

That the PK takes up DB space is silly

Putting aside the issue of being called silly disappointed , my understanding is this:- A "Primary Key" in a Table is a field exactly like any other. It takes up space, in my case 4 bytes per row. There is also a "Primary Key clustered index" which is a physically different thing. It too stores the Primary Key value plus a Page where the Row can be found. So that is another 4 bytes + <page index size> per row. When fetching rows from this table, the PK is returned as part of the resultset, therefore fewer rows can be returned within one network packet than without including the PK. Another way of looking at it is that it will take more network packets to return a large resultset. In my scenario, my UC would have a clustered index instead of the PK clustered index.

Therefore I stand by my claims that the PK, in this scenario, is both artificial and redundant and takes up database space and slows down query times.

It is my understanding that everything that you use a (composite) PK for could just as easily be done with a UC. I'm happy to be corrected if that isn't so.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 21-Aug-2013 12:07:33   

I really don't understand why you keep posting new posts in this thread. I explicitly explained why it is like it is and that I won't change it. You want a UC being used as a PK, that's not going to happen, sorry.

Frans Bouma | Lead developer LLBLGen Pro
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 21-Aug-2013 12:37:06   

I spent nearly two hours researching that last message just to make sure what I understood was indeed correct.

You made, IMHO, several key mistakes and incorrect assumptions in your previous messages which led to flawed non-answers. I tried to counter that in my last message by highlighting that and yet you dismiss it all in 2 seconds.

At least we've gone from"can't" change it to "won't" change it. That about sums it up.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 21-Aug-2013 13:25:05   

simmotech wrote:

I spent nearly two hours researching that last message just to make sure what I understood was indeed correct.

You made, IMHO, several key mistakes and incorrect assumptions in your previous messages which led to flawed non-answers. I tried to counter that in my last message by highlighting that and yet you dismiss it all in 2 seconds.

Yes I did, because otherwise I'd spend even more time on something I already told you would not change.

So the time I spent on this is filled with flawed non answers and incorrect assumptions...? disappointed Did I sign a contract with you so I am obligated to do what you tell me to do? Or is really the case that you want me to change something while I don't see why I should and I explained my reasons? I seriously am convinced it's the latter, not the former. So please don't act like I have an obligation towards you to do what you want me to do.

I can also say "Won't happen" and lock the thread, without an explanation.

At least we've gone from"can't" change it to "won't" change it. That about sums it up.

I never said I can't change it. I said I won't change it. Changing it isn't trivial, because if an entity has multiple UCs, which one is the PK-one? So one of them has to be marked as 'PK', hey, that's exactly what's already there: the PK feature. It also has to make sure there's NO way to define a PK once a UC is defined as 'pk', because that would mean there are 2 field sets identifying the entity, which one to pick for relationships?

It's so easy to stubbornly telling me my answers are flawed and actually non-answers, but in practice the one who's to implement your ideas is me. So if I say that it won't happen, it won't happen. I always try to be nice and explain why and I'll do that again if I have to, but with you I already know you'll reply and re-try and re-try.

So in the end, you can try but if I explain to you why not, you already know that the question has been asked before and it has already been debated before, perhaps even tried out before and it's not implemented for a reason.

Frans Bouma | Lead developer LLBLGen Pro
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 21-Aug-2013 15:21:36   

I am, and always have been, fully aware that this is your product and you exclusively decide what goes into it and what doesn't. Absolutely no problem there.

I am also aware that your knowledge and experiences of databases are far in advance of mine. I also agree that I might reply too much and retry and retry, however that is less about "getting my way" and more about exploring other options and checking facts and understandings.

I still stand by my comments about flawed-non answers and incorrect assumptions. Correct answers can't be given until incorrect assumptions are sorted first.

Why not define the UC as a PK on the table?

Because there are nullable fields.

we do require to have a PK, because we don't support readonly entities

Not relevant.

the only thing different is that you currently have a UC containing table, and afterwards you have a PK containing table.

Nope.

The PK is needed, the UC isn't sufficient for this.

Nope.

I have explained this clearly, an entity on a table needs a pk

Nope and nope.

That the PK takes up DB space is silly

Nope.

the PK in general is a clustered index

Nope a PK and a PK index are different things.

Databases threat nulls in UC differently, sql server accepts just 1 null

Nope.

<multi-nulls>...Thats's not going to work for pk's or for pk behavior

Nope.

The engine needs pk behavior from the identifying fields

Yes to "pk behaviour" and "identifying fields" but that doesn't have to mean PK.

I never said I can't change it. I said I won't change it

Forgive me but the above answers are quite categorical that only a PK will do, thus suggesting using a UC instead _can't _work.

Your other objections about which UC and 2 field sets identifying the identity are trivial to get around I believe.

I always try to be nice and explain why and I'll do that again if I have to

Forget the feature request, that isn't going to happen - I've got that.

But I still have a thirst for knowledge. Where is my logic wrong? In particular, is there anything a composite FK can do that a composite UC can't?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 21-Aug-2013 16:27:25   

wtf.... frowning

Frans Bouma | Lead developer LLBLGen Pro
Aglaia avatar
Aglaia
LLBLGen Pro Team
Posts: 535
Joined: 07-Sep-2003
# Posted on: 21-Aug-2013 16:33:04   

Simmotech, please understand Frans really tried explaining - something he doesn't have to, but he did take the time. He does have other things to do though, so I'm afraid I'm going to have close this thread.