Composite Primary key based on composite Foreign Key values generating a constraint violation

Data Abstract
1

Anton,

I have four tables:

SUBSCRIBER_278
REQUESTER_278
SPROVIDER_278
SERVICES_278

They are related as follows:

SUBSCRIBER_278 PK => UNIQUE_ID
REQUESTER_278 PK => HL_REQ_ID_NO, FK =>SUBSCRIBER_278.UNIQUE_ID
SPROVIDER_278 PK => HL_SPROV_ID_NO, FK =>SUBSCRIBER_278.UNIQUE_ID
SERVICES_278 PK => HL_SERV_ID_NO, FK =>SPROVIDER_278.UNIQUE_ID,SPROVIDER_278.HL_SPROV_ID_NO

The table definitions have been imported into the Schema Modeler and the Primary Keys have been set up as LargeAutoInc using Oracle Sequence Generators.

I am using Remote Data Adapter to Update the tables.

I am establishing the row level relationships between the tables as follows:

            var subscriberRow = svcCWAuth.SUBSCRIBER_278.NewSUBSCRIBER_278Row();
            subscriberRow.FillSubscriberRow(patientDBRecord.Subscriber);
            svcCWAuth.SUBSCRIBER_278.AddSUBSCRIBER_278Row(subscriberRow);
            var requesterRow = svcCWAuth.REQUESTER_278.NewREQUESTER_278Row();
            requesterRow.FillRequesterRow(patientDBRecord.Requester);
            requesterRow.UNIQUE_ID = subscriberRow.UNIQUE_ID;
            svcCWAuth.REQUESTER_278.AddREQUESTER_278Row(requesterRow);
            var sproviderRow = svcCWAuth.SPROVIDER_278.NewSPROVIDER_278Row();
            sproviderRow.FillProviderRow(patientDBRecord.SProvider);
            sproviderRow.UNIQUE_ID = subscriberRow.UNIQUE_ID;
            svcCWAuth.SPROVIDER_278.AddSPROVIDER_278Row(sproviderRow);
            var servicesRow = svcCWAuth.SERVICES_278.NewSERVICES_278Row();
            servicesRow.FillServicesRow(patientDBRecord.Service);
            servicesRow.UNIQUE_ID = sproviderRow.UNIQUE_ID;
            servicesRow.HL_SPROV_ID_NO = sproviderRow.HL_SPROV_ID_NO;
            svcCWAuth.SERVICES_278.AddSERVICES_278Row(servicesRow);

Without Relations declared in the Schema Modeler, I get the following exception (on the server) when trying to perform a table update:

ORA-02291: integrity constraint (CWEB.FK_REQUESTER_1) violated - parent key not found

When I define the relation: SUBSCRIBER_278.UNIQUE_ID => REQUESTER_278.UNIQUE_ID and try to update the table, I get the next exception:

ORA-02291: integrity constraint (CWEB.FK_SPROVIDER_1) violated - parent key not found

Likewise, when I define the relation: SUBSCRIBER_278.UNIQUE_ID => SPROVIDER_278.UNIQUE_ID and try to update the table, I get the next exception:

ORA-02291: integrity constraint (CWEB.FK_SERVICES_12) violated - parent key not found

So, when I set up the relation: SPROVIDER_278.UNIQUE_ID,HL_SPROV_ID_NO => SERVICES_278.UNIQUE_ID,HL_SPROV_ID_NO, I still get the following exception:

ORA-02291: integrity constraint (CWEB.FK_SERVICES_12) violated - parent key not found

I am unsure if we are defining the relations properly (I have tried to define them as two separate relation and had the same end result), or if there is something else going on.

Thanks,

Mark

2

Hello

Relations for the SERVICES_278 table should be defined as follows:
2017-10-05_16-11-48_chrome

2017-10-05_16-11-58_chrome

However there might be issues with applying the changes on the server depending on the way data adapter sends them. I’ll log an issue so this should be fixed for the next Beta.

Sorry for the inconvenience

3

Thanks, logged as bugs://78644

4

Hello

To get this working you need to do the following:

