Community
Showing results for 
Search instead for 
Do you mean 
Reply

Error: The INSERT statement conflicted with the FOREIGN KEY constraint PHONE_PHONEMASKID_FK

New Member
Posts: 40
Country: United_Kingdom

Error: The INSERT statement conflicted with the FOREIGN KEY constraint PHONE_PHONEMASKID_FK

Following changing some fields we have this error when saving a new contact:

 

Error The INSERT statement conflicted with the FOREIGN KEY constraint PHONE_PHONEMASKID_FK. The conflict occurred in database databasename, table dbo.TBL_PHONEMASK, column PHONEMASKID. The statement has been terminated.

 

Tried the fix suggested in Answer ID 37962: http://kb.act.com/app/answers/detail/a_id/37962/~/error%3Athe-insert-statement-conflicted-with-the-f...

 

But define field for default country is greyed out. Issue still exists and seems that the default phone county has changed to US 1.

 

This error happened after changing some contact fields from character to initial-caps (address, city, county). Tried numerous fixes as administrator reverted to characters database maintenance on My Record deleted reinstated United Kingdom restarted etc. But nothing seems to fix this error. Any help to correct error will be appreciated!

 

ACT Customer Success Team suggested these:

 

Suggestion 1

I have re-created the issue exactly as what you have in Premium V19.1

Regarding not being able to put a default value in the country field.
This was logged as a defect with v16.2 and v17.0.
it was resolved and closed. However It has been re-opened and current we are working on a fix for this. However I do not have a date for the completion of this.

The only real suggestion to undo the issue is to restore a backup at the moment

Other possible suggestions (As I am not aware of how much data has been effected by this issue)
creating a new field.
Then to move the data from the country field into the new dummy field.
From that point put the Country field back to its original settings (Character).
At this point you would have 2 options.
1 to use the original drop down list and update through time
2 to create a new drop down list with the country Initials and move the data back from the dummy field to the original field.

 

It was too late to do restore when the email was received as had done too much so last week I tried the 2nd option. It seemed to work initially but following restart, phone filed has reverted to US!

 

Suggestion 2

Unfortunately the defect you are referring to is still outstanding and I am unable to provide any updates on when this will be resolved.

 

Another workaround that may be worth trying is to create an empty copy of the database, change the field types back to character and then try importing your data. I wouldn’t be able to guarantee this will work though as the fields will be copied from the affected database and may have the same issue.

 

The better option would be to create a brand new blank database and import your data in to it. This however would depend on the amount of customisation that you have in the database.

 

Tried the suggested fixes and am up to date with hot fixes but error persists.

 

Any solutions or news on fixing the greyed out default country field?

 

Moderator
Posts: 451
Country: United_Kingdom

Re: Error: The INSERT statement conflicted with the FOREIGN KEY constraint PHONE_PHONEMASKID_FK

Hi TZAL,

 

Firstly i would like to ensure you take a backup before preforming the steps in case of unexpected issues or data loss. I have include an article regarding backing up if it is needed. 

 

19211 How to back up and restore an Act! database

 

When seeing this type of error message in the past a Reschema of the database usually corrects the Table or field causing the issue.

I having included the article below on how to do this, and i hope it will resolve the issue.

 

 

38560 How to manually run the database schema update script in Act!

 

New Member
Posts: 40
Country: United_Kingdom

Re: Error: The INSERT statement conflicted with the FOREIGN KEY constraint PHONE_PHONEMASKID_FK

Thanks. I looked at the location advice but it didn't seem to match my Windows 10 PC. Searched for "ActUpdate.config" and found 5 locations:

 

C:\act!_pro_v19_sp2x146\ACTSTD\program files\ACT\ActInstallDir
C:\Program Files (x86)\ACT\Act for Windows
C:\act!_pro_v19_sp1x10\ACTSTD\program files\ACT\ActInstallDir
C:\act!_pro_v19_sp0x147\ACTSTD\program files\ACT\ActInstallDir
C:\act!_pro_v18_sp2x53\ACTSTD\program files\ACT\ActInstallDir

 

Some were created by ACT tech support a while ago & service pack/updates I think.

 

Which one(s) apply?

Moderator
Posts: 451
Country: United_Kingdom

Re: Error: The INSERT statement conflicted with the FOREIGN KEY constraint PHONE_PHONEMASKID_FK

The important one will be found in: C:\Program Files (x86)\ACT\Act for Windows the others are the install files for Act!

New Member
Posts: 40
Country: United_Kingdom

Re: Error: The INSERT statement conflicted with the FOREIGN KEY constraint PHONE_PHONEMASKID_FK

Thanks tried that (twice to be sure) but it didn't help 100%.

 

The default Country is no longer greyed out so set default to UK But default phone & mobile is still US (1). 

 

Tried making a new contact with a UK phone number (without going into the phone option box and changing to UK) and save by got the error:

 

ACT Error.png

 

 

Want default phone to match my my country (as set in My Record and now set as default in Country Field) i.e. want UK phone  not US.

 

Hope you can help!

 

New Member
Posts: 40
Country: United_Kingdom

Re: Error: The INSERT statement conflicted with the FOREIGN KEY constraint PHONE_PHONEMASKID_FK

Just ran that reschema exercise again after amending default country field just in case... but sadly that didn't work either.

 

Moderator
Posts: 451
Country: United_Kingdom

Re: Error: The INSERT statement conflicted with the FOREIGN KEY constraint PHONE_PHONEMASKID_FK

I would like to confirm it is the structure of the database causing the problem. I would recommend creating an empty copy of the database then test. please see the article below for how. if this works you can import the data from the database that has the issue into the empty copy and test again to see if it returns.

 

19247 How to Create an Empty/Blank Copy of Your ACT! Database

 

If the issue is still happening after i would recommend giving us a call to investigate this further on 0845 268 0220

New Member
Posts: 40
Country: United_Kingdom

Re: Error: The INSERT statement conflicted with the FOREIGN KEY constraint PHONE_PHONEMASKID_FK

That was suggested be ACT tech support and I did try that but it made no difference.

 

I tried again since doing the Reschema and got this error:

 

ACT Error.png

 

But the database is on the PC. Not in Documents though, using another folder on C://.

 

I did try opening the pad file directly from the database folder on the PC. Database opens fine but the error still shows.

 

 

Moderator
Posts: 451
Country: United_Kingdom

Re: Error: The INSERT statement conflicted with the FOREIGN KEY constraint PHONE_PHONEMASKID_FK

I would like to make sure the data is showing correctly from the Actdiag tool, you can use the run command (Ctrl + R) then type 'actdiag'

once loaded can you navigate 'Databases > Database details list' i would like to ensure the database is showing from this list.

 

If it is not listed it would be on another PC and you can confirm the name from Act in the 'Help > About Act > Database information menu.

New Member
Posts: 40
Country: United_Kingdom

Re: Error: The INSERT statement conflicted with the FOREIGN KEY constraint PHONE_PHONEMASKID_FK

The database is showing on actdiag:

 

Status: online

Version: 19.0.1.0

Syncing: enabled

File path: correct location on my PC

 

Act in the 'Help > About Act > Database information menu shows correct database & server name (my PC).

 

So it looks correct for a local database doesn't it?

 

NB this error didn't happen last time I attempted creating an empty copy of the database to test (pre-update 7 reschema).

 

Hope that helps.