Visual Upgrade (ALTER TABLE statement conflicted with the FOREIGN KEY constraint)

I have a somewhat antiquated version of Visual 7.0.0 that I am working on upgrading. My thought was to initially jump to 9.0.8 and then afterwards migrate to version 10.

My conversion finishes successfully, but the log file contains may errors like below on various tables.

2023-06-09 23:33:04:55: [DBNAME] ALTER TABLE PURC_ORDER_LINE WITH check CHECK CONSTRAINT FKEY0272
[Microsoft][ODBC SQL Server Driver][SQL Server]The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FKEY0272". The conflict occurred in database "DBNAME", table "dbo.ACCOUNT", column 'ID'.

I was trying to have a productive weekend but I am a little concerned if I continue on, that I will end up with corrupted tables that don't necessarily show up right away.

I'm not a DBA, just a seasoned IT Admin. I've managed to do a few successful upgrades in the past from the 6.X versions to 7.X but I became hesitant after our resident Visual Admin was no longer with us.

I'm curious if any of you have ran into this in the past and know if it can be safely ignored, or if I need to investigate further.

Any help would be greatly appreciated.

Attachment_4260.zip

Find more posts tagged with

VISUAL - Enterprise General Discussions

Accepted answers

All comments

unknown

Just to add a little more information:

This is occurring specifically on the intermediate conversion from 7.1.2. to 8.0.0:

2023-06-10 12:16:33:26: [DBNAME] select CASH_DISBURSE_LINE.VOUCHER_ID from CASH_DISBURSE_LINE left outer join PAYABLE on CASH_DISBURSE_LINE.VOUCHER_ID = PAYABLE.VOUCHER_ID where (PAYABLE.VOUCHER_ID is null and CASH_DISBURSE_LINE.VOUCHER_ID is not null)
2023-06-10 12:16:35:01: [DBNAME] ALTER TABLE CUST_ORDER_CURR WITH check CHECK CONSTRAINT FKEY9425
[Microsoft][ODBC SQL Server Driver][SQL Server]The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FKEY9425". The conflict occurred in database "DBNAME", table "dbo.CUSTOMER_ORDER", column 'ID'.
2023-06-10 12:16:35:04: [DBNAME] select CUST_ORDER_CURR.CUST_ORDER_ID from CUST_ORDER_CURR left outer join CUSTOMER_ORDER on CUST_ORDER_CURR.CUST_ORDER_ID = CUSTOMER_ORDER.ID where (CUSTOMER_ORDER.ID is null and CUST_ORDER_CURR.CUST_ORDER_ID is not null)
2023-06-10 12:16:35:12: [DBNAME] ALTER TABLE CUST_ORDER_LINE WITH check CHECK CONSTRAINT FKEY0037
[Microsoft][ODBC SQL Server Driver][SQL Server]The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FKEY0037". The conflict occurred in database "DBNAME", table "dbo.UNITS", column 'UNIT_OF_MEASURE'.
2023-06-10 12:16:35:14: [DBNAME] select CUST_ORDER_LINE.SELLING_UM from CUST_ORDER_LINE left outer join UNITS on CUST_ORDER_LINE.SELLING_UM = UNITS.UNIT_OF_MEASURE where (UNITS.UNIT_OF_MEASURE is null and CUST_ORDER_LINE.SELLING_UM is not null)
2023-06-10 12:16:35:34: [DBNAME] ALTER TABLE CUST_ORDER_LINE WITH check CHECK CONSTRAINT FKEY0156
[Microsoft][ODBC SQL Server Driver][SQL Server]The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FKEY0156". The conflict occurred in database "DBNAME", table "dbo.CUSTOMER_ORDER", column 'ID'.
2023-06-10 12:16:35:36: [DBNAME] select CUST_ORDER_LINE.CUST_ORDER_ID from CUST_ORDER_LINE left outer join CUSTOMER_ORDER on CUST_ORDER_LINE.CUST_ORDER_ID = CUSTOMER_ORDER.ID where (CUSTOMER_ORDER.ID is null and CUST_ORDER_LINE.CUST_ORDER_ID is not null)
2023-06-10 12:16:38:03: [DBNAME] ALTER TABLE INVENTORY_BALANCE WITH check CHECK CONSTRAINT FKEY9119
[Microsoft][ODBC SQL Server Driver][SQL Server]The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FKEY9119". The conflict occurred in database "DBNAME", table "dbo.INVENTORY_TRANS", column 'TRANSACTION_ID'.
2023-06-10 12:16:38:06: [DBNAME] select INVENTORY_BALANCE.TRANSACTION_ID from INVENTORY_BALANCE left outer join INVENTORY_TRANS on INVENTORY_BALANCE.TRANSACTION_ID = INVENTORY_TRANS.TRANSACTION_ID where (INVENTORY_TRANS.TRANSACTION_ID is null and INVENTORY_BALANCE.TRANSACTION_ID is not null)
2023-06-10 12:16:39:06: [DBNAME] ALTER TABLE OPERATION WITH check CHECK CONSTRAINT FKEY0114
[Microsoft][ODBC SQL Server Driver][SQL Server]The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FKEY0114". The conflict occurred in database "DBNAME", table "dbo.SERVICE", column 'ID'.
2023-06-10 12:16:39:09: [DBNAME] select OPERATION.SERVICE_ID from OPERATION left outer join SERVICE on OPERATION.SERVICE_ID = SERVICE.ID where (SERVICE.ID is null and OPERATION.SERVICE_ID is not null)
2023-06-10 12:16:39:78: [DBNAME] ALTER TABLE PART_LOCATION WITH check CHECK CONSTRAINT FKEY0092
[Microsoft][ODBC SQL Server Driver][SQL Server]The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FKEY0092". The conflict occurred in database "DBNAME", table "dbo.PART", column 'ID'.
2023-06-10 12:16:39:81: [DBNAME] select PART_LOCATION.PART_ID from PART_LOCATION left outer join PART on PART_LOCATION.PART_ID = PART.ID where (PART.ID is null and PART_LOCATION.PART_ID is not null)
2023-06-10 12:16:40:93: [DBNAME] ALTER TABLE PHYS_COUNT_TAG WITH check CHECK CONSTRAINT FKEY0103
[Microsoft][ODBC SQL Server Driver][SQL Server]The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FKEY0103". The conflict occurred in database "DBNAME", table "dbo.PART", column 'ID'.
2023-06-10 12:16:40:96: [DBNAME] select PHYS_COUNT_TAG.PART_ID from PHYS_COUNT_TAG left outer join PART on PHYS_COUNT_TAG.PART_ID = PART.ID where (PART.ID is null and PHYS_COUNT_TAG.PART_ID is not null)
2023-06-10 12:16:41:40: [DBNAME] ALTER TABLE PURC_ORDER_CURR WITH check CHECK CONSTRAINT FKEY9427
[Microsoft][ODBC SQL Server Driver][SQL Server]The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FKEY9427". The conflict occurred in database "DBNAME", table "dbo.PURCHASE_ORDER", column 'ID'.
2023-06-10 12:16:41:43: [DBNAME] select PURC_ORDER_CURR.PURC_ORDER_ID from PURC_ORDER_CURR left outer join PURCHASE_ORDER on PURC_ORDER_CURR.PURC_ORDER_ID = PURCHASE_ORDER.ID where (PURCHASE_ORDER.ID is null and PURC_ORDER_CURR.PURC_ORDER_ID is not null)
2023-06-10 12:16:41:51: [DBNAME] ALTER TABLE PURC_ORDER_LINE WITH check CHECK CONSTRAINT FKEY0165
[Microsoft][ODBC SQL Server Driver][SQL Server]The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FKEY0165". The conflict occurred in database "DBNAME", table "dbo.PURCHASE_ORDER", column 'ID'.
2023-06-10 12:16:41:54: [DBNAME] select PURC_ORDER_LINE.PURC_ORDER_ID from PURC_ORDER_LINE left outer join PURCHASE_ORDER on PURC_ORDER_LINE.PURC_ORDER_ID = PURCHASE_ORDER.ID where (PURCHASE_ORDER.ID is null and PURC_ORDER_LINE.PURC_ORDER_ID is not null)
2023-06-10 12:16:41:65: [DBNAME] ALTER TABLE PURC_ORDER_LINE WITH check CHECK CONSTRAINT FKEY0038
[Microsoft][ODBC SQL Server Driver][SQL Server]The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FKEY0038". The conflict occurred in database "DBNAME", table "dbo.UNITS", column 'UNIT_OF_MEASURE'.
2023-06-10 12:16:41:67: [DBNAME] select PURC_ORDER_LINE.PURCHASE_UM from PURC_ORDER_LINE left outer join UNITS on PURC_ORDER_LINE.PURCHASE_UM = UNITS.UNIT_OF_MEASURE where (UNITS.UNIT_OF_MEASURE is null and PURC_ORDER_LINE.PURCHASE_UM is not null)
2023-06-10 12:16:41:71: [DBNAME] ALTER TABLE PURC_ORDER_LINE WITH check CHECK CONSTRAINT FKEY0272
[Microsoft][ODBC SQL Server Driver][SQL Server]The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FKEY0272". The conflict occurred in database "DBNAME", table "dbo.ACCOUNT", column 'ID'.
2023-06-10 12:16:41:75: [DBNAME] select PURC_ORDER_LINE.GL_EXPENSE_ACCT_ID from PURC_ORDER_LINE left outer join ACCOUNT on PURC_ORDER_LINE.GL_EXPENSE_ACCT_ID = ACCOUNT.ID where (ACCOUNT.ID is null and PURC_ORDER_LINE.GL_EXPENSE_ACCT_ID is not null)
2023-06-10 12:16:42:45: [DBNAME] ALTER TABLE QUOTE_ORDER WITH check CHECK CONSTRAINT FKEY0158
[Microsoft][ODBC SQL Server Driver][SQL Server]The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FKEY0158". The conflict occurred in database "DBNAME", table "dbo.CUSTOMER_ORDER", column 'ID'.
2023-06-10 12:16:42:46: [DBNAME] select QUOTE_ORDER.CUST_ORDER_ID from QUOTE_ORDER left outer join CUSTOMER_ORDER on QUOTE_ORDER.CUST_ORDER_ID = CUSTOMER_ORDER.ID where (CUSTOMER_ORDER.ID is null and QUOTE_ORDER.CUST_ORDER_ID is not null)
2023-06-10 12:16:43:46: [DBNAME] ALTER TABLE RECEIVABLE_LINE WITH check CHECK CONSTRAINT FKEY0281
[Microsoft][ODBC SQL Server Driver][SQL Server]The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FKEY0281". The conflict occurred in database "DBNAME", table "dbo.CUST_ORDER_LINE".
2023-06-10 12:16:43:50: [DBNAME] select RECEIVABLE_LINE.CUST_ORDER_ID, RECEIVABLE_LINE.CUST_ORDER_LINE_NO from RECEIVABLE_LINE left outer join CUST_ORDER_LINE on RECEIVABLE_LINE.CUST_ORDER_ID = CUST_ORDER_LINE.CUST_ORDER_ID and RECEIVABLE_LINE.CUST_ORDER_LINE_NO = CUST_ORDER_LINE.LINE_NO where (CUST_ORDER_LINE.CUST_ORDER_ID is null and RECEIVABLE_LINE.CUST_ORDER_ID is not null) or (CUST_ORDER_LINE.LINE_NO is null and RECEIVABLE_LINE.CUST_ORDER_LINE_NO is not null)
2023-06-10 12:16:43:85: [DBNAME] ALTER TABLE REQUIREMENT WITH check CHECK CONSTRAINT FKEY0036
[Microsoft][ODBC SQL Server Driver][SQL Server]The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FKEY0036". The conflict occurred in database "DBNAME", table "dbo.UNITS", column 'UNIT_OF_MEASURE'.
2023-06-10 12:16:43:87: [DBNAME] select REQUIREMENT.USAGE_UM from REQUIREMENT left outer join UNITS on REQUIREMENT.USAGE_UM = UNITS.UNIT_OF_MEASURE where (UNITS.UNIT_OF_MEASURE is null and REQUIREMENT.USAGE_UM is not null)
2023-06-10 12:16:44:04: [DBNAME] ALTER TABLE REQUIREMENT WITH check CHECK CONSTRAINT FKEY0095
[Microsoft][ODBC SQL Server Driver][SQL Server]The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FKEY0095". The conflict occurred in database "DBNAME", table "dbo.PART", column 'ID'.
2023-06-10 12:16:44:06: [DBNAME] select REQUIREMENT.PART_ID from REQUIREMENT left outer join PART on REQUIREMENT.PART_ID = PART.ID where (PART.ID is null and REQUIREMENT.PART_ID is not null)
2023-06-10 12:16:44:90: [DBNAME] ALTER TABLE SHIPPER_LINE WITH check CHECK CONSTRAINT FKEY9380
[Microsoft][ODBC SQL Server Driver][SQL Server]The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FKEY9380". The conflict occurred in database "DBNAME", table "dbo.RMA_LINE".
2023-06-10 12:16:44:93: [DBNAME] select SHIPPER_LINE.RMA_ID, SHIPPER_LINE.RMA_LINE_NO from SHIPPER_LINE left outer join RMA_LINE on SHIPPER_LINE.RMA_ID = RMA_LINE.RMA_ID and SHIPPER_LINE.RMA_LINE_NO = RMA_LINE.LINE_NO where (RMA_LINE.RMA_ID is null and SHIPPER_LINE.RMA_ID is not null) or (RMA_LINE.LINE_NO is null and SHIPPER_LINE.RMA_LINE_NO is not null)
2023-06-10 12:16:44:98: [DBNAME] ALTER TABLE SHIPPER_LINE WITH check CHECK CONSTRAINT FKEY0161
[Microsoft][ODBC SQL Server Driver][SQL Server]The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FKEY0161". The conflict occurred in database "DBNAME", table "dbo.CUST_ORDER_LINE".
2023-06-10 12:16:45:01: [DBNAME] select SHIPPER_LINE.CUST_ORDER_ID, SHIPPER_LINE.CUST_ORDER_LINE_NO from SHIPPER_LINE left outer join CUST_ORDER_LINE on SHIPPER_LINE.CUST_ORDER_ID = CUST_ORDER_LINE.CUST_ORDER_ID and SHIPPER_LINE.CUST_ORDER_LINE_NO = CUST_ORDER_LINE.LINE_NO where (CUST_ORDER_LINE.CUST_ORDER_ID is null and SHIPPER_LINE.CUST_ORDER_ID is not null) or (CUST_ORDER_LINE.LINE_NO is null and SHIPPER_LINE.CUST_ORDER_LINE_NO is not null)
2023-06-10 12:16:48:98: [DBNAME] ALTER TABLE WORK_ORDER WITH check CHECK CONSTRAINT FKEY0093
[Microsoft][ODBC SQL Server Driver][SQL Server]The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FKEY0093". The conflict occurred in database "DBNAME", table "dbo.PART", column 'ID'.
2023-06-10 12:16:49:14: [DBNAME] select WORK_ORDER.PART_ID from WORK_ORDER left outer join PART on WORK_ORDER.PART_ID = PART.ID where (PART.ID is null and WORK_ORDER.PART_ID is not null)

