Foreign Key Frustration

racha

Prince
Joined
Jul 27, 2013
Messages
335
Location
Over there --->
Anyone able to tell me why this isn't working or, more importantly, how to make it work?

Code:
INSERT OR REPLACE INTO Governors
               (GovernorType,               Image,                           Name,                                                   Title,                                               ShortTitle,                                       Description,                                               IdentityPressure,   TransitionStrength,   PortraitImage,                           PortraitImageSelected)
VALUES   ('GOVERNOR_ALCHEMY',   'GOVERNOR_ALCHEMY',   'LOC_GOVERNOR_ALCHEMY_NAME',   'LOC_GOVERNOR_ALCHEMY_TITLE',   'LOC_GOVERNOR_ALCHEMY_TITLE',   'LOC_GOVERNOR_ALCHEMY_DESCRIPTION',   -5,                               100,                               'GovernorNormal_Cardinal',   'GovernorSelected_Cardinal');

Sqlitebrowser says: Query executed successfully

Code:
INSERT OR REPLACE INTO Governors
               (GovernorType,                   Image,                                   Name,                                           Title,                                           ShortTitle,                                   Description,                           IdentityPressure,   TransitionStrength,   PortraitImage,                           PortraitImageSelected)
VALUES   ('GOVERNOR_AQUAFARMER',   'GOVERNOR_CITY_DEFENDER',   'LOC_GOVERNOR_AQUA_NAME',   'LOC_GOVERNOR_AQUA_TITLE',   'LOC_GOVERNOR_AQUA_TITLE',   'LOC_GOVERNOR_AQUA_DESC',   -5,                               100,                               'GovernorNormal_Defender',   'GovernorSelected_Defender');

Sqlitebrowser says: FOREIGN KEY constraint failed:

If I paste the working code for the alchemy governor into Sqlitebrowser and change even one character of its name I also get the foreign key error.

I've tried deleting everything from /cache, relaunching the game to recreate the database files and this still happens.
 
Did you add them to the Types table first?

I did, yes.

Edit: Here's the complete file that the game is having the issue with:

Code:
INSERT OR REPLACE INTO Types
               (Type,                                                           Kind)
VALUES   ('GOVERNOR_AQUAFARMER',                           'KIND_GOVERNOR'),
               ('GOVERNOR_AQUA_PROMOTION_BASE',       'KIND_GOVERNOR_PROMOTION'),
               ('GOVERNOR_AQUA_PROMOTION_FISH',       'KIND_GOVERNOR_PROMOTION'),
               ('GOVERNOR_AQUA_PROMOTION_CRABS',       'KIND_GOVERNOR_PROMOTION'),
               ('GOVERNOR_AQUA_PROMOTION_WHALES',   'KIND_GOVERNOR_PROMOTION'),
               ('GOVERNOR_AQUA_PROMOTION_TURTLES',   'KIND_GOVERNOR_PROMOTION'),
               ('GOVERNOR_AQUA_PROMOTION_REEFS',       'KIND_GOVERNOR_PROMOTION');

INSERT OR REPLACE INTO Governors
               (GovernorType,                   Image,                                   Name,                                           Title,                                           ShortTitle,                                   Description,                           IdentityPressure,   TransitionStrength,   PortraitImage,                           PortraitImageSelected)
VALUES   ('GOVERNOR_AQUAFARMER',   'GOVERNOR_CITY_DEFENDER',   'LOC_GOVERNOR_AQUA_NAME',   'LOC_GOVERNOR_AQUA_TITLE',   'LOC_GOVERNOR_AQUA_TITLE',   'LOC_GOVERNOR_AQUA_DESC',   -5,                               100,                               'GovernorNormal_Defender',   'GovernorSelected_Defender');

INSERT OR REPLACE INTO GovernorPromotions
               (GovernorPromotionType,                           Name,                                                               Description,                                               Level,Column,   BaseAbility)
