Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: Unique Where NOT NULL

    I've seen a few semi-solutions to this problem and thought I'd pose the question here for more opinions on what to run with...

    I want a column to contain only unique values, where NULL is allowed multiple times.

    NULL does not equal NULL [In my head anyway ] so how could a NULL value violate a unique constraint?

    Quick example to show what I mean
    Code:
    CREATE TABLE fun_with_dupes (
       name varchar(10) PRIMARY KEY
     , unique_thing int UNIQUE
    )
    INSERT INTO fun_with_dupes(name, unique_thing) VALUES ('George', NULL)
    INSERT INTO fun_with_dupes(name, unique_thing) VALUES ('Pat', 1)
    INSERT INTO fun_with_dupes(name, unique_thing) VALUES ('Sean', 2)
    INSERT INTO fun_with_dupes(name, unique_thing) VALUES ('Paul', NULL)
    George
    Home | Blog

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    th overhead will suck but you could enforce this with a before trigger.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The way to do this, and I think I got the idea from Pat, is to create an indexed view with a unique constraint:
    Code:
    --Creating a unique constraint on a nullable column using an indexed view
    CREATE TABLE dbo.SampleTable
    	(PKey varchar(10) PRIMARY KEY,
    	NullableUniqueColumn int)
    go
    
    CREATE VIEW SampleTable_constraint
    with	schemabinding --schemabinding required for indexed views
    as
    select	NullableUniqueColumn
    from	dbo.SampleTable --two-part naming convention required for schemabinding
    where	NullableUniqueColumn is not null
    go
    
    CREATE UNIQUE CLUSTERED INDEX [pk_SampleTable_constraint] ON [dbo].[SampleTable_constraint] 
    ([NullableUniqueColumn] ASC)
    GO
    
    INSERT INTO SampleTable(PKey, NullableUniqueColumn) VALUES ('George', NULL)
    INSERT INTO SampleTable(PKey, NullableUniqueColumn) VALUES ('Pat', 1)
    INSERT INTO SampleTable(PKey, NullableUniqueColumn) VALUES ('Sean', 2)
    INSERT INTO SampleTable(PKey, NullableUniqueColumn) VALUES ('Paul', NULL)
    --edited to fix logic error...
    Last edited by blindman; 09-26-08 at 12:29.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Hang on, this is a bit sleeker:
    Code:
    --Creating a unique constraint on a nullable column using an indexed view
    CREATE TABLE dbo.fun_with_dupes
    	(name varchar(10) PRIMARY KEY,
    	unique_thing int)
    go
    
    CREATE VIEW fun_with_dupes_constraint
    with	schemabinding --schemabinding required for indexed views
    as
    select	name
    from	dbo.fun_with_dupes --two-part naming convention required for schemabinding
    where	unique_thing is not null
    go
    
    CREATE UNIQUE CLUSTERED INDEX [pk_fun_with_dupes_constraint] ON [dbo].[fun_with_dupes_constraint] 
    ([name] ASC)
    GO
    
    INSERT INTO fun_with_dupes(name, unique_thing) VALUES ('George', NULL)
    INSERT INTO fun_with_dupes(name, unique_thing) VALUES ('Pat', 1)
    INSERT INTO fun_with_dupes(name, unique_thing) VALUES ('Sean', 2)
    INSERT INTO fun_with_dupes(name, unique_thing) VALUES ('Paul', NULL)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    A simple solution is to create another table.

    Fifth Normal Form says that every table should be nothing more or less than the join of its superkeys. If you follow that principle you will never have the problem you described.

    CREATE TABLE fun_with_dupes1 (
    name varchar(10) PRIMARY KEY
    );

    CREATE TABLE fun_with_dupes2 (
    name varchar(10) PRIMARY KEY REFERENCES fun_with_dupes1 (name)
    , unique_thing int NOT NULL UNIQUE
    );

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Thanks for the replies chaps, and apologies in the delay of this one!

    All 3 suggestions are ones I have read and considered, and now that I'm probably looking at this piece of work again today I will have a stab at implementing each.

    My gut reaction was normalising the schema, but (I know, a cop-out!) I am tentative to do this because of the legacy environment set up. Obviously this method would take more preparation time and a few more re-writes, but overall it is the better choice (IMO).

    The indexed view is a good one, and the easiest to implement (no impact on legacy work) and one I saw on the SQLTeam Blogs. One of the reasons I'm not so sure about putting this solution in place is because of the obscurity of the solution - I don't think the next guy to come along would have a clue how this constraint was being enforced.

    Which brings us neatly along to triggers... The overhead costs aren't too much of a problem to consider in this situation, but the obfusciation is still a problem. However, the ease of implementation is a plus point again here.

    Lots to consider. Like I said, I'll try play with this later today and report back with my findings.

    In the meantime, pros and cons for each option are more than welcome
    George
    Home | Blog

  7. #7
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    I guess you are not yet on SQL Server 2008, right.

    If you are, 2008 has a new feature called "filtered indexes" which is basically what you are looking for (you basically add a WHERE condition to the CREATE INDEX statement)

  8. #8
    Join Date
    Apr 2007
    Posts
    183

    A "simple" workaround is using calculated column

    And of course you have an IDENTITY column on the table, right?
    Code:
    CREATE TABLE	#Fun
    		(
    			rowid int identity(1, 1),
    			name VARCHAR(10),
    			thing INT,
    			uiq AS case when thing is null then '_' + cast(rowid as varchar(11)) else cast(thing as varchar(11)) end
    		)
    
    create unique index ix_fun on #fun (uiq)
    
    INSERT	#Fun
    SELECT	'George', NULL
    
    INSERT	#Fun
    SELECT	'Pat', 1
    
    INSERT	#Fun
    SELECT	'Sean', 2
    
    INSERT	#Fun
    SELECT	'Paul', NULL
    
    SELECT	*
    FROM	#Fun
    
    DROP TABLE	#Fun

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Creative, but ugly.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by Shammat
    I guess you are not yet on SQL Server 2008, right.
    Correct.
    Definately sounds like a good feature though - I'll try rememberise it!
    Quote Originally Posted by Peso
    And of course you have an IDENTITY column on the table, right?
    No...
    You're right though, it is a simple enough solution - another one to add to the pot.
    George
    Home | Blog

  11. #11
    Join Date
    Apr 2007
    Posts
    183
    If unique_thing column value is supposed to be positive only, the calculated column can be made easier with this

    uiq AS COALESCE(unique_thing, - RowID)

    Of the suggested methods [pre sql server 2008 anyway ;-) ]
    I prefer the calculated column approach (who coukld have guessed... ). It is also possible to make the column persistable.

    It is least invasive and doesn't require an indexed view with schemabinding.
    And I don't want to guess what happens with performance when inserting records into base table, and database engine has to update the clustered index on the view(s).
    Last edited by Peso; 09-30-08 at 17:17.

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I have two situations where I need to enforce this, one is on a varchar field, the other is on a uniqueidentifier field (it's populated from a separate system, hence why there may be NULLS).
    George
    Home | Blog

  13. #13
    Join Date
    Apr 2007
    Posts
    183
    For SQL Server 2008 using the sample code above, use this

    create unique index ix_fun on #fun (thing)
    where thing is not null

    to create the unique index over non-null values.

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Just to feedback.

    After much deliberation I've decided to go with Blindmans suggestion of an indexed view - I find it to be the least obfuscated, and the cleanest method I am able to implement on this 2005 instance.

    Cheers for all the advice chaps
    George
    Home | Blog

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Peso
    For SQL Server 2008 using the sample code above, use this

    create unique index ix_fun on #fun (thing)
    where thing is not null

    to create the unique index over non-null values.
    Sweet. That is the way it ought to be implemented.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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