2023-06-10 12:16:49:96: [DBNAME] Turn on not trusted constraints succeeded. 19 constraint(s) had data errors. Consult VMCVTUTL.LOG for details.

It appears the select statements are useful in picking out specific rows with an issue. I understand the relationship of foreign keys and their purpose but I'm unsure how I could have ended up with so many of these, unless since this is a 25 year old database/implementation that earlier versions didn't use these foreign keys for data consistency.

I'm not sure whether I can continue and worry about resolving these at a later time, or if I should stop and seek out advise from support and abandon the upgrade process for this weekend and reschedule.

unknown

Yes I ran into this with that portion of an upgrade.

Yes you can run the queries to identify 'bad data'

Yes the rules changed where what was acceptable in old database versions is now a violation.

If you are going to tackle this yourself, if it were me I would not continue this process in live until you can do it successfully and repeatedly in test environment after resolving bad data.

We abandoned some of the intermediate upgrades 6 to 7 7 to 8 etc. because of this and other problems with certain executables- mainly DBR scheduler.

When we finally upgraded 6.5.3 to 10.x, I recruited the help of SYNERGY because I knew all the quirks to date and they handled all of that stuff and I was able to focus on the specifics of our unique implementation and environment.

May you succeed in what ever direction and action you decide to take!

systemsadmin

