topic Re: use multiple fileds as primary/foreign key in Data Management Questions

use multiple fileds as primary/foreign key

PaoloPensalfini — Fri, 22 Jan 2016 09:39:38 GMT

Hi everybody,

I'm guessing if in ArcGIS it is possible to use the join or create a relationship class commands using multiple fields as primary/foreign keys.

I mean...I've got field A, B and C. Separatley they are not a primary key, but if I concatenate A+B+C they become a primary key.

What I've done since today is creating an extra field that I populate with a concatenation expression using the field calculator. This is an annoying solution since I've to create a redundant field in both the primary/foreign tables (I often can't do that because of the feature class schema) and I've to "maintain" that redundant fields (executing the field calculator's concatenation expression each time there are some changings).

I can't believe there's not another more pratical solution...
I will appreciate any and every suggestion!
Thanks in advance,

Paolo

Re: use multiple fileds as primary/foreign key

DanPatterson_Retired — Fri, 22 Jan 2016 09:59:15 GMT

Are you talking about this? Appending related fields with a join—Help | ArcGIS for Desktop

or are you looking for a way for a persistent field?

Re: use multiple fileds as primary/foreign key

PaoloPensalfini — Fri, 22 Jan 2016 11:13:04 GMT

Thanks Dan for your answer but...it's not exactly what I need, I think.

I'm sorry for my english....I try to explain me better.

I want to join (or relate...it doesn't matter) the feature class "Parcel" with the external table "Parcelinfo". "Parcel" has got three fields:"A"(Administrative_Unit code), "B" (Parcel Number), "C" (Cadastrian sheet number). Separately they aren't unique and they can't be my primary key. Instead, the concatenation's content of A+B+C creates a unique primary key. I've got the same situation in the Parcels' table.

Is there a way to tell ArcGIS that there's not a unique field as a primary key, but the primary key is created using three fields (A+B+C)? Can I do that without phisically creating a new field with the content of A+B+C?

Thanks in advance,

Paolo

Re: use multiple fileds as primary/foreign key

DanPatterson_Retired — Fri, 22 Jan 2016 11:21:38 GMT

I couldn't find one... you might want to scroll through the tools in the toolbox tree to see if you can find one.

A quick tour of geoprocessing tool references—Help | ArcGIS for Desktop

Re: use multiple fileds as primary/foreign key

XanderBakker — Fri, 22 Jan 2016 11:32:31 GMT

If you are not allowed to add an additional field with the result of concatenating the 3 fields and cannot change the content of an additional field, you could use a script to obtain the information, but that will write the obtained information to (additional) fields. If it should be a join or relate that only exists in memory in a session of ArcMap, this will not be an option. Depending of the source format of the data, maybe Creating a query layer—Help | ArcGIS for Desktop is a solution for you.

Re: use multiple fileds as primary/foreign key

TedKowal — Fri, 10 Dec 2021 23:20:19 GMT

I believe you are referring to composite keys. I know you can do this when working with data table, I generally separate my tabular data from the geometry use the composite form of the type database you are using to reference multiple column primary keys.... an example:

CREATE TABLE Persons
(
  P_Id int NOT NULL,
  LastName varchar(255) NOT NULL,
  FirstName varchar(255),
  Address varchar(255),
  City varchar(255),
   CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)

I am not sure, however, if a feature class can do this?

Re: use multiple fileds as primary/foreign key

TedKowal — Fri, 22 Jan 2016 16:09:15 GMT

Another thought came to me, create a view of the data and use a composite index for the view.... then at least your concatenations would be automated and indexed

Re: use multiple fileds as primary/foreign key

PaoloPensalfini — Tue, 26 Jan 2016 08:40:27 GMT

Thanks very much indeed for your answers!

I wished ESRI had found a direct solution to composite keys....but it seems they have not yet found it .

I've found something interesting in the "Spatial ETL" module: Creating a Unique Identifier | CRCCalculator - FME Knowledge Center .

It's about the creation (virtual? phisical? ...and above all: "on the fly"? I mean...in the meantime I create a new feature?) of "Natural keys" (Natural key - Wikipedia, the free encyclopedia)

I'm not sure it could be the solution; I think it's only another way to create a new phisical "ID field" made of a new string based on two or more fields' content.

I'll give it a try!

@Ted: I'm not very expert in "data view" but it seems Arcgis creates a virtual view table so that I can't assign indexes (neither composite) to that.

Thanks again to everyone!
Paolo

Re: use multiple fileds as primary/foreign key

TedKowal — Tue, 26 Jan 2016 15:51:48 GMT

Very interesting article!

I was not referring to the Arcgis data view but to create your view within your database itself (outside of ESRI products). Then use this created view as a table within Arcgis .... may be a little old fashion but in the few tests that I did it seems to work with creating an automatic concatenation of multiple fields to a unique key index. This only works for all the tabular data fields (not the shape column).

Re: use multiple fileds as primary/foreign key

WesMiller — Tue, 26 Jan 2016 18:54:12 GMT

Have you thought about doing a Relationship Class?

Deciding between relationship classes, relates, and joins—Help | ArcGIS for Desktop