Devart Forums

My table has a foreign key reference to a Unique Key in another table. But that relationship does not show in Entity Developer as a Navigation Property on the entities. If I make the Unique Key into the Primary Key then it will show the relation in Entity Developer. If I have two foreign key constraints, with one foreign key referencing the Primary Key and the other foreign key referencing the Unique Key in the same table, then both relations shows up. But when I validate the model I get the following error message

1 The types of all properties in the Dependent Role of a referential constraint must be the same as the corresponding property types in the Principal Role. The type of property 'setup_type_name' on entity 'SSCS.DAL.EF.POSTGRES.SSCSPriceBook.Store.setup' does not match the type of property 'setup_type_id' on entity 'SSCS.DAL.EF.POSTGRES.SSCSPriceBook.Store.setup_type' in the referential constraint 'fk_setup_to_setup_type_name'. SSCS.DAL.EF.POSTGRES.SSCSPriceBook


2 The types of all properties in the Dependent Role of a referential constraint must be the same as the corresponding property types in the Principal Role. The type of property 'Setuptypename' on entity 'SSCS.DAL.EF.POSTGRES.SSCSPriceBook.Setup' does not match the type of property 'Setuptypeid' on entity 'SSCS.DAL.EF.POSTGRES.SSCSPriceBook.Setuptype' in the referential constraint 'Setuptype_Setup1'. SSCS.DAL.EF.POSTGRES.SSCSPriceBook

I have created a simple test working on my system. Here is the script for these tables. Could you please change these scripts so that I would be able to reproduce the issue?
I am using 4.95.152 build of dotConnect for PostgreSQL.

That worked for me on 4.95.146 build of dotConnect for PostgreSQL. But you have both key and unique key defined for the same type (integer). I will modify your script to reproduce the issue.

I modified your script and was able to reproduce my same issue. Here is your modifed script.
CREATE TABLE unique_test
(
id integer NOT NULL,
"unique_id" character varying(30),
"value" character varying(10),
CONSTRAINT unique_test_pkey PRIMARY KEY (id),
CONSTRAINT unique_test_unique_id_key UNIQUE (unique_id)
);
CREATE TABLE unique_test_detail
(
id integer NOT NULL,
"unique_fk_id" character varying(30),
"value" character varying(10),
CONSTRAINT unique_test_detail_pkey PRIMARY KEY (id),
CONSTRAINT unique_fk FOREIGN KEY (unique_fk_id)
REFERENCES unique_test (unique_id)
);


It is always like the FK in the Child/Dependent table want to map to the data type of the PK in the Parent/Referenced table. It worked in your for your script because you had them both as integer. My modified script has it as an integer and vchar. So that is why I can this error message.


1 The types of all properties in the Dependent Role of a referential constraint must be the same as the corresponding property types in the Principal Role. The type of property 'unique_fk_id' on entity 'SSCSPriceBookModel.Store.unique_test_detail' does not match the type of property 'id' on entity 'SSCSPriceBookModel.Store.unique_test' in the referential constraint 'unique_fk'. SSCSPriceBookModel


2 The types of all properties in the Dependent Role of a referential constraint must be the same as the corresponding property types in the Principal Role. The type of property 'Uniquefkid' on entity 'SSCSPriceBookModel.Uniquetestdetail' does not match the type of property 'Id' on entity 'SSCSPriceBookModel.Uniquetest' in the referential constraint 'Uniquetest_Uniquetestdetail'. SSCSPriceBookModel



I am upgrading to 4.95.152 build of dotConnect for PostgreSQL to see if I can the same issue.

I upgraded to 4.95.152 build of dotConnect for PostgreSQL and getting the exact same problem mention above.

Have you been able to verify this issue, because I also have another issue with Foreign Keys that I will be posted today as it relates to using two Foreign Keys in the same Dependent/Child Table to reference the same Primilary Key in the Parent/Referenced Table. I will create a seperate issue for this one.

Unfortunately, there is no support for such associations in Entity Framework.
At least one of Association Ends should point to Entity Key of an entity.
There is a discussion on StackOverflow concerning this problem, for example.

So why in your sample script it works and doesn't work when I modified your script to have a different data type (vchar rather than integer)? Neither one of your Association Ends are tied to the Entity Key.

CREATE TABLE unique_test
(
id integer NOT NULL,
unique_id integer,
"value" character varying(10),
CONSTRAINT unique_test_pkey PRIMARY KEY (id),
CONSTRAINT unique_test_unique_id_key UNIQUE (unique_id)
);
CREATE TABLE unique_test_detail
(
id integer NOT NULL,
unique_fk_id integer,
"value" character varying(10),
CONSTRAINT unique_test_detail_pkey PRIMARY KEY (id),
CONSTRAINT unique_fk FOREIGN KEY (unique_fk_id)
REFERENCES unique_test (unique_id)
);

Actually, it does.
If you open the .edml file you will see that the association points to the id column of the unique_test table.

Which is completely wrong. The Association should have been with the unique_id field rather than the id field. If it is not support like you mention, than the model should have reported an error.

This the phyiscal database association
CONSTRAINT unique_fk FOREIGN KEY (unique_fk_id)
REFERENCES unique_test (unique_id)

This is incorrectly generated edml





**** It should be referencing the unique_id field

Any updates on this issue?

We will investigate the possibility to change the behaviour.
We plan to add validation error in one of the future builds.
I will post here as soon as any new information is available.

Any Update on this issue?

The latest 4.95.180 build contains the validation error stating that the storage constraint is invalid in the considered scenario.