Professional Web Applications Themes

Unique constraint - Microsoft SQL / MS SQL Server

> I need to create a unique contraint on a column (WO_number) but I can't use > an index because the database must allow a null. Then I don't understand how you are going to enforce uniqueness. How do you know that two NULL columns (whose values are unknown, by definition) are the same or different? Maybe you could use a trigger to rollback the insert or update if the value in that column is NOT NULL *AND* already exists in the table....

  1. #1

    Default Re: Unique constraint

    > I need to create a unique contraint on a column (WO_number) but I can't
    use
    > an index because the database must allow a null.
    Then I don't understand how you are going to enforce uniqueness. How do you
    know that two NULL columns (whose values are unknown, by definition) are the
    same or different?

    Maybe you could use a trigger to rollback the insert or update if the value
    in that column is NOT NULL *AND* already exists in the table.


    Aaron Bertrand - MVP Guest

  2. #2

    Default Re: Unique constraint

    A unique column can be nullable but I take it your requirement is to allow
    any number of NULL values while maintaining the uniqueness of non-NULL
    values. You can enforce this with an indexed view:

    CREATE TABLE Sometable (id INTEGER PRIMARY KEY, wo_number INTEGER NULL)

    GO

    CREATE VIEW Sometable_wo_number
    WITH SCHEMABINDING
    AS
    SELECT wo_number FROM dbo.Sometable WHERE wo_number IS NOT NULL

    GO

    CREATE UNIQUE CLUSTERED INDEX ucl_wo_number ON Sometable_wo_number
    (wo_number)

    INSERT INTO Sometable VALUES (1,1)
    INSERT INTO Sometable VALUES (2,2)
    INSERT INTO Sometable VALUES (3,NULL)
    INSERT INTO Sometable VALUES (4,NULL)
    INSERT INTO Sometable VALUES (5,1) -- Fails

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Portas Guest

Similar Threads

  1. Unique Form inserting into many tables using unique id
    By Gabo Navarro in forum Macromedia Exchange Dreamweaver Extensions
    Replies: 0
    Last Post: September 16th, 06:25 PM
  2. Adding UNIQUE constraint on NULL column
    By Dave Smith in forum PostgreSQL / PGSQL
    Replies: 6
    Last Post: January 13th, 09:39 PM
  3. Syntax for UNIQUE constraint?
    By Guinness Mann in forum ASP
    Replies: 1
    Last Post: October 15th, 09:58 PM
  4. Best way to create this constraint?
    By Zig Mandel in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 1st, 12:52 PM
  5. Conditional Constraint?
    By Howard J. Rogers in forum Oracle Server
    Replies: 9
    Last Post: December 24th, 02:52 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139