Primary keys composed of multiple fields?

[PatriciaW]

I'm an experienced database developer but not very experienced with FileMaker. I've been unable to find an answer to this question:

Is it possible to define a unique primary key which is composed of multiple fields? I can define several fields which are unique but really what I want is a compound key which is unique but not the fields which make it up. Is this possible?

[PatriciaW]

I'm an experienced database developer but not very experienced with FileMaker. I've been unable to find an answer to this question:

Is it possible to define a unique primary key which is composed of multiple fields? I can define several fields which are unique but really what I want is a compound key which is unique but not the fields which make it up. Is this possible?

[PatriciaW]

I'm an experienced database developer but not very experienced with FileMaker. I've been unable to find an answer to this question:

Is it possible to define a unique primary key which is composed of multiple fields? I can define several fields which are unique but really what I want is a compound key which is unique but not the fields which make it up. Is this possible?

[Vaughan]

Yes... using a calculation field that concatenates the fields together.

The real trick of course is ensuring that the concatenated value really is unique. There is no direct way of validating a calculation field but an article in the TechInfo database explains how to do it with a self-join and a lookup (search for validating calculation fields or summat similar).

The biggest problem with using calculated remote keys is that new related records cannot be created automatically through the relationship (via the blank row at the bottom of the portal) because data cannot be entered into the remote key field because it is a calc.

[Vaughan]

Yes... using a calculation field that concatenates the fields together.

The real trick of course is ensuring that the concatenated value really is unique. There is no direct way of validating a calculation field but an article in the TechInfo database explains how to do it with a self-join and a lookup (search for validating calculation fields or summat similar).

The biggest problem with using calculated remote keys is that new related records cannot be created automatically through the relationship (via the blank row at the bottom of the portal) because data cannot be entered into the remote key field because it is a calc.

[Vaughan]

Yes... using a calculation field that concatenates the fields together.

The real trick of course is ensuring that the concatenated value really is unique. There is no direct way of validating a calculation field but an article in the TechInfo database explains how to do it with a self-join and a lookup (search for validating calculation fields or summat similar).

The biggest problem with using calculated remote keys is that new related records cannot be created automatically through the relationship (via the blank row at the bottom of the portal) because data cannot be entered into the remote key field because it is a calc.

[PatriciaW]

Hmmm .... sounds rather kludgey ... I'm surprised there isn't a more elegant way of doing this as it is a standard way of creating a database. Thanks for the clarification.

[PatriciaW]

Hmmm .... sounds rather kludgey ... I'm surprised there isn't a more elegant way of doing this as it is a standard way of creating a database. Thanks for the clarification.

[PatriciaW]

Hmmm .... sounds rather kludgey ... I'm surprised there isn't a more elegant way of doing this as it is a standard way of creating a database. Thanks for the clarification.

[Fenton]

You can use the calculated key to create records in a portal, if you're willing to define the targetted field as text. Then you can pull the 2 parts out again into separate fields if necessary afterwards, and even allow them to be enterable in that table, by using an auto-enter by calculation.

Or you can use a button to run a script to create related records.

