SQL Server Foreign Key field to accept Null Value

[Kevin_S]

OK - First I realize this is in the wrong forum but this posts is kind of a request for help / mini-rant about developing schema in Access then upsizing to larger RDBMS so I thought I'd post it here as this forum tends to get more traffic then the SQL Server forum and it could possibly help out someone else looking to do development in both areas so... here goes... (ps - Mile if you must move this thread wont be mad at ya either )

Heres the problem:

Been developing a personnel database for our HR Department over the last few weeks. Set up is Access 2002 FE w/ SQL Server 2000 BE. Interaction between FE & BE done through a mix of ODBC and ADO/stored procedures. I used the Access FE to layout the table schema then upsized them to SQL Server. I have a relation between two of the primary tables (tblPosition & tblEmployee) set as so:

tblPosition
PositionID PK
etc..

tblEmployee
EmployeeID PK
PositionID FK
etc...

Relationship - one - to - one between PositionID

Now, this worked really well in Access as the one to one prevented more then one employee from being able to have a position ID AND an employee could also not have a position and still be in the system. This is required as, with a government agency of this size, there is an entity called a transfer request where employees are moving for one location to another or working out of class (basically without a position) until civil service can catch up with the change in their system which filters to ours.

Now, when I upsized this to SQL Server I had to index the PositionID FK field in the Employee table to create a one - to - one join and... whalla!... SQL Server will not accept null values in FK fields like Access will....

I could work around this by creating the join as one to many but this is not what I want because in theory then more then one employee could have the same position ID = bad, bad, bad!

First, a request, does anyone know a proper way to set up a one to one in SQL Server where the FK field will accept null values? I've been all over the internet and seen many positings similar to this but not a simple solution...

Also, this should be a warning to all of those out there looking to develop in one app and move the same design to another app... From now on I, personally, will only be developing the BE in the chosen BE medium and then doing FE's in the chosen FE medium - no more mixing!!!!

All help appreciated!
Kev

 

[Kevin_S]

Pat - thansk for the help...

When I upsized the example to SQL I still run into the same problem as before as SQL Server will not allow me to enter a null value on the right side of the 1-to-1 join. I tried it in both my db and the example you provided which I upsized as is. The FK index was set to create unique index with allow nulls set to true. I have attached two screen captures. One showing my table with what I have the key set to and the other the error that occurs when I try to add data to the second table in your example when I should be able to add records without adding related records to the first table... Your example works in Access but does not work in SQL Server (at least not on my server?)

Any idea how to get around this...?

Thanks,
kev

 

[Kevin_S]

thats what I was afraid off... thanks for the help

Kev