Unique constraint error when there is no constraint

Question

We are using SQL CE 3.5 on tablet PCs, that synchs with our host SQL 2005 Server using Microsoft Synchronization Services.  On the tablets, when inserting a record, we get the following error:

A duplicate value cannot be inserted into a unique index. [ Table name = refRegTitle,Constraint name = PK_refRegTitle

But the only PK on this table is RegTitleID.

The table structure is:

[RegTitleID] [int] IDENTITY(1,1) NOT NULL,
[RegTitleNumber] [int] NOT NULL,
[RegTitleDescription] [varchar](200) NOT NULL,
[FacilityTypeID] [int] NOT NULL,
[Active] [bit] NOT NULL,

 

The problem occurs when a Title Number is inserted and a record with that number already exists.  There is no unique constraint on Title Number.

Has anyone else experienced this?

Answer 1

 

Hi,

 

    I did not understand your question. PK column is both UNIQUE and "NOT NULL" by definition of PK in SQL Compact. So, if you have a primary key column, you are likely to hit this error. A primary key, by default makes columns unique, but, identity itself does not. Why dont you look at information_schema.table_constraints to see, if there are any unique constraints on the table or not.

 

Thanks

Udaya

Answer 2

I just ran into a similar issue with SQL CE 3.5.

I created my db using SQL Server Management Studio. Using the designer, I set a column's Unique property to Yes (which was a FK column)
Later I set it back to No. Then weeks later, when trying to delete records from the table referenced by the FK column, I kept getting an error stating that an index (with the UQ prefix) did not exist. Not sure why it didn't get completely removed properly.

I couldn't find the UQ_TABLENAME_0000000000### constraint anywhere (such as information_schema.table_constraints). So...

I just set Unique property back to Yes which created a new Unique Index and then was able to delete my records. I realize that this is response is over a year later. But I might suggest adding the Unique Constraint, then removing it again to see if it clears things up.

Answer 3

Hi Dbronco,

This looks like a bug in sqlce. Thanks for reporting the issue.

Regards,
Vipul