none
Index Ignore Nulls not doing what I need--how to? RRS feed

  • Question

  • I have a table "Domains" with columns "ID" (primary key), "Domain", and "ParentDomainID". ParentDomainID is a non-required foreign key to Domains.ID.

    I'm trying to define an index that assures the combination of Domain and Parent Domain ID is unique, even though Domain rows needn't be unique--if they have unique, non-null, ParentDomainIDs.

    E.g. allow

    Domain, ParentDomainID
    "www", 1
    "www", 2
    "www", Null

    while disallowing either of these non-unique cases

    Domain, ParentDomainID
    "www", Null
    "www", Null
    "www", 1
    "www", 1

    But "Ignore Nulls" No in the index does not seem to prevent the first "disallow" case of two identical Domain/Null ParentDomainID rows. (I tried Ignore Nulls Yes just in case I was not really understanding what it meant to do. Same problem.) The second case is prevented just as expected.

    What am I missing? Is there a way to achieve my objective?

    Thanks in advance!

    Friday, January 12, 2018 5:54 PM

Answers

  • Yes. I can prevent the one case but not the other.

    I was afraid that this might not be do-able at the table definition level. More googling suggests the issue is that Null<>Null by definition. So ["test", Null] <> ["test", Null] since the first Null <> the second Null.

    I have a serious Love-Hate relationship with Data Macros. They work so well once they work...


    • Edited by Dick Watson Saturday, January 13, 2018 6:10 PM
    • Marked as answer by Dick Watson Sunday, January 14, 2018 2:29 AM
    Friday, January 12, 2018 6:30 PM
  • NULL is not a value, but the absence of a value, so NULL plus anything will always evaluate to NULL.  Similarly NULL compared with anything will always evaluate to NULL, nether TRUE nor FALSE.
     
    You can see this in the debug window:

    ? "www" + NULL
    Null

    If the expression is compared with itself:

    ? "www" + NULL = "www" + NULL
    Null

    A zero length string on the other hand is a value so:

    ? "www" + ""
    www

    A comparative operation in this case will evaluate to TRUE:

    ? "www" + "" = "www" + ""
    True

    So if the ParentDomainID column is of text data type disallow Nulls (Required property = TRUE) and use a zero-length string in place of a NULL.

    If, as is more likely, the ParentDomainID column is a number data type, then again disallow Nulls, but set its DefaultValue property to 0 (zero).

    If for some reason you really want to allow Nulls, then the only way I can think of by which you could achieve what you want, would be by applying a CHECK CONSTRAINT to the table.  The constraint would be something like this:

    (SELECT Domain FROM Domains WHERE ParentDomainID  IS NULL GROUP BY Domain HAVING COUNT(*) >1) IS NULL

    The CHECK CONSTRAINT would be supplementary to the index of course.  Note that in Access the ALTER TABLE statement to apply a  CHECK CONSTRAINT must be executed in code.  Unlike other DDL statements, it cannot be executed in the query designer's  SQL window.


    Ken Sheridan, Stafford, England

    • Marked as answer by Dick Watson Sunday, January 14, 2018 2:29 AM
    Friday, January 12, 2018 6:35 PM
  • .............if I try to use a zero value instead of Null I can't enforce referential integrity for ParentDomainID->ID.

    You can maintain referential integrity while having a foreign key value of zero by inserting a row into the referenced table:

    INSERT INTO ParentDomains(ParentDomainID,ParentDomain)
    VALUES(0,"N/A");

    It is sometimes thought by less experienced Access developers that a specific value cannot be inserted into an autonumber column.  This is not the case.

    You might want to use a different value for the ParentDomain column, of course.  The choice as to what is appropriate is yours; it could even be a zero-length string.  This not only solves the indexing problem, but removes the semantic ambiguity inherent in Nulls at column positions in a table.

    The above would be my preferred solution in this context, but if you wish to main the Nulls, then applying a CHECK CONSTRAINT is easily done.  The constraint should be recorded in the application's documentation of course.  Such constraints are an integral part of the database relational model.  A data macro solution doesn't immediately spring to mind, though I've not really given it any thought.  It might be possible.


    Ken Sheridan, Stafford, England

    • Marked as answer by Dick Watson Sunday, January 14, 2018 2:29 AM
    Friday, January 12, 2018 8:52 PM