1.Adjust the relation definitions as stated in the post above (ie Master field should point to Primary Key to properly propagate AutoInc value during inserts)

2.Enforce the order Deltas are applied to the database (as we need to be sure that SUBSCRIBER_278 and SPROVIDER_278 tables are modified prior to the SERVICES_278 table, otherwise we won’t be able to set values of corresponding fields in the SERVICES_278 table):

2.1.Open the Schema Modeler

2.2.Select the Update Rules node

2.3.Press the ‘Add Update Rule’ button

2.4.In the Data Table Name combobox select the SUBSCRIBER_278 table

2.5.Go back to the Update Rules node and add another rule, for the table REQUESTER_278

2.6.Go back to the Update Rules node and add another rule, for the table SPROVIDER_278

2.7.Go back to the Update Rules node and add another rule, for the table SERVICES_278

3.Save the changes and close the Schema Modeler

Now database changes will be applied in the order tables are mentioned in the update rules, so AutoInc values will always be propagated properly.

Regards

5

Anton,

I’m also trying to solve the key management issue I have been able to get past the commit and run into an error here. I’ll try the normal step through the code and let you know what I find on the Delphi side. here’s the stacktrace at least…

Looks like a query was subsequently generated as well so it follows the stacktrace.

Thanks,
Monte, Mark and Haiqiao

2017/10/19 12:34:19.846 Loading ServiceSchemas from C:\SourceCode\development2\MTWebSvcB\SvcCCOK_MT_BServer\bin\x64\Debug\SvcBase.daSchema
2017/10/19 12:34:52.059 37b91457-cdb5-4d8b-a379-9514bd1453b6 Entering wsProcessLoadPreClaimInfo with pi_PARM_DATA
2017/10/19 12:34:57.143 37b91457-cdb5-4d8b-a379-9514bd1453b6 Entering Oracle_DoGetNextAutoInc with sequenceName = EDI_SEQ
2017/10/19 12:34:57.208 37b91457-cdb5-4d8b-a379-9514bd1453b6 Exiting Oracle_DoGetNextAutoInc EDI_SEQ 403811
2017/10/19 12:35:00.586 37b91457-cdb5-4d8b-a379-9514bd1453b6 Entering Oracle_DoGetNextAutoInc with sequenceName = EDI_BATCH_SEQ
2017/10/19 12:35:00.606 37b91457-cdb5-4d8b-a379-9514bd1453b6 Exiting Oracle_DoGetNextAutoInc EDI_BATCH_SEQ 8202723
2017/10/19 12:35:03.500 37b91457-cdb5-4d8b-a379-9514bd1453b6 Entering Oracle_DoGetNextAutoInc with sequenceName = EDI_PC_BILL_SEQ
2017/10/19 12:35:03.517 37b91457-cdb5-4d8b-a379-9514bd1453b6 Exiting Oracle_DoGetNextAutoInc EDI_PC_BILL_SEQ 9810206
2017/10/19 12:35:04.358 37b91457-cdb5-4d8b-a379-9514bd1453b6 Entering Oracle_DoGetNextAutoInc with sequenceName = CLM_TRACKING_NBR_SEQ
2017/10/19 12:35:04.365 37b91457-cdb5-4d8b-a379-9514bd1453b6 Exiting Oracle_DoGetNextAutoInc CLM_TRACKING_NBR_SEQ 28524291
2017/10/19 12:40:40.233 37b91457-cdb5-4d8b-a379-9514bd1453b6 Error executing processLoadPreClaimInfo service System.Data.SyntaxErrorException: Syntax error: Missing operand after ‘12’ operator.
at System.Data.ExpressionParser.Parse()
at System.Data.DataExpression…ctor(DataTable table, String expression, Type type)
at System.Data.Select…ctor(DataTable table, String filterExpression, String sort, DataViewRowState recordStates)
at System.Data.DataTable.Select(String filterExpression, String sort, DataViewRowState recordStates)
at RemObjects.DataAbstract.DataAdapter.MergeTableDelta(DataTable table, Delta delta, Boolean acceptChangesDuringUpdate, Boolean briefcase)
at RemObjects.DataAbstract.DataAdapter.MergeTableDelta(DataTable table, Delta delta)
at RemObjects.DataAbstract.DataAdapter.MergeDelta(DataSet dataset, IEnumerable1 deltas) at RemObjects.DataAbstract.DataAdapter.InternalUpdate(DataSet dataset, String[] tableNames) at RemObjects.DataAbstract.DataAdapter.Update(DataSet dataset, String[] tableNames) at RemObjects.DataAbstract.DataAdapter.Update(DataSet dataset) at SvcCCOK_MT_B.SvcEDI.ProcessLoadPreClaimInfo(Guid& pi_CONVERSATION_ID, List1& selectedDataFields, U_KV_Pair[][]& pi_PARM_DATA, U_KV_Pair[][]& po_RESPONSE_DATA, MTMethods methodName) in C:\SourceCode\development2\MTWebSvcB\SvcCCOK_MT_BServer\SvcEDI\SvcEDI.cs:line 1598