VALUES   ('GOVERNOR_AQUA_PROMOTION_BASE',       'LOC_GOV_AQUA_PROMO_BASE_NAME',           'LOC_GOV_AQUA_PROMO_BASE_DESC',           0,       1,           1),
               ('GOVERNOR_AQUA_PROMOTION_FISH',       'LOC_GOV_AQUA_PROMO_FISH_NAME',           'LOC_GOV_AQUA_PROMO_FISH_DESC',           1,       0,           0),
               ('GOVERNOR_AQUA_PROMOTION_CRABS',       'LOC_GOV_AQUA_PROMO_CRABS_NAME',       'LOC_GOV_AQUA_PROMO_CRABS_DESC',       1,       2,           0),
               ('GOVERNOR_AQUA_PROMOTION_WHALES',   'LOC_GOV_AQUA_PROMO_WHALES_NAME',       'LOC_GOV_AQUA_PROMO_WHALES_DESC',       2,       0,           0),
               ('GOVERNOR_AQUA_PROMOTION_TURTLES',   'LOC_GOV_AQUA_PROMO_TURTLES_NAME',   'LOC_GOV_AQUA_PROMO_TURTLES_DESC',   2,       2,           0),
               ('GOVERNOR_AQUA_PROMOTION_REEFS',       'LOC_GOV_AQUA_PROMO_REEFS_NAME',       'LOC_GOV_AQUA_PROMO_REEFS_DESC',       3,       1,           0);

INSERT OR REPLACE INTO GovernorPromotionSets
               (GovernorType,                   GovernorPromotion)
VALUES   ('GOVERNOR_AQUAFARMER',   'GOVERNOR_AQUA_PROMOTION_BASE'),
               ('GOVERNOR_AQUAFARMER',   'GOVERNOR_AQUA_PROMOTION_FISH'),
               ('GOVERNOR_AQUAFARMER',   'GOVERNOR_AQUA_PROMOTION_CRABS'),
               ('GOVERNOR_AQUAFARMER',   'GOVERNOR_AQUA_PROMOTION_WHALES'),
               ('GOVERNOR_AQUAFARMER',   'GOVERNOR_AQUA_PROMOTION_TURTLES'),
               ('GOVERNOR_AQUAFARMER',   'GOVERNOR_AQUA_PROMOTION_REEFS');

INSERT OR REPLACE INTO GovernorPromotionPrereqs
               (GovernorPromotionType,                           PrereqGovernorPromotion)
VALUES   ('GOVERNOR_AQUA_PROMOTION_FISH',       'GOVERNOR_AQUA_PROMOTION_BASE'),
               ('GOVERNOR_AQUA_PROMOTION_CRABS',       'GOVERNOR_AQUA_PROMOTION_BASE'),
               ('GOVERNOR_AQUA_PROMOTION_WHALES',   'GOVERNOR_AQUA_PROMOTION_BASE'),
               ('GOVERNOR_AQUA_PROMOTION_TURTLES',   'GOVERNOR_AQUA_PROMOTION_BASE'),
               ('GOVERNOR_AQUA_PROMOTION_REEFS',       'GOVERNOR_AQUA_PROMOTION_BASE');

INSERT OR REPLACE INTO GovernorPromotionModifiers
               (GovernorPromotionType,                           ModifierId)
VALUES   ('GOVERNOR_AQUA_PROMOTION_BASE',       'AQUA_PROMO_BASE'),
               ('GOVERNOR_AQUA_PROMOTION_BASE',       'AQUA_PROMO_BASETWO'),

               ('GOVERNOR_AQUA_PROMOTION_FISH',       'AQUA_PROMO_FISHIDENTITY'),
               ('GOVERNOR_AQUA_PROMOTION_FISH',       'AQUA_PROMO_FISHBONUS'),
               ('GOVERNOR_AQUA_PROMOTION_FISH',       'AQUA_PROMO_FISHBONUSTWO'),

               ('GOVERNOR_AQUA_PROMOTION_CRABS',       'AQUA_PROMO_CRABSIDENTITY'),
               ('GOVERNOR_AQUA_PROMOTION_CRABS',       'AQUA_PROMO_CRABSBONUS'),
               ('GOVERNOR_AQUA_PROMOTION_CRABS',       'AQUA_PROMO_CRABSBONUSTWO'),

               ('GOVERNOR_AQUA_PROMOTION_WHALES',   'AQUA_PROMO_WHALESIDENTITY'),
               ('GOVERNOR_AQUA_PROMOTION_WHALES',   'AQUA_PROMO_WHALESBONUS'),
               ('GOVERNOR_AQUA_PROMOTION_WHALES',   'AQUA_PROMO_WHALESBONUSTWO'),

               ('GOVERNOR_AQUA_PROMOTION_TURTLES',   'AQUA_PROMO_TURTLESIDENTITY'),
               ('GOVERNOR_AQUA_PROMOTION_TURTLES',   'AQUA_PROMO_TURTLESBONUS'),
               ('GOVERNOR_AQUA_PROMOTION_TURTLES',   'AQUA_PROMO_TURTLESBONUSTWO'),

               ('GOVERNOR_AQUA_PROMOTION_REEFS',       'AQUA_PROMO_REEFSIDENTITY'),
               ('GOVERNOR_AQUA_PROMOTION_REEFS',       'AQUA_PROMO_REEFSBONUS'),
               ('GOVERNOR_AQUA_PROMOTION_REEFS',       'AQUA_PROMO_REEFSBONUSTWO');