All replies

  • Hi Dick,

    Probably not at the table level. Definitely at the form (data entry) level. Or maybe, you can use a data macro.

    Just a thought...

    Friday, January 12, 2018 6:20 PM
  • I thought I'd give it try and created the following:

    And then I tried entering data as follows and only got the error when expected:

    Did you get the same result?





    • Edited by .theDBguy Friday, January 12, 2018 6:34 PM
    Friday, January 12, 2018 6:25 PM
  • What am I missing? Is there a way to achieve my objective?

    Hi Dick,

    I can tell how I solved this.

    I gave every table in my applications an Autonumber PK, with a strict naming convention for both table name and its Id. This has advantages, because each Id is always unique. With the naming conventing I can automatically generate all kind of SQL-strings using the Id's to join the tables. As the Id's always are very short, it is the fastest way to retrieve information.

    Other fields can also be keyed, using a Secondary (or Alternate) Key. These Secondary Keys can be unique or may contain duplicate values.

    Imb.

    Friday, January 12, 2018 6:30 PM
  • Yes. I can prevent the one case but not the other.

    I was afraid that this might not be do-able at the table definition level. More googling suggests the issue is that Null<>Null by definition. So ["test", Null] <> ["test", Null] since the first Null <> the second Null.

    I have a serious Love-Hate relationship with Data Macros. They work so well once they work...


    • Edited by Dick Watson Saturday, January 13, 2018 6:10 PM
    • Marked as answer by Dick Watson Sunday, January 14, 2018 2:29 AM
    Friday, January 12, 2018 6:30 PM
  • NULL is not a value, but the absence of a value, so NULL plus anything will always evaluate to NULL.  Similarly NULL compared with anything will always evaluate to NULL, nether TRUE nor FALSE.
     
    You can see this in the debug window:

    ? "www" + NULL
    Null

    If the expression is compared with itself:

    ? "www" + NULL = "www" + NULL
    Null

    A zero length string on the other hand is a value so:

    ? "www" + ""
    www

    A comparative operation in this case will evaluate to TRUE:

    ? "www" + "" = "www" + ""
    True

    So if the ParentDomainID column is of text data type disallow Nulls (Required property = TRUE) and use a zero-length string in place of a NULL.

    If, as is more likely, the ParentDomainID column is a number data type, then again disallow Nulls, but set its DefaultValue property to 0 (zero).

    If for some reason you really want to allow Nulls, then the only way I can think of by which you could achieve what you want, would be by applying a CHECK CONSTRAINT to the table.  The constraint would be something like this:

    (SELECT Domain FROM Domains WHERE ParentDomainID  IS NULL GROUP BY Domain HAVING COUNT(*) >1) IS NULL

    The CHECK CONSTRAINT would be supplementary to the index of course.  Note that in Access the ALTER TABLE statement to apply a  CHECK CONSTRAINT must be executed in code.  Unlike other DDL statements, it cannot be executed in the query designer's  SQL window.


    Ken Sheridan, Stafford, England

    • Marked as answer by Dick Watson Sunday, January 14, 2018 2:29 AM
    Friday, January 12, 2018 6:35 PM
  • So if the ParentDomainID column is of text data type disallow Nulls (Required property = TRUE) and use a zero-length string in place of a NULL.

    If, as is more likely, the ParentDomainID column is a number data type, then again disallow Nulls, but set its DefaultValue property to 0 (zero).

    If for some reason you really want to allow Nulls, then the only way I can think of by which you could achieve what you want, would be by applying a CHECK CONSTRAINT to the table.  The constraint would be something like this:

    (SELECT Domain FROM Domains WHERE ParentDomainID  IS NULL GROUP BY Domain HAVING COUNT(*) >1) IS NULL

    The CHECK CONSTRAINT would be supplementary to the index of course.  Note that in Access the ALTER TABLE statement to apply a  CHECK CONSTRAINT must be executed in code.  Unlike other DDL statements, it cannot be executed in the query designer's  SQL window.
    The ParentDomainID is a Long Integer FK to the same table's column ID, an autonumber (long integer) primary key. So, I can't use a null string, and if I try to use a zero value instead of Null I can't enforce referential integrity for ParentDomainID->ID. So Null seems the least bad answer. I'll have to check out ALTER TABLE / CHECK CONSTRAINT. That certainly sounds like what I want, but I've never been down that path before. I'm thinking a Data Macro *might* be less opaque than a CHECK CONSTRAINT though.
    • Edited by Dick Watson Friday, January 12, 2018 7:17 PM
    Friday, January 12, 2018 7:04 PM
  • .............if I try to use a zero value instead of Null I can't enforce referential integrity for ParentDomainID->ID.

    You can maintain referential integrity while having a foreign key value of zero by inserting a row into the referenced table:

    INSERT INTO ParentDomains(ParentDomainID,ParentDomain)
    VALUES(0,"N/A");

    It is sometimes thought by less experienced Access developers that a specific value cannot be inserted into an autonumber column.  This is not the case.

    You might want to use a different value for the ParentDomain column, of course.  The choice as to what is appropriate is yours; it could even be a zero-length string.  This not only solves the indexing problem, but removes the semantic ambiguity inherent in Nulls at column positions in a table.

    The above would be my preferred solution in this context, but if you wish to main the Nulls, then applying a CHECK CONSTRAINT is easily done.  The constraint should be recorded in the application's documentation of course.  Such constraints are an integral part of the database relational model.  A data macro solution doesn't immediately spring to mind, though I've not really given it any thought.  It might be possible.


    Ken Sheridan, Stafford, England

    • Marked as answer by Dick Watson Sunday, January 14, 2018 2:29 AM
    Friday, January 12, 2018 8:52 PM
  • INSERT INTO ParentDomains(ParentDomainID,ParentDomain)
    VALUES(0,"N/A");

    Hi Ken, Dick,

    Be careful! This works, but only safely for an empty table.

    When inserting a record in this way, the seed of the table is also set to that value, in the example to 0.

    The next record that is inserted in the "normal" way, will have an autonumber (root + 1) of 1, which can be in serious conflict with an already existing record with autonumber = 1.

    Imb.



    • Edited by Imb-hb Friday, January 12, 2018 10:41 PM safely
    Friday, January 12, 2018 10:39 PM
  • This works, but only safely for an empty table.

    Not necessarily.  I should have mentioned that it's necessary to compact and repair the file after INSERTING the zero row.


    Ken Sheridan, Stafford, England

    Friday, January 12, 2018 11:05 PM
  • This works, but only safely for an empty table.

    Not necessarily.  I should have mentioned that it's necessary to compact and repair the file after INSERTING the zero row.

    Hi Ken,

    Not too handy because you have to compact the BE itself, and that is not always available, especially not in the production environment.

    Imb.

    Friday, January 12, 2018 11:30 PM
  • I'm sure the OP can decide for himself whether he can find the few seconds downtime necessary to execute the once only INSERT INTO statement and compact the file.


    Ken Sheridan, Stafford, England

    Friday, January 12, 2018 11:56 PM
  • While this is a often used model, the "clean" relational solution is using two tables. Domain ( DomainID, DomainName ) and DomainHierarchy ( ParentDomainID, ChildDomainID ). When using a combined table, you need to use a concrete value to mark the parent. As you have encountered NULL is not an option. The most common case is to use the same ID as "child" as the parent.
    Saturday, January 13, 2018 12:39 AM
  • This certainly has generated a lot of help and input. Thanks to everyone.

    Agreed, a separate table for the hierarchy is probably more, well, formally correct... But it doesn't really eliminate this problem, just moves it. My prior experience is that dealing with more tables in these linked list cases is a BAD thing as it will make Access get to "too many tables" problems twice as fast.

    Using the "magic" parent record ID also works. But the reasons that 0 is harder to get in there and causes problems once you force it in are symbolic of why I was hoping not to go that way. (Most people, myself included, have probably never pondered jamming arbitrary values in there with queries since the UI won't allow jamming arbitrary values in there.) And thanks for the "Compact and Repair" note. Sure enough, Access doesn't behave well after SQL inserting 0 into an autonumbered column that already has data. This is all in the desktop hacking stage, so doing this was no issue re. FE/BE stuff or anything else.

    The CHECK CONSTRAINT case would probably work "best", but it's opaque to get in there--requires using only ADO code, only supported in Jet v4--and opaque once it's in there and probably not a very future-proof solution. If I thought this kind of functioanlity was more likely to get mainstreamed into the Access product than deprecated from it, I might go this way.

    I've spent the whole afternoon futzing around with BeforeUpdate data macros. The IsInsert case is really easy. The Not IsInsert case, has proven unsolvable so far. The problem keeps coming back to variations on using LookupRecord and not being able to tell if the "duplicate pseudo key value" record you find is the uncommitted version of the record being changed/not being able to cope with a WHERE condition that's evaluating the primary key of a record being updated. I even rediscovered a BeforeUpdate problem that I then found I'd posted about some five years ago.

    So I'm going to punt and go with the "magic" parent record ID as the least bad solution.
    Saturday, January 13, 2018 3:57 AM
  • Using the "magic" parent record ID also works.

    Hi Dick,

    Your application is very analogues to an genealogical database, where each person has a father and a mother. Unfortunately the father and/or mother are not always known (Null-value).

    In such systems an artificial (autonumber) key is choosen as PK, which "solves" the uniqueness of any record. Fathers and/or mothers can then be "keyed" allowing duplcates (to handle the many not-known parents, and the fact that persons can have more children).

    In a genealogical database the artificial PK (autonumber) is "accepted" because different persons can have to many common attributes that a compound PK is not reliable enough.

    In my work to build a RAD-tool around Access, I reverted things, and I only use autonumbers as PK. Without any problems!

    Imb.

    Saturday, January 13, 2018 8:42 AM
  • In such systems an artificial (autonumber) key is choosen as PK, which "solves" the uniqueness of any record. Fathers and/or mothers can then be "keyed" allowing duplcates (to handle the many not-known parents, and the fact that persons can have more children).

    ...

    In my work to build a RAD-tool around Access, I reverted things, and I only use autonumbers as PK. Without any problems!

    I'm still missing the point you keep trying to make here.

    The table *does* use an autonumber column for PK, named ID. This is not a new thing to me. That's what I'm doing. It has another column, ParentDomainID, that is a foreign key referring back to that autonumbered, primary key, ID column in the same table, Domains. This is similar to your genealogy example except that there are some rows that, by definition, do NOT have parents. It's not that they are unknown and might be known/knowable at some future point. It's that it's known that they don't, can't, exist. The ParentDomain is, well, Null. The problem I started out trying to solve with index definition is assuring that both Domains and their parent are unique AND parent-less domains are also unique.

    Acceptable cases:
    ID, Domain, ParentDomainID
    901, "www", Null
    902, "www", 456
    903, "www", 457
    
    Unacceptable case #1, easy to catch with a compound index:
    ID, Domain, ParentDomainID
    901, "www", 456
    902, "www", 456
    
    Unacceptable case #2, can't catch with a compound index because 
    Null <> Null: the problem this thread is all about preventing: ID, Domain, ParentDomainID 901, "www", Null 902, "www", Null

    The magic ParentDomainID value method works because then you can force the column to be required and then you also avoid the problem of Null <> Null. But it create the problem of weeding out the magic number when walking the tree.

    ID, Domain, ParentDomainID
    456, "www", 0
    457, "www", 234
    458, "www", 236
    
    Compound index can now catch two duplicates here, the
    identical "parent-less" one pointing to the magic 
    ParentDomainID, and the identical one pointing to the 
    same ParentDomainID:
    ID, Domain, ParentDomainID
    456, "www", 0
    457, "www", 0 <- compound index not unique
    458, "www", 236
    459, "www", 236 <- compound index not unique

    If you have some way that the existing autonumbered, PK, column ID can solve this in place of the magic row, I'm all ears.

    • Edited by Dick Watson Saturday, January 13, 2018 9:44 AM
    Saturday, January 13, 2018 9:31 AM
  • The problem I started out trying to solve with index definition is assuring that both Domains and their parent are unique AND parent-less domains are also unique.

    Hi Dick,

    I do not believe you can make unique keys on parent-less domains in the way you use is.

    If Domains and parent Domains are the same entity, you could include the parent Domains in the Domain_table, to ensure that both Domains and parent Domains are unique by the DomainID. The ParentDomainID is then a self-reference to the Domain_tbl. And because Domains are uniqee, parent-less Domains are also unique.

    Imb.


    • Edited by Imb-hb Saturday, January 13, 2018 9:55 AM
    Saturday, January 13, 2018 9:53 AM
  • If you have some way that the existing autonumbered, PK, column ID can solve this in place of the magic row, I'm all ears.

    Some years ago Chris Date gave an interview to Mark Whitehorn, in which the question of Null foreign keys in the context of a unary relationship type was raised.  Date's recommendation was that the unary relationship type should be modelled by a table, i.e. one which resolves the relationship type into one one-to-one relationship type and one one-to-many  relationship type.

    This is Stefan's 'clean' solution. It solves the indexing problem as the Domains table no longer includes a foreign key, so the Domain column is a candidate key, which of course means that the column contains distinct values, which is what one would expect of a table named Domains.

    Where a domain has no parent domain, there is no row in the table modelling the relationship type.  The primary table modelling the relationship type is Domain, i.e. it is both the primary key and a foreign key, which is of course what defines a one-to-one relationship type.

    The question is whether the modelling of the unary relationship type by a table is a correct representation of the reality.  This depends on the semantics.  While modelling the relationship type in this way overcomes the indexing problem it still leaves the semantic ambiguity of a Null foreign key.  The absence of a row in the table has no more meaning than a Null foreign key.  In both cases there is nothing to tell us what the absence of a parent domain means.  We might interpret it as meaning something on the basis of our knowledge of the reality, but there is nothing in the data per se to support that interpretation.

    Consequently the model is a valid one if the semantic ambiguity is a reality, not just an artefact of the lack of any data to give us an unambiguous meaning.

    If, on the other hand, a Null foreign key, or the non existence of a row in the table modelling the relationship type, has an unambiguous meaning in the context of the reality being modelled, then modelling the unary relationship type by a table is not valid if that table represents that unambiguous meaning by the absence of row.  In this scenario the inclusion of a 'N/A' or similar value in the ParentDomains table and a ParentDomainID foreign key column in the Domains table would be the correct model.  I would, however, question the use of Domains as the table name in this context as the table is not modelling a distinct set of Domains, but a binary relationship type between domains and parent domains.

    It may of course be that 'N/A' is not the only appropriate interpretation of a Null foreign key.  It might also encompass the interpretations 'Unknown', 'Pending' etc, in which case there would be one row for each of these potential meanings in ParentDomains.  This means that a Null foreign key can't be replaced with a zero in each case of course.  The use of a zero, while I do this regularly in cases where a Null has only one possible interpretation, is merely a matter of superficial neatness.  As an autonumber the primary key of ParentDomains has no semantic significance; its values are arbitrary, so if there are multiple possible 'meanings' of a Null foreign key, the values of the primary key for those meanings can be anything distinct, at the whim of the autonumber mechanism.  

    The need for multiple 'N/A' or similar attributes is not uncommon when modelling discontinuous hierarchies of many types.  One of my online demos uses the pre 1997 local administrative areas in England of County, District and Civil Parish in the following model:

    Locations>----Parishes>----Districts>----Counties

    Not all districts had civil parishes, however.  In my county, Staffordshire, the districts of Stoke-on-Trent and Tamworth are such, the former because it was formerly a metropolitan County Borough, the latter because it is the smallest local authority in England and not divided into civil parishes.  Consequently, to maintain the continuity of the hierarchy it is necessary to include  multiple 'N/A' parishes in the Parishes table, one per 'parish-free' district, each with an entirely arbitrary ParishID key value in an autonumber column.


    Ken Sheridan, Stafford, England

    Saturday, January 13, 2018 1:27 PM
  • I'm still missing the point you keep trying to make here.

    Hi Dick,

    The "point" in a few words:

    - Use an artificial key as PK.

    - Forget the uniqueness of the compound index. Instead, allow duplicates.

    Imb.

    Saturday, January 13, 2018 1:46 PM
  • If Domains and parent Domains are the same entity, you could include the parent Domains in the Domain_table, to ensure that both Domains and parent Domains are unique by the DomainID. The ParentDomainID is then a self-reference to the Domain_tbl. And because Domains are uniqee, parent-less Domains are also unique.

    Yes, these subdomains and parent domains are the same thing for my purpose. Parent domains *are* in the Domains table. I've just never demonstrated that in any of my examples because it didn't seem relevant. Yes, the ID (autonumbered PK) gives each row uniqueness. No, that does nothing to prevent the exact same "Domain" name (a human readable thing) from being assigned to multiple rows. Which is what I was using indexes to accomplish. (Isn't this what "unique" indexes exist for?) Yes, ParentDomainID is a self-reference. I've said that a number of times.

    - Use an artificial key as PK.

    - Forget the uniqueness of the compound index. Instead, allow duplicates.

    But preventing duplicate domain names from getting entered, regardless of the prospect they'd have unique ID autonumbered PKs, is exactly what I'm trying to accomplish. There can be only one, say, google.com and one yahoo.com. Likewise, there can be *only one* finance (sub)domain with a parent domain of google.com even though there could also be *only one* finance (sub)domain of yahoo.com and, conceivably, *no more than one* finance top level domain.

    ID, Domain, ParentDomainID
    54, "google.com", Null
    55, "yahoo.com", Null
    56, "finance", 54
    57, "finance", 55
    58, "finance", Null
    59, "finance", Null /* <-- yes, unique ID, NO! duplicate name--unique PK doesn't prevent */
    60, "finance", 54 /* <-- yes, unique ID, NO! duplicate name--unique PK doesn't prevent */


    • Edited by Dick Watson Saturday, January 13, 2018 5:23 PM
    Saturday, January 13, 2018 4:49 PM
  • In this scenario the inclusion of a 'N/A' or similar value in the ParentDomains table and a ParentDomainID foreign key column in the Domains table would be the correct model.  I would, however, question the use of Domains as the table name in this context as the table is not modelling a distinct set of Domains, but a binary relationship type between domains and parent domains.

    In my use case, the ParentDomainID is self-referential. If I change the Table name to SiteBuckets instead of Domains, the problem doesn't change. Each SiteBucket has a name. Each SiteBucket may have a parent. Each SiteBucket with the same parent has to have a unique name. Each SiteBucket with no parent has to have a unique name. SiteBuckets with different parents can have the same name because the combined name is still unique. The table is modeling other things about the SiteBuckets besides this relationship. It's just the way I was trying to solve this. I wasn't trying for the SQL Good Housekeeping medal. I could have said to heck with the relationship and just typed out the full bucket name in the child domain rows and ignored modeling their relationship to the parent SiteBucket. That would make SiteBucket unique by itself:

    ID, SiteBucket
    4, "google.com"
    5, "yahoo.com"
    6, "finance.google.com" /* <--db doesn't know/care how this is related to "google.com" */
    7, "finance.yaho0.com" /* <--db doesn't know how this is related to "yahoo.com" or care 
    that it has a typo for the non-normalized parent "yahoo.com" */ 8, "finance"
    As noted, I'm proceeding with the magic ID=0, default, for ParentSiteBucketID, a required column, solution. Then the compound index, unique, prevents duplicate SiteBuckets as intended yesterday around this time. 


    • Edited by Dick Watson Saturday, January 13, 2018 5:24 PM
    Saturday, January 13, 2018 5:20 PM
  • But preventing duplicate domain names from getting entered, regardless of the prospect they'd have unique ID autonumbered PKs, is exactly what I'm trying to accomplish.

    Hi Dick,

    I begin to understand what is happening.

    I did a little experiment with a table with fields Domain (string) and ParentID (long integer). There is a unique key on the fields Domain, ParentID.

    When I add records, it is prohibited when both Domain and ParentID are the same, but I can add as many records with the same Domain and a Null in ParentId. No warning at all in that case. In fact very strange!!!

    I never realized that. Though I have many cases where I have the same situation, I have never seen duplicates.

    The reason for that is, that I hate to have a user to fill in a form completely, and after pressing the Validate button, see a screen where he/she has nothing to do with, and cannot store the record because of key violation.

    Instead, in every BeforeUpdate event I do a check to see whether the current value would give a key violation later with the validation, with an understyandable message. In that case the user can choose a different name or make the ParentID not Null.

    Perhaps this is also a workaround for you. Check whether the combination of Domain and ParentID (Null or Not Null) already exists in the table, and take appropriate action.

    Imb.

    Saturday, January 13, 2018 6:32 PM
  • In fact very strange!!!

    Not really.  A key violation occurs when the values in the relevant column(s) in two rows would be the same.  Anything containing a NULL can never be the same as anything else containing a NULL, even if everything else apart from the NULL is exactly the same.  This is one of the fundamental characteristics of NULL; any comparative operation involving a NULL will always evaluate to NULL, neither TRUE nor FALSE, so there will be no index violation.

    But don't confuse concatenation of a value and a NULL with a value containing a NULL.  The result of such a concatenation will be the other value(s), and won't contain a NULL, so a comparison on equality would never evaluate to NULL, e.g. "X" & NULL = "X" & NULL will evaluate to TRUE because it is the same as "X" = "X".  Indexes don't work like this, however.  Expressed as a Boolean expression what the index is evaluating is ("X" = "X") AND (NULL = NULL), which reduces to TRUE AND NULL, which evaluates to NULL.


    Ken Sheridan, Stafford, England

    Saturday, January 13, 2018 6:56 PM
  • Not really.

    Thank you, Ken.

    In this case I will let my brains prevail over my feelings.

    Imb.

    Saturday, January 13, 2018 7:20 PM
  • I did a little experiment with a table with fields Domain (string) and ParentID (long integer). There is a unique key on the fields Domain, ParentID.

    When I add records, it is prohibited when both Domain and ParentID are the same, but I can add as many records with the same Domain and a Null in ParentId. No warning at all in that case. In fact very strange!!!

    Bingo! See also 3rd post in thread by @.theDBguy

    Ken's explanation, as well as similar several places earlier in the thread, is correct as to the why of why this happens but doesn't really help answer the question of how it's "supposed to be" solved. Even the two table solution (one for domains and one for domain relationships) doesn't address the problem of assuring that these potentially multi-row hierarchies are unique. And maybe that's the core message: maybe it is just not "SQL Good Housekeeping" to try to enforce some multi-row uniqueness at the schema level--maybe the "SQL Good Housekeeping" method is to do that in business logic wrapped around the schema. Maybe I'm just slothfully trying to avoid coding the business logic and looking for a way to get the db schema to enforce it for me. The CHECK CONSTRAINT solution probably borders on "SQL Good Housekeeping" but, as noted, Access seems like it only "supports" it because of the brief romance Access had with DAO, not because they really want to.

    in every BeforeUpdate event I do a check to see whether the current value would give a key violation later with the validation, with an understyandable message. In that case the user can choose a different name or make the ParentID not Null.

    Perhaps this is also a workaround for you. Check whether the combination of Domain and ParentID (Null or Not Null) already exists in the table, and take appropriate action.

    If I were building a UI and lots of forms and events and code and all that sexy stuff this would be a great solution and I'd do it in a flash. But this db is just desktop hackery for my own purposes so pretty much I use it from tables and queries. (As I do with literally dozens of my desktop Access databases.) The BeforeUpdate data macro would be great but it has so many limitations and oddities that it really can't do what you describe doing from code.

    • Edited by Dick Watson Sunday, January 14, 2018 2:29 AM
    Saturday, January 13, 2018 7:37 PM