SAP CAPM Association Modeling

[vijaygn1990]

HI Team,

We are moving towards capm in our organisation. I have started experimenting. I am trying to establish association between 2 entities (something like foreighn key constraint).But am not able to. Assume I have two tables as below.

Table1:

PROGRAM_ID|VOUCHER_DESC

1ax-ofd | voucher 1

Table 2:
PROGRAM_ID|VOUCHER_DENOM

1ax-ofd | voucherxs5

1ax-ofd | voucherxs5

In above 2 tables, PROGRAM_ID is the key. I created the entities like below.But cannot achieve foreign key relationship.

entity VoucherProgram: managed{

key PROGRAM_ID : UUID @(Core.Computed : true);

VOUCHER_DESC : String(2000);

VoucherDenomination : Association to many VoucherDenomination on VoucherDenomination.PROGRAM_ID = $self;

}

entity VoucherDenomination: managed{

key DENOM_ID: UUID @(Core.Computed: true);

PROGRAM_ID: Association to VoucherProgram;

DENOMINATION: String(50)

}

By using above, am able to insert a record into VoucherDenomination directly, which is not right because the PROGRAM_ID has to exist in VoucherProgram entity. Kindly advise what am I missing here?

[andre96 · Accepted solution]

It seems that database constraints are not enabled by default, yet.

Please see https://cap.cloud.sap/docs/guides/databases#db-constraints for how to enabled them.

After adapting your settings (e.g. via `.cdsrc.json`), you can always check the SQL via `cds compile --to sql yourFile.cds`.

Please be aware of the warning boxes in the section I linked above. It redirects you to https://cap.cloud.sap/docs/guides/providing-services#assert-target , which describes the `@assert.target` annotation that you can use the ensure that an association target exists.

Kind regards,

Andre

[vijaygn1990 · Accepted solution]

In the same program, I want to do an entity select on join condition. I have used the below query but it gives me the folowing error.

 using {db.VoucherProgram as vp, db.VoucherDenomination as vd} from '../db/datamodel'; 

@protocol: 'rest'service VoucherProgramDenom@(path:'/getVoucherProgramDenom') 
{ entity VoucherProgramDenoms as select from vp { 
PROGRAM_ID, 
EXPIRY_DATE, 
VOUCHER_DENOM: Association to vd on VOUCHER_DENOM.PROGRAM_ID = PROGRAM_ID};
}
} 

<strong>[ERROR] srv\voucherprogramdenom.cds:9:9: Unexpected usage of structured type “VOUCHER_DENOM.PROGRAM_ID” (in entity:“VoucherProgramDenom.VoucherProgramDenoms”/element:“VOUCHER_DENOM”/on)</strong>