SQL error...FOREIGN KEY constraint failed. What's wrong with my code?

[pokiehl]

INSERT INTO Improvement_ValidTerrains (ImprovementType, TerrainType, PrereqTech, PrereqCivic) VALUES ('IMPROVEMENT_FARM', 'TERRAIN_TUNDRA', 'NULL', 'NULL') ;

Trying to make it so that farms can be built on tundra...not sure why I'm getting this error.

EDIT: Seems like you're not supposed to include tables that you have no value for. So this is not getting any errors:

INSERT INTO Improvement_ValidTerrains (ImprovementType, TerrainType) VALUES ('IMPROVEMENT_FARM', 'TERRAIN_TUNDRA') ;

 

[HandyVac]

You don't want quotations marks around NULL.

NULL without quotes is the value null, but 'NULL' is a text string containing the word "NULL".

Edit: The Foreign Key error is because it's checking in the Techs and Civics tables for a tech/civic named "NULL" and not finding one.

 

[ITcore]

You don't want quotations marks around NULL.

NULL without quotes is the value null, but 'NULL' is a text string containing the word "NULL".

Edit: The Foreign Key error is because it's checking in the Techs and Civics tables for a tech/civic named "NULL" and not finding one.

I apologize to pokiehl for hijacking this temporarily, but could I get a spare set of eyes for a unique key constraint error? The file is attached. My Database log only says:

[3010451.677] [Gameplay] ERROR: UNIQUE constraint failed: Modifiers.ModifierId
[3010451.677] [Gameplay] ERROR: UNIQUE constraint failed: Modifiers.ModifierId

And my Modding log tells me which file isn't loading properly. That's all I know. I have a ton of Modifiers so another set of eyes would be lovely. The Modifiers that begin with "QUO_" are made by isau, those all work. The "IT_" ones are mine, and is most likely theissue.

 

[pokiehl]

Ahhh, thank you HandyVac! That makes sense. And ITcore, I have no clue what the issue is, but I hope someone can help.

 

[jeremy savarese]

I deal with databases professionally. I unzipped the files and have it opened it Notepad++, but do not see the insert statement you're referring to. I also don't see any SQL manipulating the Improvement_ValidTerrains table.

 

[ITcore]

I deal with databases professionally. I unzipped the files and have it opened it Notepad++, but do not see the insert statement you're referring to. I also don't see any SQL manipulating the Improvement_ValidTerrains table.

The file that was uploaded was mine. I'm getting a Unique constraint failure and it points to Modifiers.ModifierId and that's it.

 

[jeremy savarese]

Are you working with a list of valid ModifierIds?

This tells you that an insert or update is failing because it is trying to insert a value into a ModifierId column that doesn't exist in your Modifiers table. Being able to see the contents of the Modifiers table would help tremendously.

 

[jeremy savarese]

I see a few things that may or may not be problematic, if you could perform a select * on the Modifiers table I can probably figure this out.

 

[ITcore]

Vast majority of the ModifierIds I'm working with are custom. How do I do that? That's a bit advanced for my level of knowledge.

 

[jeremy savarese]

Sorry, this is really tricky without being able to see what these tables look like, I can only make educated guesses as to how these tables are designed.

The command would be:
SELECT * FROM Modifiers;

This would produce a dump of the Modifiers table.


If you can't do this I would suggest strongly separating the file and loading one civilization at a time. This will help narrow our search.

 

[jeremy savarese]

I also want to point out lines 450-465 and 560-565 I see you inserting values into the TraitModifiers table before inserting into the Modifiers table. This could be your culprit.

 

[HandyVac]

You'll find a copy of the database from the last time you ran the game under Documents\My Games\Sid Meier's Civilization VI\Cache
DebugGameplay.sqlite is the file you're looking for.

The database tables are defined in the file 01_GameplaySchema.sql under Steam\steamapps\common\Sid Meier's Civilization VI\Base\Assets\Gameplay\Data\Schema

 

[ITcore]

I also want to point out lines 450-465 and 560-565 I see you inserting values into the TraitModifiers table before inserting into the Modifiers table. This could be your culprit.

Here's the dump of the Modifiers.

 

[jeremy savarese]

Okay perfect between the schema and dump, this should be all that I need give me a few minutes.

 

[jeremy savarese]

Okay I need to go to sleep, but here's what I've gathered:

Your inserts failed somewhere around line 153 :

INSERT INTO Modifiers
( ModifierId, ModifierType, RunOnce, Permanent, OwnerRequirementSetId, SubjectRequirementSetId ) VALUES
( 'TRAIT_INTERNATIONAL_TRADE_GAIN_FOOD', 'MODIFIER_PLAYER_ADJUST_TRADE_ROUTE_YIELD_FOR_INTERNATIONAL', 0, 0, NULL, NULL ),
( 'TRAIT_INTERNATIONAL_TRADE_GAIN_GOLD', 'MODIFIER_PLAYER_ADJUST_TRADE_ROUTE_YIELD_FOR_INTERNATIONAL', 0, 0, NULL, NULL ),
( 'TRAIT_INCOMING_TRADE_OFFER_FOOD', 'MODIFIER_PLAYER_CITIES_ADJUST_TRADE_ROUTE_YIELD_TO_OTHERS', 0, 0, NULL, NULL ),
( 'TRAIT_INCOMING_TRADE_OFFER_GOLD', 'MODIFIER_PLAYER_CITIES_ADJUST_TRADE_ROUTE_YIELD_TO_OTHERS', 0, 0, NULL, NULL );

Those values were not inserted.

I was misread and assumed you were the first guy who posted earlier, this is not a violation of referential integrity, it is a violation of the UNIQUE constraint on the primary key.

This means that you're trying to insert a value for the Modifiers table specifically ModifierId that already exists in the table. I hope this helps, I will be out of town this weekend and come back to this Monday. If you're still stuck just holler.

 

[ITcore]

Thank you so much for your help! I've successfully parsed through the entire file and fixed all outstanding issues. There were a few more instances of that issue popping up and I appreciate the in-depth help you provided. Also, thanks to HandyVac for helping me figure out how to pull the database tables.

The fix was simple, I just created custom names for all the new modifiers and requirements.