INSERT OR REPLACE INTO Requirements
               (RequirementId,                                       RequirementType)
VALUES   ('REQUIRES_RESOURCE_IS_FISH',           'REQUIREMENT_PLOT_RESOURCE_TYPE_MATCHES'),
               ('REQUIRES_RESOURCE_IS_SHARK',       'REQUIREMENT_PLOT_RESOURCE_TYPE_MATCHES'),   -- Sukritact's Resources
               ('REQUIRES_RESOURCE_IS_CRABS',       'REQUIREMENT_PLOT_RESOURCE_TYPE_MATCHES'),
               ('REQUIRES_RESOURCE_IS_TORTOISE',   'REQUIREMENT_PLOT_RESOURCE_TYPE_MATCHES'),   -- Terra Mirabilis
               ('REQUIRES_RESOURCE_IS_WHALES',       'REQUIREMENT_PLOT_RESOURCE_TYPE_MATCHES'),
               ('REQUIRES_RESOURCE_IS_TURTLES',   'REQUIREMENT_PLOT_RESOURCE_TYPE_MATCHES'),
               ('REQUIRES_RESOURCE_IS_REEFS',       'REQUIREMENT_PLOT_RESOURCE_TYPE_MATCHES'),
               ('REQUIRES_CITY_HAS_LIGHTHOUSE',   'REQUIREMENT_CITY_HAS_BUILDING'),
               ('REQUIRES_CITY_HAS_SHIPYARD',       'REQUIREMENT_CITY_HAS_BUILDING'),
               ('REQUIRES_CITY_HAS_SEAPORT',           'REQUIREMENT_CITY_HAS_BUILDING');

INSERT OR REPLACE INTO RequirementArguments
               (RequirementId,                                       Name,                       Value)
VALUES   ('REQUIRES_RESOURCE_IS_FISH',           'ResourceType',   'RESOURCE_FISH'),
               ('REQUIRES_RESOURCE_IS_SHARK',       'ResourceType',   'RESOURCE_SUK_SHARK'),
               ('REQUIRES_RESOURCE_IS_CRABS',       'ResourceType',   'RESOURCE_CRABS'),
               ('REQUIRES_RESOURCE_IS_TORTOISE',   'ResourceType',   'RESOURCE_TM_TORTOISE'),
               ('REQUIRES_RESOURCE_IS_WHALES',       'ResourceType',   'RESOURCE_WHALES'),
               ('REQUIRES_RESOURCE_IS_TURTLES',   'ResourceType',   'RESOURCE_TURTLES'),
               ('REQUIRES_RESOURCE_IS_REEFS',       'FeatureType',   'FEATURE_REEF'),
               ('REQUIRES_CITY_HAS_LIGHTHOUSE',   'BuildingType',   'BUILDING_LIGHTHOUSE'),
               ('REQUIRES_CITY_HAS_SHIPYARD',       'BuildingType',   'BUILDING_SHIPYARD'),
               ('REQUIRES_CITY_HAS_SEAPORT',           'BuildingType',   'BUILDING_SEAPORT');

INSERT OR REPLACE INTO RequirementSets
               (RequirementSetId,                       RequirementSetType)