Part of the reason for these is because each major version of Visual of the past tightened up foreign key constraints where referential integrity wasn't super-enforced, and records that should reference other tables could become orphaned because they didn't get deleted / updated properly when the 'parent' or referenced record was deleted / updated. In our case, we ran into these between 6.5.3->7.0 and 7.1.2->8.0 with note fields, ECN comments, etc. Since in our case the records were only information references, I took note of what IDs / references were an issue, dumped the reference tables into holding tables so the upgrade could perform, then wrote a query identifying the problem records so I could update / prune them out, then load the reference table back into my main DB.

Especially if your db started out older than 6.5.3, there may be a lot more inconsistencies than what we had, so you can either spend time cleaning up the bad records (which is fine if you have the time), or have Synergy's folks work on it for you. The issue in these error messages popping up during the upgrade process is you don't know which record(s) are the issue at that time, and you have little choice but have the upgrade fail and roll back (which is one of the reasons to try major upgrades on a test db cope of your live db first).

The nice thing though is you can use these error statements in queries to identify those bad records and clean them up prior to your next upgrade attempt, though depending on the complexity of the table relationship(s), you may be more comfortable having consultants work on that; on the other hand, if you can navigate SSMS / run basic SQL queries, these CAN be educational and help you understand Visual's data dictionary a bit better.

