[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1197
  • Last Modified:

Violation on FOREIGN KEY constraint

I have almost reached the point of “Scream!” Running this code will return the error:

“Project Corp.exe raised exception class EIBInterbaseError with message ‘violation of FOREIGN KEY constraint “AVAIL_FUNDS”.

On the backend, EMPLOYEE Table is linked to ASSETS by employee id; cascade update and cascade delete.
All other tables -- including AVAIL_FUNDS -- where the employee ids are linked, are cascade update and set null on delete.
Calling “DELETE” only, on the employee table from either the backend or the front end operates properly. All linked records in ASSETS are deleted and the other tables set the associated records value to NULL.
Running the code will insert the values into the FUNDS and BUYO datasets, and will post/commit the values in the database, then return the error on DELETE of the employee.
One thing I’ve noticed, after commit the Employee dataset (EmpDS) is not closing as expected. For some reason it remain in the “Active” state.
Any assistance would be greatly appreciated.

Front end: C++ Builder 6 Pro (build 10.166)
Backend: Interbase 7.5 ; Dialect 3
Win XP ; Service Pack 2

void __fastcall TEmployeeFrm::btnBuyoutClick(TObject *Sender)
{
  int shBuyOut = DataMod->EmpDS->FieldByName("EMPLOYEEID")->AsInteger;
  AnsiString currName =  DataMod->EmpDS->FieldByName("FULL_NAME")->AsString;

  Variant cpStanding = DataMod->StandDS->Lookup("EMPLOYEE",shBuyOut,"SUM_OF_ADDEDASSET1");

  DataMod->FundsDS->Last();

  double currAvail = DataMod->FundsDS->FieldByName("CURR_FUNDS")->AsCurrency,
         shStanding = cpStanding,
         payAmount = 0,
         curr_money_change = 0;

  payAmount = shStanding * currAvail;
  curr_money_change = currAvail - payAmount;

  DataMod->FundsDS->Insert();
  DataMod->FundsDS->FieldByName("CURR_FUNDS")->AsCurrency = curr_money_change;
  DataMod->FundsDS->FieldByName("EMP_ID")->AsInteger = shBuyOut;
  DataMod->FundsDS->FieldByName("CHANGE")->AsCurrency = payAmount;
  DataMod->FundsDS->FieldByName("AVAIL_BY")->AsString = currName;

  DataMod->FundsDS->Post();

  if(DataMod->FundsTA->Active == false)
    DataMod->FundsTA->StartTransaction();

  Screen->Cursor = crSQLWait;

  DataMod->EmpDS->DisableControls();
  try
  {
    DataMod->FundsTA->Commit();
  }
  __finally
  {
    for(int z=0; z < DataMod->Corporate->DataSetCount; z++)
      DataMod->Corporate->DataSets[z]->Active = true;
  }

  DataMod->BuyODS->Insert();
  DataMod->BuyODS->FieldByName("EMPLOY_ID")->AsInteger = shBuyOut;
  DataMod->BuyODS->FieldByName("BO_NAME")->AsString = currName;
  DataMod->BuyODS->FieldByName("BO_STANDING")->AsCurrency = shStanding;
  DataMod->BuyODS->FieldByName("BO_CURR_FUNDS")->AsCurrency = currAvail;
  DataMod->BuyODS->FieldByName("PAYOUT")->AsCurrency = payAmount;


  DataMod->BuyODS->Post();

  if(DataMod->BuyOTA->Active == false)
    DataMod->BuyOTA->StartTransaction();

  try
  {
    DataMod->BuyOTA->Commit();
  }
  __finally
  {
    for(int y=0; y < DataMod->Corporate->DataSetCount; y++)
      DataMod->Corporate->DataSets[y]->Active = true;
    DataMod->EmpDS->EnableControls();
  }

  TLocateOptions SearchEmp;
  SearchEmp.Clear();
  bool FoundEmp = DataMod->EmpDS->Locate("EMPLOYEEID", shBuyOut, SearchEmp);
  if(FoundEmp == true)
    DataMod->EmpDS->Delete();

  if(DataMod->EmpTA->Active == false)
    DataMod->EmpTA->StartTransaction();

  try
  {
    DataMod->EmpTA->Commit();
  }
  __finally
  {
    for(int x=0; x < DataMod->Corporate->DataSetCount; x++)
      DataMod->Corporate->DataSets[x]->Active = true;
    DataMod->EmpDS->EnableControls();
  }
  Screen->Cursor = crDefault;
}
0
soothin
Asked:
soothin
  • 3
2 Solutions
 
Gary BenadeCommented:
please post the table metadata for AVAIL_FUNDS, including indexes and other constraints. I suspect something you have done on that table, probably a unique or primary constraint on avail_funds column, is not allowing two or more rows to have a value of NULL. You can check this by setting two rows to NULL in your table editor.
0
 
soothinAuthor Commented:
I've posted the "AVAIL_FUNDS" and all tables that contain a reference from the "EMPLOYEE" table; however, I'm having a little trouble seeing the problem on the backend, since calling 'DELETE' on the backend or the front end will not generate an error. Seems to happen when 'DELETE' is called in the code.

/* Table: ASSETS, Owner: SYSDBA */

CREATE TABLE "ASSETS"
(
  "ASSETID"      INTEGER NOT NULL,
  "EMPLOYEE"      INTEGER,
  "ADDEDASSET"      "ASSETLINE",
  "ASSETDATE"      TIMESTAMP,
CONSTRAINT "PK_ASSETS" PRIMARY KEY ("ASSETID")
);

/* Table: ASSETS_SEC, Owner: SYSDBA */

CREATE TABLE "ASSETS_SEC"
(
  "ASSET_SECID"      INTEGER NOT NULL,
  "ADDED_SEC"      "ASSETLINE",
  "ADD_SEC_DATE"      TIMESTAMP,
  "ADDED_SEC_SH"      INTEGER,
  "ADDED_BY"      "FULLNAME_VALUE",
CONSTRAINT "PK_ASSETS_SEC" PRIMARY KEY ("ASSET_SECID")
);

/* Table: AVAIL_FUNDS, Owner: SYSDBA */

CREATE TABLE "AVAIL_FUNDS"
(
  "AVAIL_ID"      INTEGER NOT NULL,
  "EMP_ID"      INTEGER,
  "CURR_FUNDS"      "ASSETLINE",
  "AVAIL_DATE"      TIMESTAMP,
  "CHANGE"      "ASSETLINE",
  "AVAIL_BY"      "FULLNAME_VALUE",
 PRIMARY KEY ("AVAIL_ID")
);

/* Table: BUYOUT, Owner: SYSDBA */

CREATE TABLE "BUYOUT"
(
  "BUYOUT_ID"      INTEGER NOT NULL,
  "EMPLOY_ID"      INTEGER,
  "BO_NAME"      "FULLNAME_VALUE",
  "BO_STANDING"      "ASSETLINE",
  "BO_CURR_FUNDS"      "ASSETLINE",
  "PAYOUT"      "ASSETLINE",
  "BUYOUT_DATE"      TIMESTAMP,
CONSTRAINT "PK_BUYOUT" PRIMARY KEY ("BUYOUT_ID")
);

/* Table: DIST_WINS, Owner: SYSDBA */

CREATE TABLE "DIST_WINS"
(
  "WIN_KEY"      INTEGER NOT NULL,
  "WIN_ID"      INTEGER,
  "EMPNO"      INTEGER,
  "SH_STANDINGS"      "ASSETLINE",
  "DISTRIBUTED_SHARE"      "ASSETLINE",
  "DISTRIBUTED_DATE"      TIMESTAMP,
  "WIN_NAME"      "FULLNAME_VALUE",
CONSTRAINT "PK_DIST_WINS" PRIMARY KEY ("WIN_KEY")
);

/* Table: EMPLOYEES, Owner: SYSDBA */

CREATE TABLE "EMPLOYEES"
(
  "EMPLOYEEID"      INTEGER NOT NULL,
  "LASTNAME"      VARCHAR(20) NOT NULL,
  "FIRSTNAME"      VARCHAR(15),
  "USERID"      "USERNAME" NOT NULL,
  "DATEHIRE"      DATE,
  "ADDRESS1"      "ADDRESSLINE",
  "ADDRESS2"      VARCHAR(10),
  "CITY"      VARCHAR(15),
  "STATE"      CHAR(2),
  "ZIPCODE"      VARCHAR(11),
  "FULL_NAME"       COMPUTED BY (LASTNAME||', '||FIRSTNAME),
  "EMAIL"      VARCHAR(20),
  "PHONE"      VARCHAR(13),
CONSTRAINT "PK_EMPLOYEES" PRIMARY KEY ("EMPLOYEEID"),
CONSTRAINT "UNQ1_EMPLOYEES" UNIQUE ("USERID")
);
ALTER TABLE "ASSETS" ADD FOREIGN KEY ("EMPLOYEE") REFERENCES "EMPLOYEES" ("EMPLOYEEID") ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE "ASSETS_SEC" ADD CONSTRAINT "FK_ASSETS_SEC_1" FOREIGN KEY ("ADDED_SEC_SH") REFERENCES "EMPLOYEES" ("EMPLOYEEID") ON UPDATE CASCADE ON DELETE SET NULL;
ALTER TABLE "AVAIL_FUNDS" ADD CONSTRAINT "FK_AVAIL_FUNDS_1" FOREIGN KEY ("EMP_ID") REFERENCES "EMPLOYEES" ("EMPLOYEEID") ON UPDATE CASCADE ON DELETE SET NULL;
ALTER TABLE "DIST_WINS" ADD CONSTRAINT "FK_DIST_WINS_2" FOREIGN KEY ("EMPNO") REFERENCES "EMPLOYEES" ("EMPLOYEEID") ON UPDATE CASCADE ON DELETE SET NULL;
0
 
soothinAuthor Commented:
Oh, also here are the indexes

Name                  Unique      Table            Foreign Key
--------------------------------------------------------------------
RDB$FOREIGN37      No      ASSETS            RDB$PRIMARY1
RDB$PRIMARY3      Yes      ASSETS
RDB$FOREIGN49      No      ASSETS_SEC      RDB$PRIMARY1
RDB$PRIMARY12      Yes      ASSETS_SEC
RDB$FOREIGN48      No      AVAIL_FUNDS      RDB$PRIMARY1
RDB$PRIMARY52      Yes      AVAIL_FUNDS
RDB$PRIMARY35      Yes      BUYOUT
RDB$FOREIGN31      No      DIST_WINS      RDB$PRIMARY7
RDB$FOREIGN46      No      DIST_WINS      RDB$PRIMARY1
RDB$PRIMARY30      Yes      DIST_WINS
RDB$10                  Yes      EMPLOYEES
RDB$PRIMARY1      Yes      EMPLOYEES
0
 
balderCommented:
don't look like you have posted the metadata from the same db as you are running against

&#8220;Project Corp.exe raised exception class EIBInterbaseError with message &#8216;violation of FOREIGN KEY constraint &#8220;AVAIL_FUNDS&#8221;.

ALTER TABLE "AVAIL_FUNDS" ADD CONSTRAINT "FK_AVAIL_FUNDS_1" FOREIGN KEY ("EMP_ID") REFERENCES "EMPLOYEES" ("EMPLOYEEID") ON UPDATE CASCADE ON DELETE SET NULL;


can't find a constraint with this name
0
 
soothinAuthor Commented:
After I post the metadata, I notice the same thing. Although the index was there, I made sure that the constraint appeared in the metadata, but still got the same error.
However, I’m hot on the track of getting this thing licked. Each dataset had its own transaction in the application. So I deleted all transactions with the exception of the default transaction, and the whole program works like a charm. I think this would be fine if only one person would be using the application, but this is not how we use our client / server applications.
Two bullets in the Developer’s Guide I’m currently looking at to resolve further.
- Concurrent transactions do not see each other’s partial or uncommitted results, which might create inconsistencies in the application’s state. This is referred to as “isolation”.
- Committed updates to records survive failures, including communication failures, process failures, and server system failure. This is referred to as durability.
The problem lies in the transactions, or how I'm using transactions.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now