I found a thread that discusses the situation I am having (https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_20677681.html
), I am just trying to now figure out the best workaround for our situation.
Basically, we have Access back-end db that we are trying to convert to SQL 2008. In one of our tables, we have a non-primary key field that in Access has the following properties:
Allow Zero Length: Yes
Indexed: Yes (No Duplicates)
So this field contains Null values for many records, but for the records with entries, all entries are unique (this is a system ID for a particular software package some of our clients are on -- if they are not on this software package, the field is Null).
The table containing this field is joined to another table via a query in a one-to-many type relationship (it is a table of payroll dates for each client on that software package). This other table contains many date fields that the user needs to update. So there is a Form based on this query that the user uses to update these values, and it works just fine.
The issue is in that trying to move the table to SQL 2008, you are not allowed to have any field be both Unique and Not Null (as explained in that other thread I referenced). If we allow Nulls, we cannot make it Unique, and therefore it thinks we have a many-to-many relationship, making the query un-updateable. So our update Form no longer works.
We were trying to figure out ways around this. One idea we came up is to make the fields we want updated on the Form unbound, then have an AfterUpdate script to run some SQL code to update the table directly behind the scenes, then refresh the Form. Seems like it will be a bit of work, so I want to make sure we exhaust any other simpler options before we decide to go that route.
Does anyone know of easier/better ways of doing this?