Re: SqlServerCe foreign key problem

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

<marnix.koerselman@xxxxxxxxx> wrote in message
news:07444966-684c-41d0-9597-de901b4b659a@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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



.

Relevant Pages

  • Re: SqlServerCe foreign key problem
    ... VolumeId and FieldId are identity columns and so are auto generated by ... Just for display purpose, all rows that are not sent to engine, but are ... CONSTRAINT PK_Volume PRIMARY KEY, Label nvarchar)"; ...
    (microsoft.public.sqlserver.ce)
  • Re: SqlServerCe foreign key problem
    ... VolumeId and FieldId are identity columns and so are auto generated by ... Just for display purpose, all rows that are not sent to engine, but are ... CONSTRAINT PK_Volume PRIMARY KEY, Label nvarchar)"; ...
    (microsoft.public.sqlserver.ce)
  • Re: removing last chrs (with different browsers giving different last chrs )
    ... I normally stick to regexp even if is_numericor type casting would also suffice, since the regexp engine is needed anyway, and I've set up a collection of frequently needed expressions as class constants. ... could very well be a primary key. ... In this case foreign key integrity would be violated (after all we are using a "real" relational database engine, ... A detailled error message is precisely the wrong thing in this case - it won't help the user, the developer has his or her own sources, but the evil bot will feast on that. ...
    (comp.lang.php)
  • Re: Whats Informix thinking or Idea
    ... hidden indexes with usable names. ... This is one major reason I still ... You only created the constraint that owns ... place the engine will simply rename the index to a hidden name, ...
    (comp.databases.informix)
  • Re: Welchen Sinn haben BeginLoadData und EndLoadData (DataTable)
    ... >> nicht mit Einschränkungen (CONSTRAINT) da sie nämlichen ... zwei Kunden fusionieren, erzeugt man einen neuen Kunden und hängt die ... der Datenbank blind alle mögliche Logik ... leichter von Engine zu Engine zu Engine ...
    (microsoft.public.de.german.entwickler.dotnet.datenbank)