I want to migrate data contained in an Oracle Table A to SQL Server. The table A contains a column CIN with unique constraint but mutliple null values. The problem is that SQL Server is rejecting the data. Is there any workaround for this ?
You can use a Filtered Index to remove the NULL values from consideration regarding uniqueness. You would first drop the existing
UNIQUE CONSTRAINT and then replace it with a
UNIQUE INDEX. These two objects are essentially the same given that a
UNIQUE CONSTRAINT actually exists via a
UNIQUE INDEX (the documentation for creating a column constraint states that
UNIQUE is a "constraint that provides entity integrity for a specified column or columns by using a unique index.").
CREATE UNIQUE NONCLUSTERED INDEX index_name ON SchemaName.TableA (CIN ASC) WHERE [CIN] IS NOT NULL;