2

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 ?

Improve this question
2
  • @AaronBertrand, wasn't that just a few days ago? I had not seen that but yes, this appears to be a duplicate of that one.
    – Solomon Rutzky
    Oct 28, 2014 at 15:13
  • yes I think it's a duplicate :) the site didn't suggest that question to me..
    – mounaim
    Oct 28, 2014 at 15:19

1 Answer 1

Reset to default
3

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;
Improve this answer
2
  • 1
    Thank you @srutzky for the complete documentation of the answer :)
    – mounaim
    Oct 28, 2014 at 15:08
  • Thank you. It works. The importan thing is to drop the UNIQUE constrain on the columns definition and than create this filtered index.
    – Oliver Nilsen
    Dec 7, 2021 at 10:44

Not the answer you're looking for? Browse other questions tagged or ask your own question.