Foreign Key referencing Unique Key not show as a Navigation

cjbiggs · Post by **cjbiggs** » Wed 28 Jul 2010 00:34

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

AndreyR · Post by **AndreyR** » Thu 29 Jul 2010 13:13

I have created a simple test working on my system. Here is the script for these tables.

Code: Select all

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)
);

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.

cjbiggs · Post by **cjbiggs** » Thu 29 Jul 2010 16:02

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.

cjbiggs · Post by **cjbiggs** » Thu 29 Jul 2010 16:14

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.

cjbiggs · Post by **cjbiggs** » Mon 02 Aug 2010 15:59

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.

AndreyR · Post by **AndreyR** » Tue 03 Aug 2010 12:39

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.

cjbiggs · Post by **cjbiggs** » Tue 03 Aug 2010 21:13

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)
);

AndreyR · Post by **AndreyR** » Wed 04 Aug 2010 14:28

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.

cjbiggs · Post by **cjbiggs** » Wed 04 Aug 2010 17:12

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

cjbiggs · Post by **cjbiggs** » Mon 09 Aug 2010 23:39

Any updates on this issue?

AndreyR · Post by **AndreyR** » Tue 10 Aug 2010 12:39

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.

cjbiggs · Post by **cjbiggs** » Mon 18 Oct 2010 14:57

Any Update on this issue?

AndreyR · Post by **AndreyR** » Mon 18 Oct 2010 16:26

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