UNIQUE Column with multiple NULL values in visual studio

Question

I want to have a UNIQUE constraint on my Barcode column which may contain null values.

How do I create the constraint that allows multiple null values?

I use a local Database.sdf file (Microsoft SQL Server Compact 4.0 with the .NET Framework Data Provider for Microsoft SQL Server Compact 4.0).

I tried this :

SqlCeConnection con = null;
con = new SqlCeConnection(@"Data Source=|DataDirectory|\Database.sdf");
con.Open();

try
{
    SqlCeCommand cmd = con.CreateCommand();
    cmd.CommandText = "CREATE UNIQUE NONCLUSTERED INDEX Barcodex ON Products(Barcode) WHERE Barcode IS NOT NULL";
    cmd.ExecuteReader();
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}

but it is not working.

The error I got :

There was an error parsing the query,[Token line number =1,Token line offset =64,Token in error = WHERE]

Any help?

What version of SQL Server are you using? – Gordon Linoff Sep 21 '15 at 01:44 — Gordon Linoff, Sep 21 '15 at 01:44
@GordonLinoff SQL Server 2008 R2 – Dr ZIZO Sep 21 '15 at 01:47 — Dr ZIZO, Sep 21 '15 at 01:47

Mitch Wheat · Answer 1 · 2015-09-21T03:02:14.567

3

Update: Filtered Indexes are NOT supported in SQL Server Compact 4.0

Ref: Differences Between SQL Server Compact and SQL Server

The TSQL syntax is correct. (Assuming you have SQL Server 2008+. Filtered Indexes were introduced in SQL Server 2008)

Change this:

        cmd.ExecuteReader();

to this

        cmd.ExecutNonQuery();

Also, you should wrap your connection in a using block.

edited Sep 21 '15 at 03:02

answered Sep 21 '15 at 01:41

Mitch Wheat

295,962
43
465
541

I have a unique column on each row , I tried @GordonLinoff code below but it throw error `There was an error parsing the query,[Token line number =1,Token line offset =64,Token in error = AS]` – Dr ZIZO Sep 21 '15 at 12:25

Gordon Linoff · Answer 2 · 2015-09-21T15:14:03.763

0

Filtered indexes were introduced in SQL Server 2008. Prior to that version, you would get an error on the where clause in such a statement. If you are using a more recent version, perhaps your compatibility settings are causing the problem.

The compatibility level is a way to have a given version of SQL Server behave like an earlier version. Levels 80 and 90 are for 2000 and 2005, so would not allow your syntax. Here is some documentation.

If you have a unique id on each row, then you can use a computed column and unique index for the same effect:

alter table product add barcode_productid as (case when barcode is null then productid end);

CREATE UNIQUE NONCLUSTERED INDEX Barcodex ON Products(Barcode, barcode_productid);

edited Sep 21 '15 at 15:14

answered Sep 21 '15 at 01:47

Gordon Linoff

1,242,037
58
646
786

Could you explain what compatibility settings could cause this problem ? – Dr ZIZO Sep 21 '15 at 01:49
The code throw an error `There was an error parsing the query,[Token line number =1,Token line offset =64,Token in error = WHERE]` – Dr ZIZO Sep 21 '15 at 12:25

UNIQUE Column with multiple NULL values in visual studio

2 Answers2