VALUES   ('RACHA_PLOT_IS_FISH',               'REQUIREMENTSET_TEST_ANY'),
               ('RACHA_PLOT_IS_CRABS',               'REQUIREMENTSET_TEST_ANY'),
               ('RACHA_PLOT_IS_WHALES',           'REQUIREMENTSET_TEST_ALL'),
               ('RACHA_PLOT_IS_TURTLES',           'REQUIREMENTSET_TEST_ALL'),
               ('RACHA_PLOT_IS_REEFS',               'REQUIREMENTSET_TEST_ALL'),
               ('RACHA_HARBOUR_BUILDINGS',       'REQUIREMENTSET_TEST_ANY');

INSERT OR REPLACE INTO RequirementSetRequirements
               (RequirementSetId,                   RequirementId)
VALUES   ('RACHA_PLOT_IS_FISH',           'REQUIRES_RESOURCE_IS_FISH'),
               ('RACHA_PLOT_IS_CRABS',           'REQUIRES_RESOURCE_IS_CRABS'),
               ('RACHA_PLOT_IS_WHALES',       'REQUIRES_RESOURCE_IS_WHALES'),
               ('RACHA_PLOT_IS_TURTLES',       'REQUIRES_RESOURCE_IS_TURTLES'),
               ('RACHA_PLOT_IS_REEFS',           'REQUIRES_RESOURCE_IS_REEFS'),
               ('RACHA_HARBOUR_BUILDINGS',   'REQUIRES_CITY_HAS_LIGHTHOUSE'),
               ('RACHA_HARBOUR_BUILDINGS',   'REQUIRES_CITY_HAS_SHIPYARD'),
               ('RACHA_HARBOUR_BUILDINGS',   'REQUIRES_CITY_HAS_SEAPORT');

INSERT OR REPLACE INTO Modifiers
               (ModifierId,                                       ModifierType,                                                                           SubjectRequirementSetId)
VALUES   ('AQUA_PROMO_BASE',                           'MODIFIER_SINGLE_CITY_CULTURE_BORDER_EXPANSION',   NULL),
               ('AQUA_PROMO_BASETWO',                   'MODIFIER_BUILDING_YIELD_CHANGE',                                   NULL),

               ('AQUA_PROMO_FISHIDENTITY',           'MODIFIER_SINGLE_CITY_ADJUST_IDENTITY_PER_TURN',   NULL),
               ('AQUA_PROMO_FISHBONUS',               'MODIFIER_PLAYER_ADJUST_PLOT_YIELD',       'RACHA_PLOT_IS_FISH'),
               ('AQUA_PROMO_FISHBONUSTWO',           'MODIFIER_PLAYER_ADJUST_PLOT_YIELD',       'RACHA_PLOT_IS_FISH'),
       
               ('AQUA_PROMO_CRABSIDENTITY',       'MODIFIER_SINGLE_CITY_ADJUST_IDENTITY_PER_TURN',   NULL),
               ('AQUA_PROMO_CRABSBONUS',               'MODIFIER_PLAYER_ADJUST_PLOT_YIELD',       'RACHA_PLOT_IS_CRABS'),
               ('AQUA_PROMO_CRABSBONUSTWO',       'MODIFIER_PLAYER_ADJUST_PLOT_YIELD',       'RACHA_PLOT_IS_CRABS'),

               ('AQUA_PROMO_WHALESIDENTITY',       'MODIFIER_SINGLE_CITY_ADJUST_IDENTITY_PER_TURN',   NULL),
               ('AQUA_PROMO_WHALESBONUS',           'MODIFIER_PLAYER_ADJUST_PLOT_YIELD',       'RACHA_PLOT_IS_WHALES'),
               ('AQUA_PROMO_WHALESBONUSTWO',       'MODIFIER_PLAYER_ADJUST_PLOT_YIELD',       'RACHA_PLOT_IS_WHALES'),

               ('AQUA_PROMO_TURTLESIDENTITY',   'MODIFIER_SINGLE_CITY_ADJUST_IDENTITY_PER_TURN',   NULL),
               ('AQUA_PROMO_TURTLESBONUS',           'MODIFIER_PLAYER_ADJUST_PLOT_YIELD',       'RACHA_PLOT_IS_TURTLES'),
               ('AQUA_PROMO_TURTLESBONUSTWO',   'MODIFIER_PLAYER_ADJUST_PLOT_YIELD',       'RACHA_PLOT_IS_TURTLES'),
       
               ('AQUA_PROMO_REEFSIDENTITY',       'MODIFIER_SINGLE_CITY_ADJUST_IDENTITY_PER_TURN',   NULL),
               ('AQUA_PROMO_REEFSBONUS',               'MODIFIER_PLAYER_ADJUST_PLOT_YIELD',       'RACHA_PLOT_IS_REEFS'),
               ('AQUA_PROMO_REEFSBONUSTWO',       'MODIFIER_PLAYER_ADJUST_PLOT_YIELD',       'RACHA_PLOT_IS_REEFS');

