[GIS] Foreign Key is not added to destination table after the creation of a relationship class

Question

Let's say I have two tables A and B. The relationship between these two tables is as follows :

A (1,*) <—- (1,1) B

Now when I try to add a relationship class for these two tables on Arcatalog, I right-click on the .sde file in Arcatalog and then :

New ==> relationship class.

For the origin table I choose A, and for the destination table I choose B

(Is that right btw or should it be the opposite?)

For the type of the relation ship I select : Simple (Peer to Peer) relationship

Direction : Both

The cardinality of the relationship class : (1 – m) One to Many

And after that I get to select the primary key field in the origin table and the foreign key in the destination table:

As you can see for the foreign key it actually gives me the objectid of that table, now I need to understand if I have to manually add the foreign key field in that table and then select it here?

Shouldn't it be created automatically after the relationship class is created? Obviously when I click on Next and I go back to my postgresql table no foreign key is added.. and it's confusing 'cause I thought that once we define a relationship automatically the id of the origin table migrates to the destination table and becomes a foreign key!

I had to use this database in a web application using ASP.NET, with Entity Framework, and I got confused after Entity Framework has generated the model from my database (at first I added the FK manually).. So now I'm doing it all over again and I would like to do it the right way..

How can I define the foreign keys and the relationship classes from arcatalog?

Answer 1

@vince:

Relationship classes tell the geodatabase about existing relationships. The key columns need to exist. There are many reasons why the columns should be created with the table, most importantly, to avoid the fragmentation caused by adding a column.

If you don't define the relationships in the geodatabase (not all of which support the concept of foreign keys, so they are optional), then you don't get the benefits of relationship classes.

@BenSNadler:

The foreign key options in the dialog filter based on the input field data type. If the foreign key field does not exist you need to manually add it. I suggest using the arcgis tool add field management to be clear. The geodatabase relationship does not place any database level constraints on the field. To the DB it is just a regular field.

I added those fields manually and then created the relationship classes.