Could not create foreign key constraint.

Could not create foreign key constraint.

  

Hi guys,


When trying to publish an eSpace i get this error: "Upgrade Error 

Could not create foreign key constraint. There are 'NIF' values of entity 'LOG_SITUACAO_CONTRIB' with no corresponding value in entity 'SPS_ENTIDADE'."

I can't understand why this is happening since i don't have any records in my
'LOG_SITUACAO_CONTRIB' table. How can this happen?


Thank you,

Rafael Valente

Hi Rafael,

The error seems to indicate that you do have records in that table. If you have access to the database, you could drop the table, then try to republish. If not, you could Ctrl-X/V the table in Service Studio (which will create a different physical table) and try to republish.

Kilian Hekhuis wrote:

Hi Rafael,

The error seems to indicate that you do have records in that table. If you have access to the database, you could drop the table, then try to republish. If not, you could Ctrl-X/V the table in Service Studio (which will create a different physical table) and try to republish.

Hello Killian,


I tried both methods and it didn't work. I even deleted the tables from the database but the error: "
Upgrade Error

Could not create foreign key constraint. There are 'NIF' values of entity 'LOG_SITUACAO_CONTRIB' with no corresponding value in entity 'SPS_ENTIDADE'." continue to persist.

I need more ideas, please.


Best regards,

Rafael Valente


What I think is happening is that the LOG_SITUACAO_CONTRIB table already has some values. You are trying to make one of the properties from that table a FK to another table (SPS_ENTIDADE), but this other table does not have the corresponding value for the existing records in LOG_SITUACAO_CONTRIB.

Let's say LOG_SITUACAO_CONTRIB has a property 'CountryID' with a value of 6, and you are creating the FK right now to the SPS_ENTIDADE table, it is going to try and find a record in the SPS_ENTIDADE with an ID of 6.

Since it doesn't find it, it cannot create the foreign key, because it will lose the purpose of the FK.

So what you need to do is to either edit all the values for the property that you are trying to create the FK in table LOG_SITUACAO_CONTRIB to have values that exist in the table SPS_ENTIDADE. Or if the clear that property for all the records in the LOG_SITUACAO_CONTRIB table.

Hope this helps.

Andres Moreno wrote:

What I think is happening is that the LOG_SITUACAO_CONTRIB table already has some values. You are trying to make one of the properties from that table a FK to another table (SPS_ENTIDADE), but this other table does not have the corresponding value for the existing records in LOG_SITUACAO_CONTRIB.

Let's say LOG_SITUACAO_CONTRIB has a property 'CountryID' with a value of 6, and you are creating the FK right now to the SPS_ENTIDADE table, it is going to try and find a record in the SPS_ENTIDADE with an ID of 6.

Since it doesn't find it, it cannot create the foreign key, because it will lose the purpose of the FK.

So what you need to do is to either edit all the values for the property that you are trying to create the FK in table LOG_SITUACAO_CONTRIB to have values that exist in the table SPS_ENTIDADE. Or if the clear that property for all the records in the LOG_SITUACAO_CONTRIB table.

Hope this helps.

Hello Andres,


Thank you for your reply.

My biggest difficulty is to understand why this error is thrown, i.e., bad values in table LOG_SITUACAO_CONTRIB if this one is being created and, therefore, there are no values there. Even when i query the table it returns no records. Can you explain this?


Best regrads,

Rafael Valente


It can also be that you have a mandatory FK-attribute in the entity.

So when you are inserting a record with an empty FK-attribute, you will get an exception!


J. wrote:

It can also be that you have a mandatory FK-attribute in the entity.

So when you are inserting a record with an empty FK-attribute, you will get an exception!


Hello J,


This error happens when i'm publishing the espace right after creating the new table that has that FK referencing the other table. I didn't even get to the part of inserting new records because the table isn't created because the espace isn't published with this error being thrown. I tried to publish with the FK not mandatory and i get the same error.


Best regards,

Rafael Valente


For the record and i don't know if this is relevant or not but the PK of the already existing table is Text. I did a test in which in the new table i tried to create a FK but to another table which ID is integer instead of text and i successfully published the espace. 

Solution

I have the solution now. Here it is what i did:
- In SQL Server i created a new column with the same data type as the PK that i want to link
- I ran a script that creates the FK linkin the new column and the PK from the other table (i had other FK's in the new table and i copied the script that creates the FK and changed the columns that i wanted)
- Finally, opened the SS and set the delete rule to protect


Best regards,

Rafael Valente

Solution

Hi Rafael,

I'm getting the same error as you described here. I have tried to delete the column in Service Center, published, then added back in but it still gives me the Foreign Key error as I publish with the new column. 

I looked at your solution and I am not sure how to use SQL to access the tables created by using Service Center. I thought these objects were created in OutSystems cloud. And we can't use Management Studio to manipulate these objects.

I'm confused.

Thanks


Tim Le wrote:

Hi Rafael,

I'm getting the same error as you described here. I have tried to delete the column in Service Center, published, then added back in but it still gives me the Foreign Key error as I publish with the new column. 

I looked at your solution and I am not sure how to use SQL to access the tables created by using Service Center. I thought these objects were created in OutSystems cloud. And we can't use Management Studio to manipulate these objects.

I'm confused.

Thanks



Hello Tim,


By the time i was having this trouble i wasn't working with the Outsystems cloud. I had a proper server for the application and a different server for the database. This was the reason why i could access the database through SSMS. 

When you refer to "Service Center", what you mean is "Service Studio" right?

You can manipulate your database through SQL widget and run all SQL scripts like you were in SSMS. Unfortunately, i'm not in this project anymore. Therefore, i don't have access to the script i used to unblock this problem.


Best regards,

Rafael Valente

Thank you Rafael. I just realized that Outsystems also offers on-premise installation. It looks like you have the on-premise setup so you can have direct access to the database. I am using the cloud version of Outsystems so I don't have access to the SQL server database.

I currently change the Delete property from "Protected" to "Ignore" and the error goes away.

Thanks again.

Tim Le wrote:

Thank you Rafael. I just realized that Outsystems also offers on-premise installation. It looks like you have the on-premise setup so you can have direct access to the database. I am using the cloud version of Outsystems so I don't have access to the SQL server database.

I currently change the Delete property from "Protected" to "Ignore" and the error goes away.

Thanks again.


Hello Tim,


I'm sorry i couldn't help you right away with my last reply but i'm happy to know you found a workaround for your problem.


Best regards,

Rafael Valente