INSERT OR REPLACE INTO ModifierArguments
               (ModifierId,                                       Name,                   Value)
VALUES   ('AQUA_PROMO_BASE',                           'YieldType',   'MODIFIER_SINGLE_CITY_ADJUST_CITY_GROWTH'),   -- +15% growth in this city
               ('AQUA_PROMO_BASE',                           'Amount',           15),
               ('AQUA_PROMO_BASETWO',                   'YieldType',   'YIELD_FOOD'),   -- +2 food from lighthouses, shipyards and seaports
               ('AQUA_PROMO_BASETWO',                   'Amount',           2),                           

               ('AQUA_PROMO_FISHIDENTITY',           'Amount',           -2),
               ('AQUA_PROMO_FISHBONUS',               'YieldType',   'YIELD_FOOD'),
               ('AQUA_PROMO_FISHBONUS',               'Amount',           2),
               ('AQUA_PROMO_FISHBONUSTWO',           'YieldType',   'YIELD_FAITH'),
               ('AQUA_PROMO_FISHBONUSTWO',           'Amount',           1),

               ('AQUA_PROMO_CRABSIDENTITY',       'Amount',           -2),
               ('AQUA_PROMO_CRABSBONUS',               'YieldType',   'YIELD_FOOD'),
               ('AQUA_PROMO_CRABSBONUS',               'Amount',           2),
               ('AQUA_PROMO_CRABSBONUSTWO',       'YieldType',   'YIELD_GOLD'),
               ('AQUA_PROMO_CRABSBONUSTWO',       'Amount',           1),

               ('AQUA_PROMO_WHALESIDENTITY',       'Amount',           -2),
               ('AQUA_PROMO_WHALESBONUS',           'YieldType',   'YIELD_FOOD'),
               ('AQUA_PROMO_WHALESBONUS',           'Amount',           2),
               ('AQUA_PROMO_WHALESBONUSTWO',       'YieldType',   'YIELD_SCIENCE'),
               ('AQUA_PROMO_WHALESBONUSTWO',       'Amount',           1),

               ('AQUA_PROMO_TURTLESIDENTITY',   'Amount',           -2),
               ('AQUA_PROMO_TURTLESBONUS',           'YieldType',   'YIELD_FOOD'),
               ('AQUA_PROMO_TURTLESBONUS',           'Amount',           2),
               ('AQUA_PROMO_TURTLESBONUSTWO',   'YieldType',   'YIELD_SCIENCE'),
               ('AQUA_PROMO_TURTLESBONUSTWO',   'Amount',           1),

               ('AQUA_PROMO_REEFSIDENTITY',       'Amount',           -2),
               ('AQUA_PROMO_REEFSBONUS',               'YieldType',   'YIELD_SCIENCE'),
               ('AQUA_PROMO_REEFSBONUS',               'Amount',           2),
               ('AQUA_PROMO_REEFSBONUSTWO',       'YieldType',   'YIELD_CULTURE'),
               ('AQUA_PROMO_REEFSBONUSTWO',       'Amount',           1);
 
Last edited:
So far as I can tell your SQL code executes just fine in-game

Your governor and all its promotions are being added properly and I am not seeing any database errors.

The in-game action from which my SQL file where I pasted this code has a LoadOrder setting of 150. This ensures that the code executes after both R&F and GS have loaded.

Without "fixer" code added into SQLiteBrowser I get a "no such function: Make Hash" error when I try to execute your code directly in SQliteBrowser. This is normal -- the game adds a few things to the SQL system which are not standard to SQL on its own.
 
LeeS, thanks for looking at the code. I get the same error from the in-game logs with a load order of 8686, which I assume loads my mods last (I explicitly want my mods to over-write any changes other mods make to the same ~things~). Yesterday, I even tried verifying the local game files via steam (as well as deleting the cache files) and still get the exact same issue.

