Referential integrity checks on a Complex datatype : Portal

S

Sumit Chandra

started a topic almost 3 years ago

Hi -

We are in process of finalizing data model for our domain.

We are using a complex datatype to store address . The definition attributes of this address complex datatype are:

Street

City

Region

Country

Postalcode

We have two lookup tables - Lkp_Region which has all the values of region and Lkp_Country which has all the values of country.

We need to establish a foreign key relationship between Lkp_Region and Region definition attribute of the complex datatype that we have created. We need to do the same between Country definition attribute of the complex datatype and Lkp_country.

Is this use case of applting foreign key constraints on fields of a complex datatype feasible ? and how we should go about implementing refrential integrity on columns of a complex datatype ?

Thanks for the response

Best Answer

F

Fathia Jama said almost 3 years ago

When creating a reference relationship, the relationship is on the entity not on a specific attribute. For example if you create a entity address with a complex data type, the relationship with region would create a reference to region from entity with its own attribute. FID_Region

F

Fathia Jama

said almost 3 years ago

Answer

When creating a reference relationship, the relationship is on the entity not on a specific attribute. For example if you create a entity address with a complex data type, the relationship with region would create a reference to region from entity with its own attribute. FID_Region

S

Sumit Chandra

said almost 3 years ago

Hi Fathia -

Thanks for the response.

To clarify my question - In the example that you have provided, my ask is - can the Region FK that is created on Address table, be part of 'InputAddress' complex datatype ? rather than existing as a simple attribute of Address entity ?

Thanks

F

Fathia Jama

said almost 3 years ago

No, the reference attribute is created once the relationship is created. We cannot force the reference attribute to be apart of the complex type.

S

Sumit Chandra

said almost 3 years ago

Thanks. That answers my question.

We were lookign to create an address data type . But we need to enforce country & region values via a refrence relationship. Guess, we will create the address fields as simple attributes and work from there.

Regards