SqlServerCe foreign key problem

Discussion in 'microsoft.public.sqlserver.ce' started by marnix.koerselman, Dec 17, 2008.

  1. Hi,

    I'm probably an idiot, but I can't figure out what's wrong with this
    situation:

    -- create database --

    string connectionString = @"Data Source = |DataDirectory|
    \MyData.sdf";
    SqlCeEngine engine = new SqlCeEngine(connectionString);
    engine.CreateDatabase();

    SqlCeCommand cmd = new SqlCeCommand();
    cmd.Connection = new SqlCeConnection(connectionString);
    cmd.Connection.Open();

    cmd.CommandText = "CREATE TABLE Volumes (VolumeId int IDENTITY
    CONSTRAINT PK_Volume PRIMARY KEY, Label nvarchar(20))";
    cmd.ExecuteNonQuery();

    cmd.CommandText = "CREATE TABLE Files(FileId int IDENTITY
    CONSTRAINT PK_Files PRIMARY KEY,
    Path nvarchar(256), VolumeId int NOT NULL,
    CONSTRAINT FK_Volume FOREIGN KEY(VolumeId) REFERENCES
    Volumes(VolumeId))";
    cmd.ExecuteNonQuery();

    .... So now I have 2 tables, where each file row references a specific
    volume row (right?)

    -- run the code above, then import the generated database file
    (VS2008) to create Data Connection, Data Source, MyDataSet,
    TableAdapters ---

    -- try to put some data in the tables --

    TableAdapterManager tam = new TableAdapterManager();
    tam.FilesTableAdapter = new FilesTableAdapter();
    tam.VolumesTableAdapter = new VolumesTableAdapter();
    tam.UpdateOrder =
    TableAdapterManager.UpdateOrderOption.InsertUpdateDelete;

    MyDataDataSet dataSet = new MyDataDataSet();
    tam.VolumesTableAdapter.Fill(dataSet.Volumes);
    tam.FilesTableAdapter.Fill(dataSet.Files);

    MyDataDataSet.VolumesRow volume = dataSet.Volumes.AddVolumesRow
    ("label");
    MyDataDataSet.FilesRow file = dataSet.Files.AddFilesRow("path",
    volume);

    tam.UpdateAll(dataSet);

    .... UpdateAll throws an exception: "A foreign key value cannot be
    inserted because a corresponding primary key value does not exist.
    [ Foreign key constraint name = FK_Volume ]"

    I've also tried updating the Volumes table first using the
    VolumesTableManager, or adding the records in a different manner, and
    many other silly things, but same difference. Please... any advice/
    ideas?

    Regards, Marnix
     
    marnix.koerselman, Dec 17, 2008
    #1
    1. Advertisements

  2. Here is the problem:
    1) VolumeId and FieldId are identity columns and so are auto generated by
    engine (i.e. not set by application)
    2) Just for display purpose, all rows that are not sent to engine, but are
    in dataset would show "-1" as identity value
    3) When Volumes table row goes to engine, a identity value gets assigned to
    VolumeId
    4) When Files table row goes to engine, engine tries to validate the
    constraint, but there is no value (or default value -1) is being sent to
    engine and there is now row in Volumes table with VolumeId as "-1"; the
    insert fails.

    Hope that helps.

    Thanks,
    Laxmi
     
    Laxmi Narsimha Rao Oruganti, Dec 19, 2008
    #2
    1. Advertisements

  3. Here is the problem as found by my friend Imran:
    1) VolumeId and FieldId are identity columns and so are auto generated by
    engine (i.e. not set by application)
    2) Just for display purpose, all rows that are not sent to engine, but are
    in dataset would show "-1" as identity value
    3) When Volumes table row goes to engine, a identity value gets assigned to
    VolumeId
    4) When Files table row goes to engine, engine tries to validate the
    constraint, but there is no value (or default value -1) is being sent to
    engine and there is now row in Volumes table with VolumeId as "-1"; the
    insert fails.

    Hope that helps.

    Thanks,
    Laxmi
     
    Laxmi Narsimha Rao Oruganti, Dec 19, 2008
    #3
  4. Here is the problem as found by my friend Imran:
    1) VolumeId and FieldId are identity columns and so are auto generated by
    engine (i.e. not set by application)
    2) Just for display purpose, all rows that are not sent to engine, but are
    in dataset would show "-1" as identity value
    3) When Volumes table row goes to engine, a identity value gets assigned to
    VolumeId
    4) When Files table row goes to engine, engine tries to validate the
    constraint, but there is no value (or default value -1) is being sent to
    engine and there is now row in Volumes table with VolumeId as "-1"; the
    insert fails.

    Hope that helps.

    Thanks,
    Laxmi
     
    Laxmi Narsimha Rao Oruganti, Dec 19, 2008
    #4
  5. Hey Laxmi,

    Thanks for your response. Do you perhaps know of another way to
    accomplish this using identity columns? I would prefer to use
    automatically generated ID values in the model, rather than doing
    something in code to generate my own unique values.

    Regards, Marnix
     
    marnix.koerselman, Dec 19, 2008
    #5
  6. Why don't you try to use two updatable SqlCeResultSet, one for Volumes and
    another for Files. You then start creating a new 'volume' on the Volumes
    resultset and saving it, after which I guess you can obtain the identity
    value of the volume just created and use that value to fill the VolumeId
    column in the files table.

    --

    Alberto Silva
    www.moving2u.pt - R&D Manager
    http://msmvps.com/AlbertoSilva - Blog
    Microsoft MVP - Device Application Development
     
    Alberto Silva - Microsoft MVP - Device Application, Dec 19, 2008
    #6
  7. Excellent advise Alberto! As you may have guessed I'm fairly new using
    data access and c# (I did some ADOX and C++ in the past) and the
    Microsoft tutorials seem to favor use of DataSets. Thanks for showing
    me a new path.
     
    marnix.koerselman, Dec 20, 2008
    #7
  8. Too bad though... I was hoping to be able to use TableAdapters for
    event handling in different parts of my application. Seems not natural
    to combine this with ResultSets.
     
    marnix.koerselman, Dec 20, 2008
    #8
  9. Too bad though... I was hoping to use the events fired by
    TableAdapters to respond to added/removed rows elsewhere in my
    program. The combination of ResultSets and TableAdapters seems not a
    natural one.
     
    marnix.koerselman, Dec 20, 2008
    #9
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.