I don't suppose if I said I'm also having bizarre problems with the game thinking there's no ReplacesBuildingType column in the BuildingReplaces table (but only for certain mods), it would shed any light on a possible cause?
 
Here's a tip for you. In your SQLite tool of choice: run this little block first before testing your code:

Code:
DROP TRIGGER OnTypeInsert;
CREATE TRIGGER OnTypeInsert AFTER INSERT ON Types BEGIN UPDATE Types SET Hash = random() Where Type = New.Type; END;
PRAGMA foreign_keys = ON;

This allows you insert into the Types table and enables Foreign Key Constraints which recreates how your code will run in game. This should allow you to discover the offending lines of code. Often FK constraint errors can happen just because you have the correct statements but the wrong order.
 
Pasting that code results in Query executed successfully when I run my code afterwards.

Does this mean I can just add your block of code to the top of files that give me trouble?
 
Pasting that code results in Query executed successfully when I run my code afterwards.

Does this mean I can just add your block of code to the top of files that give me trouble?

No. You definitely don't want this code in your mod - it will do bad things as it overwrites the hashing function. It's just to check for basic syntax errors. If you code works after this testing block then it should work in game PROVIDED the data in database matches up - and the means the exact same version of DLCs, Expansions and other mods being applied. You might also need to add a Load Order to your SQL to make sure it runs AFTER the code adding the entries it relies on. Load Order is the only way to reliably enforce the sequence in which database changes happen.
 
Alas, this brings me back to the initial problem, which I should have been more clear about - the "FOREIGN KEY constraint failed" message also appears in modding.log when I start a game. The mod files all have a load order of 86xx and don't rely on any other mods, it's just adding new governors.
 
When I run your code I get no errors related to it in either Database.log nor Modding.log

The fact that you are getting an error message reporting a column as being invalid when we know the opposite is true signals to me you have something fundamentally wrong but I'm not as yet sure what.

A follow up on your LoadOrder settting: are you stating this load order value in the Properties of your UpdateDatabase actions, or within the properties of the mod itself ? The former method works whereas the later does not.
 
A follow up on your LoadOrder settting: are you stating this load order value in the Properties of your UpdateDatabase actions, or within the properties of the mod itself ? The former method works whereas the later does not.

Could this be where I'm going wrong? When I add an action in ModBuddy, I add the load order to the custom properties section above where I add the flies.

Here's the relevant section of the .modinfo file for this mod:

Code:
  <InGameActions>
    <UpdateDatabase id="GreatGovernors">
      <Properties>
        <LoadOrder>8686</LoadOrder>
      </Properties>
      <File>Governor_Alchemist.sql</File>
      <File>Governor_Animal_Science.sql</File>
      <File>Governor_Architect.sql</File>
      <!-- <File>Governor_Aquafarmer.sql</File> -->
      <File>Governor_Drink.sql</File>
      <File>Governor_Fashion.sql</File>
      <File>Governor_Industrialist.sql</File>
      <File>Governor_Trader.sql</File>
      <File>Governors_Other_Changes.sql</File>
    </UpdateDatabase>
    <UpdateIcons id="GreatGovernorIcons">
      <Properties>
        <LoadOrder>8685</LoadOrder>
      </Properties>
      <!-- <File>Icons.xml</File> -->
      <File>Governors_Icons.sql</File>
    </UpdateIcons>
    <UpdateText id="GreatGovernorText">
      <Properties>
        <LoadOrder>8687</LoadOrder>
      </Properties>
      <File>Governors_Language.sql</File>
    </UpdateText>
    <ImportFiles id="GreatGovernors_Import">
      <Properties>
        <LoadOrder>8600</LoadOrder>
      </Properties>
      <File>Images/Specialists32.dds</File>
      <File>Images/Specialists64.dds</File>
      <File>Images/SpecialistsCityBannerFill22.dds</File>
      <File>Images/SpecialistsCityBannerFill32.dds</File>
      <File>Images/SpecialistsCityBannerSlot22.dds</File>
      <File>Images/SpecialistsCityBannerSlot32.dds</File>
    </ImportFiles>
  </InGameActions>
 
Your modinfo is structured correctly for using LoadOrder. That is not the issue.

Without the mod itself in its current state there's really nothing more that can be done in terms of attempting to deduce your problem, esp given that your code executes for me without trouble.
 
Top Bottom