Can Unique Index ignore Nulls?

[Sandy]

Hello -

I am trying to create a unique index for values in 2 fields. However, one of
the fields does not alway contain a value. Is it possible to create some
logic for the index that works something like this:

If the value in field B is not null, then only allow unique values for the
combination of field A and Field B

Many thanks
sandy

 

[Jerry Whittle]

You can't really fudge with indexes too much. There isn't much that you can
do besides the defaults.

A unique index can allow nulls. However in your case, if A is the same and B
is null in two or more records, you can't have a unique index.

BTW: One of the differences between a unique index and a primary key is that
the PK can not have nulls in it.

 

[Sandy]

Thanks, Jerry -

My PK always has a value in it, and there is always more than one null for
the second field for that PK value.

So, I will have to build a query to limit the options available from the
drop down used to add the records -- let see how I do with that!

thanks again,
sandy

 

[Jamie Collins]

I am trying to create a unique index for values in 2 fields. However, one of
the fields does not alway contain a value. Is it possible to create some
logic for the index that works something like this:

If the value in field B is not null, then only allow unique values for the
combination of field A and Field B

That's exactly how things work in Jet out of the box e.g.

CREATE TABLE Test
(
A INTEGER NOT NULL,
B INTEGER,
UNIQUE (A, B)
)
;
INSERT INTO Test (A, B) VALUES (1, NULL)
;
INSERT INTO Test (A, B) VALUES (1, NULL)
;
INSERT INTO Test (A, B) VALUES (1, NULL)
;
INSERT INTO Test (A, B) VALUES (1, 2)
;
INSERT INTO Test (A, B) VALUES (1, 2)
;

Only the last insert above fails.

Jamie.

--

 

[Jamie Collins]

A unique index can allow nulls. However in your case, if A is the same and B
is null in two or more records, you can't have a unique index.

Really? Then how do you explain this:

Sub MultiNullUnique()
Kill Environ$("temp") & "\DropMe.mdb"
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe.mdb"
With .ActiveConnection
Dim sql As String
sql = _
"CREATE TABLE Test" & vbCr & "(" & vbCr & " A INTEGER" & _
" NOT NULL," & vbCr & " B INTEGER," & vbCr & " UNIQUE" & _
" (A, B)" & vbCr & ")"
.Execute sql
sql = _
"INSERT INTO Test (A, B) VALUES" & _
" (1, NULL)"
.Execute sql
sql = _
"INSERT INTO Test (A, B) VALUES" & _
" (1, NULL)"
.Execute sql
sql = _
"INSERT INTO Test (A, B) VALUES" & _
" (1, NULL)"
.Execute sql

sql = _
"SELECT A, B FROM Test"
Dim rs
Set rs = .Execute(sql)
MsgBox rs.GetString(, , , , "{{NULL}}")
End With
Set .ActiveConnection = Nothing
End With
End Sub

Jamie.

--

 

[Sandy]

Hi Jamie -

When I try to create the UNIQUE (A, B) index [unclustered] in the existing
table, I get this error:

The duplicate key value is (6, <NULL>)

My tables are on an SQL server so I am working via SQL server Management
Studio

How do I implement the UNIQUE (A, B) index?

Thanks!

 

[Jerry Whittle]

I have a simple explaination:

ORA-00001: unique constraint (WHITTLEJ.SYS_C00189602) violated

Notice the ORA, as in Oracle, error above.........

Oracle and Access treat unique constraints differently. I need to remember
what hat I'm wearing when replying to these questions!

 

[Jamie Collins]

When I try to create the UNIQUE (A, B) index [unclustered] in the existing
table, I get this error:

The duplicate key value is (6, <NULL>)

My tables are on an SQL server so I am working via SQL server Management
Studio

How do I implement the UNIQUE (A, B) index?

LOL! Then Jerry was correct after all.

Suggestions:

1) Create a VIEW of the data which returns only the non-null values
then use --EITHER-- a unique index on the VIEW --OR-- use an INSTEAD
OF trigger on the VIEW.

2) Use a BEFORE TRIGGER on the table.

3) Use a calculated "nullbuster" column (google it).

Jamie.

--