ADO.NET Data Providers
There are no primary or candidate keys in the referenced table th...
Question:
Hello,
i am facing problem creating a database with tables which have contraints.
I receive the error: "There are no primary or candidate keys in the referenced table [SomeTableName] that match the referencing column list in the foreign key [SomeForeignKeyName]"
I realize that the column list in the [SomeTableName] table must be unique for foreign key constraints. But i am not trying to create any constraints! [SomeForeignKeyName] foreign key is a "Relation Only" relation for easy mapping. I cannot put the unique constraint on my column list because it cannot be unique! (besides, eventhough I make it unique it for debugging purposes, i still get the same error!)
here is info about the table structure:
Table 1 : Column 1 (Primary Key), Column 2, Column 3, Column 4
Table 2 : Column 1 (Primary Key), Column 2, Column 3 (Unique Constaint), Column 4 (Unique Constraint), Column 5, Column 6, Column 7
Table2_Table1_relation:
Table2.Column5 (parent) - Table1.Column3 (child)
Relation Only
Error message: There are no primary or candidate keys in the referenced table [Table2] that match the referencing column list in the foreign key [Table2_Table1_relation]
Any help would be greatly appreciate.
Answer1:
I'm sorry to inform you sir but this is not possible with DataSet. A DataSet is a cached representation of a database and like a real database, it sticks to the fundamental principles of the relational model. One of these principle states that in order for a key to become a FK of another table, it must be a primary key of that table. I suggest you leverage on BindingSource.Filter property in order to simulate the effect of a relationship which you can not achieve with the DataRelation object.
Answer2:
Hmm... how could that not be possible?
i am creating a relation, but not a constraint. These clearly have two different meanings for dataset, because i can create the relation without creating the constraint.
The strange thing is that a few weeks ago this implementation was working and now suddenly it doesn't work!
Answer3:
Did you ever get a resolution to this error?
Thanks for the update.
Answer4:
yes. It turned out that there was another table that was creating this error message. I went through the data rows one by one and realized that in the end, there were some rows that existed in the table before their parent row existed in the parent table.
but for getting the data i had no choice (special case) but to get the child data first.
so then i used EnforceConstaints = FALSE of the dataset at the beginning of fetching data and at the end of the process i set EnforceConstraints back to TRUE again. This way, i didn't have to worry about having the child rows in first; once all the necessary data was downloaded, i could enforce the constraints and didn't receive any error this time.
hope this helps.
Answer5:
I have already documented this error on my website. Check this link http://www.dotnetking.com/TechnicalComments.aspx?LogID=343.
Cheers
Alireza Ahmadi Aliabadi