Associations without foreign key constraints

Discussion of open issues, suggestions and bugs regarding Entity Developer - ORM modeling and code generation tool
Post Reply
alheureu
Posts: 11
Joined: Mon 01 Oct 2012 20:03

Associations without foreign key constraints

Post by alheureu » Mon 01 Oct 2012 20:18

Hi, I'm wondering if it is possible to set associations between objects in entity developer even if there is no foreign key constraints in the underlying database. Yeah, I know having no such constraints is bad, but I'm working with an old system and I have to deal with that kind of stuff. :x

So, here's an example:
Table A contains a list of author and will be mapped to the author object.
Table B contains a list of books and will be mapped to the book object.
There is a logical mapping between the two tables, that is one author is associated with many books. Let's say I have a strange association column: author_name char(4) not nullable in the author table which maps to author_name char(4) nullable in the book table. Only there is no foreign key constraint to enforce this association.

Now I want to load one author in my C# application and I want to have all its book available too. I have to define the association in devart.

Is this possible? If not, any workarounds?
Any help will be appreciated,
Thanks a lot,

Shalex
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Re: Associations without foreign key constraints

Post by Shalex » Fri 05 Oct 2012 10:37

Which ORM (Entity Framework/NHibernate/LinqConnect) do you use? Anyway, it should be possible to create an association without foreign key constraint.

For example, in case of the Entity Framework model:
1. Add BOOK and AUTHOR tables to the model.
2. Create the 0..1 (Authors) to Many (Books) association and uncheck "Add foreign key properties to the child class" checkbox in Association Editor and set Referential Constraint Properties: AUTHOR Properties=AUTHORNAME, BOOK Properties=AUTHORNAME. Then press OK.
3. Right click on the diagram surface > Generate Database Script From Model, go through its steps and make sure that result schema corresponds to the original one:

Code: Select all

CREATE TABLE BOOK (
   BOOK_ID NUMBER NOT NULL,
   AUTHOR_NAME CHAR(4) NULL,
   CONSTRAINT PK_BOOK PRIMARY KEY (BOOK_ID)
);
CREATE TABLE AUTHOR (
   AUTHOR_NAME CHAR(4) NOT NULL,
   CONSTRAINT PK_AUTHOR PRIMARY KEY (AUTHOR_NAME)
);

alheureu
Posts: 11
Joined: Mon 01 Oct 2012 20:03

Re: Associations without foreign key constraints

Post by alheureu » Thu 11 Oct 2012 20:06

Well, you are right! I am using LinqConnect and I finally managed to handle associated objects even if my old DB model does not support foreign keys.

Thanks Shalex for giving me good advice.

Post Reply