how to create composite key

[#1]

i have been working on making a data model for the northwind database. i am stuck at a point of removing synthetic keys. the thing is i don't know exactly about the composite key. can anyone please tell how to apply composite key and what should be its syntax?

[its_anandrjs]

And more details like below

Eg:-

FieldName1 &'|'& Fielname2 as Composite_key

autonumber( Region&Year&Month, 'Ctr1' ) as Key

autonumberhash128 ( Region, Year, Month )

There are many different scenarios for synthetic keys

1. If table columns has same different fields then concatenate those tables

Eg:

Tab1:

Load Field1,Field2 from location;

Concatenate

Tab2:

Load Field1,Field2 from location;

or they will auto concatenate

2. The below table is join with Key field or they associate with each other

Tab1:

Load

Field1&'|'&Field2 as Key,

Field1,Field2,FieldA from location;

Tab2:

Field1&'|'&Field2 as Key,

Load Field1,Field2,FieldB from location;

3.Qualify the table

Qualify *;

Tab1:

Load Field1,Field2 from location;

Tab2:

Load Field1,Field2 from location;

If using Qualify the field name of table after loading comes like

Tab1.Field1,Tab1.Field2

and

Tab2,Field1,Tab2.Field2

Hope this helps

[maxgro]

Field1 & '-' & Field2 & '-' & Field3 as Key

very good post about synthetic key, read it if you have time

Re: Should We Stop Worrying and Love the Synthetic Key?

also look in the help at

autonumber

autonumberhash.......

[tresesco]

Try like below:

Load

          Field1 & Field2 as Key,

         AutoNumber(Field1 & Field2) as Key2  

Form <>;

Note: Key2 is the better way to use in terms of performance.

[#5]

Autonumber(Field1,Field2....)

This will created distinct number for the combination.

After creating the key you can drop the common fields from any one of the table to remove synthetic keys and thereby associating with the newly created key.

Thanks,

Prabhu

[its_anandrjs]

On in detail you can use

FieldName1 &'|'& Fielname2 as Composite_key

autonumber( Region&Year&Month ) as Key

autonumber( Region&Year&Month, 'Ctr1' ) as Key

autonumberhash128 ( Region, Year, Month )

Hope this helps

[MK_QSL]

You can remove synthetic key by

1) Using Qualify function for any table load

2) Renaming field names having same name 

     i.e. Address as Customer Address

          Address as Supplier Address

3) Concatenate tables

4) Create a link table by using composite key

    i.e. ID & ' '& Address as Key

We can help you better way if you can share your apps or create a sample file and upload it.

[#8 · Accepted solution]

Its nothing but Combining the common fields which makes synthetic keys

Ex:

Tab1:

Load

Country,

Region,

Sales,

Products

From --------------;

Tab2:

Load

Country,

Region,

Customer,

Outstanding

From ---------------;

If you load these 2 tables directly your script contains synthetic keys (Country and Region-- common in both tables)

Sol:

Tab1:

Country&'-'&Region as Key    //Composite key

Sales

Products

From --------------;

Tab2:

Country&'-'&Region as Key   //Composite key

Customer,

Outstanding

From --------------;

[arjunkrishnan]

Create Composite Key Like That.It's Very Useful to Avoid Synthetic Key In Data Model

Field1 & '-' & Field2 & '-' & Field3 as Key

[muniyandi]

Hi Ravi,

i understood the script  and have one doubt in your script .

Now user want to select the country and region column.what will do ?

Regards,

Muni.