Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820

    Unanswered: UNIQUE When Not Null or dependent on data in other column

    Basically i want to be able to restrict the values in a table so that all record have a unique non key identifier.

    however i need to keep old historic data that may have the same unique

    so i want to either reset the field for old records to null and have nulls ignored from the unique check or to be able to set an archive bit field to true and then have that record ignored by the unique check


    so basically FieldA is unique unless FieldA is null
    or FieldA is unique unless <condition = true>

    the exact situation is that i need to determine who the user is using the AD user name. however when a user leaves the AD user name is deleted and may be reused if in future if a user with a similar name joins. so only 1 user name is allowed in the active user list.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    You may find this article UNIQUE Column with multiple NULL values helpful.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    thank you that looks exactly what i'm after
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  4. #4
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    One of the other options i was considering was splitting out the user name into a separate table where it was Unique and referencing it as a 0 or 1 relation. however while it would work it complicates the management of the table
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    however while it would work it complicates the management of the table
    You mean you find creating a UNIQUE constraint on a not-unique column, with NULLs and an archive bit not complicated??

    I have never been in a situation where I ever needed such a construction. I strongly advise you to reconsider your design.

    Perhaps you could give us the structure of the involved table(s) (CREATE scripts) and some sample data, current situation and to-be situation. So we can better understand your problem and help you. The option you described, wasn't clear to me.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  6. #6
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Archive bits are standard here we can't ever delete data so that already there

    the problem is that the tables are now so bloated with historic data that people no longer check to see if there is an existing row before creating a new one. so this will take away the option. if there is an existing row they will have to archive the old record. which cures the nightmare of trying to figure out which user is the right user. as this way allows that to happen within the existing table format with out then having to hunt down every view, Stored proc and function that uses the existing table and shunt it onto the 2 replacements
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  7. #7
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    the other method i was considering that is actually proper 4th degree normal form is to create a Username table that contains the user name and the User ID then link this to the user table via the user ID

    in the username table the username is marked as unique and can only link to a single user
    hence removing any chance at having 2 users with the same username.

    so we get

    Code:
    Currently
    
    User Table
    User ID | Name         | Active | username
    --------+--------------+--------+----------
    1       | Mr Someone   | 1      | Some1
    2       | Ms Someone   | 0      | Some1
    3       | Someone else | 1      | Some1e
    
    
    Your Solution
    
    User Table
    User ID | Name         | Active | username | Unique Username
    --------+--------------+--------+----------+-----------------
    1       | Mr Someone   | 1      | Some1    | Some1
    2       | Ms Someone   | 0      | Some1    | Deleted 2
    3       | Someone else | 1      | Some1e   | Some1e
    
    
    Alternate Solution
    
    UserName Table
    UserName | User ID
    ---------+------------------
    Some1    |  1
    Some1e   |  3
    
    
    User Table
    User ID | Name         | Active 
    --------+--------------+--------
    1       | Mr Someone   | 1
    2       | Ms Someone   | 0
    3       | Someone else | 1
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  8. #8
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    "Your solution" is too much credit. I have only pointed you to a way to create a Unique index on a NULLable column. My solution is to reconsider the design.

    Your "Alternate Solution" is more the way I would implement it.

    You must check if IsActive 0/1 is enough. I would probably go with periods (StartDate / EndDatde) during which that person could be active. So your system would also keep track of history. Someone could be active for a period, inactive for the next period, then be active again.
    Code:
    UserName Table
    UserName | User ID
    ---------+------------------
    Some1    |  1
    Some1e   |  3
    
    
    User Table
    User ID | Name         
    --------+--------------
    1       | Mr Someone   
    2       | Ms Someone   
    3       | Someone else 
    
    UserActive Table
    User ID | StartDate       | EndDate 
    --------+--------------+--------
    1       | 2000-01-01   | 9999-12-31
    2       | 2001-01-01   | 2001-12-31
    3       | 2000-01-01   | 2001-10-31
    3       | 2002-05-01   | 2003-10-31
    3       | 2010-12-01   | 9999-12-31
    With the 0/1 solution, someone has to be there to switch that flag on or off.
    With an active period, all would go automatic, using the current date to determine all the people that are active. You could set the EndDate months in advance.

    Suppose that the 0/1 flag would be set by a nightly process and not by a manual one, you would still have to store the dates that define the period those persons are active, somewhere. Making the 0/1 flag derived/redundant.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  9. #9
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    i would tend to agree, and probably would if i was scrapping the existing database and starting from scratch
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  10. #10
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I reread this thread. You wrote
    Archive bits are standard here we can't ever delete data so that already there
    I forgot to take that into consideration while formulating my last post.

    I think the best design that is compliant with your company standards, is your "Alternate Solution".

    How is that IsActive bit set? Manually? Automatic?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  11. #11
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    depends on the data, some of them are manually set by developers in the databases directly other are set when users click delete buttons in applications, still others are set by automated jobs that cleanse tables of old or obsolete data.

    this one is currently set manually however the aim is to eventually have an automated job that will update user information from AD and if the record is no longer in AD set the active flag to 0
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •