none
Unique contraint issue RRS feed

  • Question

  • I created the following table with SQL Server Compact 3.5 SP1

    CREATE TABLE [Dummy](
     [ID] [int] NOT NULL,
     [Code] [nchar](4) NOT NULL,
     [State] [int] NOT NULL CONSTRAINT [DF_Rooms_State] DEFAULT 2,
     CONSTRAINT [PK_Dummy] PRIMARY KEY([ID]),
     CONSTRAINT [UQ_Dummy] UNIQUE([Code],[State])
    )

    adding the .sdf file to my Visual Studio 2008 SP1 smart device project creates a strong typed dataset. The unique constrains, however, are changed to a combined primary key. So there is no FindByID method, but a FindByIDCodeState method and

                DataTable addedItems = GetChanges(DataRowState.Added);
                foreach (DataRow row in addedItems.Rows)
                {
                    row["State"] = (int)DataRowState.Added;
                }
                Merge(addedItems);
                TableAdapter.Update(this);

    crashed because Merge considers eg. ID=5, Code="ABCD", State=2 and ID=5, Code="ABCD", State=4 as different while the database throws an duplicate key error.

    I think this is a bug or am I missing something?



    Monday, August 31, 2009 11:38 AM

Answers

  • > The unique constrains, however, are changed to a combined primary key.

    The DataSet is powerful enough to represent the PRIMARY KEY and UNIQUE key separately, like your SQL.  Use the Visual Studio DataSet designer to adjust the strongly-typed DataSet .xsd accordingly.
    Wednesday, September 2, 2009 12:43 AM