unknown

Thank you for the response. Over the past few days I’ve been coming to the same conclusion. I know that when I was first exposed to Visual we were on some really old version 5.1.3 perhaps, and I remember learning from our Visual guru how to upgrade to 6.5.3. We had a migration from SQLbase to SQL server with 7.0 and it looks like if I go back to an old database copy from that migration, all but one of these problems existed then and just had lived on.

I ran a couple of test runs of version 9, since the end of 2021 and had everyone test out their daily functions and end of month routines without any issues.

For whatever reason these errors didn’t catch my attention during that phase, as I didn’t look through the convert logs as closely as I should have.

Infor support has seemingly diminished in the resources they provide for SQL issues compared to some of my archived incidents. They offer nothing other than a recommendation to contact a professional DBA. They tell me that it should be safe to upgrade however without being able to turn on the trust relationship at the end of the process that “shouldn’t affect normal operation and only custom tables” but we don’t really have any custom tables.

I am certainly willing to work on correcting the errors myself, but I’m not sure that I have enough understanding of the relationship between all of the tables and foreign keys to where I’m not going to eliminate records that I shouldn’t.

I may inquire with the group you mentioned just to get a feel for the expense. Management isn’t likely to pay vast sums for database manipulation, but I’m not sure how large of a job this would be for someone experienced.

Copyright © 2025 Infor. All rights reserved.