Unique Constraint with Null values in FK

T

Tom Lewis

I have two tables; "Facilities" and "Providers" that have
a 1:1 relationship when data exists in "Facilities" (using
the "ProvNum" column in each table), but null values are
permitted in the "Facilities" table.

I want to create a unique constraint for the "ProvNum"
field in the Facilities table, but when I do so, I get
unique key violation errors when I try to insert more than
one record with a null for "ProvNum"

How should I structure the relationships, indexes and/or
constraints to enforce this kind of relationship in the
these tables?

TIA

Tom
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Setting column width in a table with a macro is not working 0
FK Constraint Violation upon INSERT 15
Wizard unable to open form in Form or Datasheet view 1
Error=Cannot transfer project, A FK constraint was thrown 3
Unable to display values in 2nd list box from 1st list box selection 2
Counting unique values in multiple columns 4
access 2003 table relationships 2
Too Many Indexes Bug 5

Top