Multiple (composite) primary keys

[beach_bar]

Hi all,
Does anyone know how to do composite primary keys in alpha five v6? What I mean is I need two fields togeather to be the primary key. If someone could give me detailed instruction on how to handle this in relationships in sets it would be a great help.
Thanks,
Walter

[Rawlings]

Hi Walter,
From the set design editor, under the parent key or child key area, select <expression> from the drop down box. You should be able to select any two field or more to form an expression for your key. It will look something like FIELD1+FEILD2. You may have have to convert fields to the same type if one of the fileds is of different type. You may want to trim the first field too. An example will be ALLTRIM(FIELD1)+FIELD2. If field2 is numeric and field1 is character then it will look like ALLTRIM(FIELD1)+STR(FIELD2). If field2 is a date type and field1 is character then it will look like ALLTRIM(FIELD1)+DTOC(FIELD2).
The possibilities are endless. Goodluck.

RAWLINGS

[Stan Mathews]

Hi Walter,
From the set design editor, under the parent key or child key area, select <expression> from the drop down box. You should be able to select any two field or more to form an expression for your key. It will look something like FIELD1+FEILD2. You may have have to convert fields to the same type if one of the fileds is of different type. You may want to trim the first field too. An example will be ALLTRIM(FIELD1)+FIELD2. If field2 is numeric and field1 is character then it will look like ALLTRIM(FIELD1)+STR(FIELD2). If field2 is a date type and field1 is character then it will look like ALLTRIM(FIELD1)+DTOC(FIELD2).
The possibilities are endless. Goodluck.

RAWLINGS

I think that covers the instructions, here's a sample.

[beach_bar]

Multiple (composite) primary keys

Thank you all. I really appreciate the help.
Thanks again,
Walter

[Edward Larrabee]

It is not a good idea to trim field values when creating composite keys, unless you use a function like LEFT() to size them to predictable sizes. The problem with ALLTRIM() and RTRIM() is that they can make the first part of a composite key different lengths, which will result in very strange behavior when you attempt to sort or search with the key. For example (using periods for nulls), what would expect for the sort order here?

Field1    Field2   CompositeKey Using ALLTRIM()
Abc.....  Def...   AbcDef
Ab......  Defg..   AbDefg
Ab......  Aabc..   AbAabc

[beach_bar]

Thanks Edward, I'll keep that in mind.
Thanks,
Walter

[masteralone]

One thing I do when I know I am creating a composite key, both fields involved will be character and both fields start with a character. Sounds silly, but works like this:

Field one (lets call client Id, and autoincrement field, field length 8, puts the letter c in the first position so you have a possible 9,999,999 clients, so whether it is c0000001 or c0001234 or c9999999, it is the same length at all times. The second field can be set up the same way. The combined field will be 16 chars in length and simply concatinate the first and second fields for the combined value.

You don't have to worry about dupes because they are always unique since the original value in each field is auto incremented. The length of the combined value will always be the same, and you don't have to trim them. A 16 character alphanumeric field should give you all the possible combinations you need.

And to be on the safe side, you can set the field rule on the combined to be unique assuring no possible duplication.


Oran