Why not have an auto-entered serial ID as the primary key, and have this calculation field as a secondary key? Use the calculation key where it needs to be used (I don't know exactly what that would be). But use the serial primary key anywhere else. That way you would not be relying entirely on the calculation.

[Fenton]

You can use the calculated key to create records in a portal, if you're willing to define the targetted field as text. Then you can pull the 2 parts out again into separate fields if necessary afterwards, and even allow them to be enterable in that table, by using an auto-enter by calculation.

Or you can use a button to run a script to create related records.

Why not have an auto-entered serial ID as the primary key, and have this calculation field as a secondary key? Use the calculation key where it needs to be used (I don't know exactly what that would be). But use the serial primary key anywhere else. That way you would not be relying entirely on the calculation.

[Fenton]

You can use the calculated key to create records in a portal, if you're willing to define the targetted field as text. Then you can pull the 2 parts out again into separate fields if necessary afterwards, and even allow them to be enterable in that table, by using an auto-enter by calculation.

Or you can use a button to run a script to create related records.

Why not have an auto-entered serial ID as the primary key, and have this calculation field as a secondary key? Use the calculation key where it needs to be used (I don't know exactly what that would be). But use the serial primary key anywhere else. That way you would not be relying entirely on the calculation.

[bruceR]

He's using Filemaker 7, which supports multiple fields in a relationship. Just pick your fields and define the relation accordingly. No calcs required. The error is thinking that this has to be a single field. It doesn't.

[bruceR]

He's using Filemaker 7, which supports multiple fields in a relationship. Just pick your fields and define the relation accordingly. No calcs required. The error is thinking that this has to be a single field. It doesn't.

[bruceR]

He's using Filemaker 7, which supports multiple fields in a relationship. Just pick your fields and define the relation accordingly. No calcs required. The error is thinking that this has to be a single field. It doesn't.

[Fenton]

Yes, that's certainly more elegant. I forgot about that.

[Fenton]

Yes, that's certainly more elegant. I forgot about that.

[Fenton]

Yes, that's certainly more elegant. I forgot about that.

[PatriciaW]

Are you saying that it is possible? I have defined multiple fields in a relationship ... but what I couldn't find out was how the uniqueness was defined ... how the primary key was defined. I could define uniqueness for the individual fields but that is not what I want. I want the combination to be unique. Or have I missed the essence of this thread?

[PatriciaW]

Are you saying that it is possible? I have defined multiple fields in a relationship ... but what I couldn't find out was how the uniqueness was defined ... how the primary key was defined. I could define uniqueness for the individual fields but that is not what I want. I want the combination to be unique. Or have I missed the essence of this thread?

[PatriciaW]

Are you saying that it is possible? I have defined multiple fields in a relationship ... but what I couldn't find out was how the uniqueness was defined ... how the primary key was defined. I could define uniqueness for the individual fields but that is not what I want. I want the combination to be unique. Or have I missed the essence of this thread?

[xochi]

I can't answer your question about uniqueness of multi-field keys in FM7.

However, I have come across a big difference between a concatenated calculate4d primary key and multi-field keys. The issue is how null values are handled. For example, if you have a key that is "LastName-FirstName-MiddleName", this works fine in FM6 and FM7 even when one of the names is blank. However, if you create this as a three field key in FM7, then you'll find that if any one of the key fields is empty, the relationship won't match.

I suppose this is how it should work, but this is a "gotcha" to be aware of. It's not always the case that using 3 fields for a key is better than making a calculated single field... I was happily converting all of my calculated field keys to multi-key relationships when I discovered that people with no middle names were suddenly not being handled properly

(And yes, i know in general it's a bad idea to use name fields as keys, this is a special case where I'm cleaning up dirty data from another agency...)

[xochi]

I can't answer your question about uniqueness of multi-field keys in FM7.

However, I have come across a big difference between a concatenated calculate4d primary key and multi-field keys. The issue is how null values are handled. For example, if you have a key that is "LastName-FirstName-MiddleName", this works fine in FM6 and FM7 even when one of the names is blank. However, if you create this as a three field key in FM7, then you'll find that if any one of the key fields is empty, the relationship won't match.

I suppose this is how it should work, but this is a "gotcha" to be aware of. It's not always the case that using 3 fields for a key is better than making a calculated single field... I was happily converting all of my calculated field keys to multi-key relationships when I discovered that people with no middle names were suddenly not being handled properly

(And yes, i know in general it's a bad idea to use name fields as keys, this is a special case where I'm cleaning up dirty data from another agency...)

[xochi]

I can't answer your question about uniqueness of multi-field keys in FM7.

However, I have come across a big difference between a concatenated calculate4d primary key and multi-field keys. The issue is how null values are handled. For example, if you have a key that is "LastName-FirstName-MiddleName", this works fine in FM6 and FM7 even when one of the names is blank. However, if you create this as a three field key in FM7, then you'll find that if any one of the key fields is empty, the relationship won't match.

I suppose this is how it should work, but this is a "gotcha" to be aware of. It's not always the case that using 3 fields for a key is better than making a calculated single field... I was happily converting all of my calculated field keys to multi-key relationships when I discovered that people with no middle names were suddenly not being handled properly

(And yes, i know in general it's a bad idea to use name fields as keys, this is a special case where I'm cleaning up dirty data from another agency...)

[Vaughan]

I'd forgotten about the enhanced relationships in FMP 7 too.

Though it's dangerous to generalise, I'd hesitate to use a calc filed as a key for a primary relationship -- one that will be used to create related ecords fir instance. For this auto-entered serial numbers or other unique keys are preferred.

But for "pulling data out in interesting ways" relationships, calc fields are very flexible. In this case I'd be deliberately designing the calc so uniqueness (or the lack of it) is not an issue. Like adding a serial number to the calc to force each record to be unique, that sort of thing.

If you want to concatenate first and last names (for instance) to use as a match field... forget it. Nothing but trouble. Flawed concept. Do it right with unique serial numbers for each person.

[Vaughan]

I'd forgotten about the enhanced relationships in FMP 7 too.

Though it's dangerous to generalise, I'd hesitate to use a calc filed as a key for a primary relationship -- one that will be used to create related ecords fir instance. For this auto-entered serial numbers or other unique keys are preferred.

But for "pulling data out in interesting ways" relationships, calc fields are very flexible. In this case I'd be deliberately designing the calc so uniqueness (or the lack of it) is not an issue. Like adding a serial number to the calc to force each record to be unique, that sort of thing.

If you want to concatenate first and last names (for instance) to use as a match field... forget it. Nothing but trouble. Flawed concept. Do it right with unique serial numbers for each person.

[Vaughan]

I'd forgotten about the enhanced relationships in FMP 7 too.

Though it's dangerous to generalise, I'd hesitate to use a calc filed as a key for a primary relationship -- one that will be used to create related ecords fir instance. For this auto-entered serial numbers or other unique keys are preferred.

But for "pulling data out in interesting ways" relationships, calc fields are very flexible. In this case I'd be deliberately designing the calc so uniqueness (or the lack of it) is not an issue. Like adding a serial number to the calc to force each record to be unique, that sort of thing.

If you want to concatenate first and last names (for instance) to use as a match field... forget it. Nothing but trouble. Flawed concept. Do it right with unique serial numbers for each person.

[Fenton]

So, to summarize. It's not that you can't do it. You can validate it with a self-relationship, a compound relationship, with each of the fields = themselves (same on both sides). Then, in the validation by calculation set it that the self-relationship must be empty:

IsEmpty (self-relationship::field)

Do this for each of the fields.

This allows you to enter a unique combo once, but the next one will be stopped. Check validate "Always" to block imports of non-unique values.

But I'd be more comfortable using this as a secondary relationship, using a regular auto-entered serial ID as the primary ID, and using the calculated one only when necessary. It's not a big deal to have both. Auto-entered serial ID's require no maintenance, unless you do an update into a clone or something.

[Fenton]

So, to summarize. It's not that you can't do it. You can validate it with a self-relationship, a compound relationship, with each of the fields = themselves (same on both sides). Then, in the validation by calculation set it that the self-relationship must be empty:

IsEmpty (self-relationship::field)

Do this for each of the fields.

This allows you to enter a unique combo once, but the next one will be stopped. Check validate "Always" to block imports of non-unique values.

But I'd be more comfortable using this as a secondary relationship, using a regular auto-entered serial ID as the primary ID, and using the calculated one only when necessary. It's not a big deal to have both. Auto-entered serial ID's require no maintenance, unless you do an update into a clone or something.

[Fenton]

So, to summarize. It's not that you can't do it. You can validate it with a self-relationship, a compound relationship, with each of the fields = themselves (same on both sides). Then, in the validation by calculation set it that the self-relationship must be empty:

IsEmpty (self-relationship::field)

Do this for each of the fields.

This allows you to enter a unique combo once, but the next one will be stopped. Check validate "Always" to block imports of non-unique values.

But I'd be more comfortable using this as a secondary relationship, using a regular auto-entered serial ID as the primary ID, and using the calculated one only when necessary. It's not a big deal to have both. Auto-entered serial ID's require no maintenance, unless you do an update into a clone or something.