at SvcCCOK_MT_B.SvcEDI.ProcessLoadPreClaimInfo(Guid& pi_CONVERSATION_ID, List`1& selectedDataFields, U_KV_Pair[][]& pi_PARM_DATA, U_KV_Pair[][]& po_RESPONSE_DATA, MTMethods methodName) in C:\SourceCode\development2\MTWebSvcB\SvcCCOK_MT_BServer\SvcEDI\SvcEDI.cs:line 1598
2017/10/19 12:07:09.265 Entering SvcEDI_BeforeGetData with sender SvcCCOK_MT_B.SvcEDI
2017/10/19 12:07:10.090 TableName = TPARTNER
2017/10/19 12:07:10.093 Parameter = SELECT TOP (100) [t0].[CREATE_BY], [t0].[CREATE_DATE], [t0].[MODIFY_BY], [t0].[MODIFY_DATE], [t0].[TPARTNER_KEY], [t0].[TPARTNER_NAME], [t0].[TPARTNER_NOTE]
FROM [TPARTNER] AS [t0]
2017/10/19 12:07:22.250 Entering SvcEDI_BeforeGetData with sender SvcCCOK_MT_B.SvcEDI
2017/10/19 12:07:23.243 TableName = EDI_BATCH
2017/10/19 12:07:23.245 Parameter = SELECT [t0].[ACK_CODE_LK], [t0].[ACK_DATE], [t0].[BATCH_ACTION_CODE], [t0].[BATCH_CONTROL_NBR], [t0].[BATCH_CREATE_DATE], [t0].[BATCH_ORIG_CONTROL_NBR], [t0].[BATCH_PUPROSE_CODE], [t0].[BATCH_TRANSACTION_AMT], [t0].[BATCH_TRANSACTION_COUNT], [t0].[BATCH_TRANSACTION_TYPE_CODE], [t0].[CREATE_BY], [t0].[CREATE_DATE], [t0].[EDI_BATCH_KEY], [t0].[EDI_KEY], [t0].[FG_CTL_NBR], [t0].[FG_DATE], [t0].[FG_RECEIVER_ID], [t0].[FG_SENDER_ID], [t0].[FG_VERSION], [t0].[IC_CTL_NBR], [t0].[IC_DATE], [t0].[IC_RECEIVER_ID], [t0].[IC_RECEIVER_ID_QUAL], [t0].[IC_SENDER_ID], [t0].[IC_SENDER_ID_QUAL], [t0].[MODIFY_BY], [t0].[MODIFY_DATE], [t0].[TS_CTL_NBR], [t0].[TS_VERSION]
FROM [EDI_BATCH] AS [t0]
WHERE ([t0].[FG_CTL_NBR] IN (:p0) AND [t0].[TS_CTL_NBR] IN (:p1))

6

Hello

This seems to be a client-side issue. Unfortunately the exception message doesn’t provide required info.
Please drop a mail to support@ . I’ll create assemblies with extended debus info and you’ll run this operation once more.

Regards

7

Will Do thanks,

Monte