[SOLVED]ERROR: UNIQUE constraint failed

[Mopafid]

Heya,

I'm trying to add a secondary class to several units at once, and have done it in the same way I've always inserted multiple lines into an SQL query.

However with this, I'm encountering this error - and I don't get why? Do I need to do it in a specific way, or how?

[Gameplay] ERROR: UNIQUE constraint failed: TypeTags.Type, TypeTags.Tag

I'm trying to implement the following:

INSERT INTO TypeTags (Type, Tag)
        VALUES
            ('UNIT_SCYTHIAN_HORSE_ARCHER', 'CLASS_SM_HORSE_CAVALRY'),
            ('UNIT_HORSEMAN', 'CLASS_SM_HORSE_CAVALRY'),
            ('UNIT_CAVALRY', 'CLASS_SM_HORSE_CAVALRY'),
            ('UNIT_HEAVY_CHARIOT', 'CLASS_SM_HORSE_CAVALRY'),
            ('UNIT_KNIGHT', 'CLASS_SM_HORSE_CAVALRY');

EDIT: I have 3 different sets of TypeTags code, and I have no clue which one of the three are causing the error.
I presumed since the base files would attach several different Types to the same Tag, that I could do the same through an SQL update - which seems that I can not? Help? :/



EDITEDIT:
And, solved!
Apparently the following was the culprit:

UPDATE TypeTags --gives the saka its own class
    SET
        Tag = 'CLASS_SAKA_HORSE'
WHERE Type = 'UNIT_SCYTHIAN_HORSE_ARCHER';

I was loading the code in my first post BEFORE this update code, which for some reason caused the error.

I simply moved the UPDATE code further up the code than my INSERT INTO, and the error vanished - and with a quick test in-game, everything seems to work as it should.

 

[Armakoir]

I think I ran into this problem as well. Try individual inserts and not using a table.

 

[Mopafid]

I think I ran into this problem as well. Try individual inserts and not using a table.

I attempted this too, thinking it would fix the problem - Sadly, it does not seem to do so. I've tried to write all my TypeTags like in my original post, and as the following:

INSERT INTO TypeTags (Type, Tag) VALUES ('UNIT_SCYTHIAN_HORSE_ARCHER', 'CLASS_SM_HORSE_CAVALRY');
INSERT INTO TypeTags (Type, Tag) VALUES ('UNIT_HORSEMAN', 'CLASS_SM_HORSE_CAVALRY');
INSERT INTO TypeTags (Type, Tag) VALUES ('UNIT_CAVALRY', 'CLASS_SM_HORSE_CAVALRY');
INSERT INTO TypeTags (Type, Tag) VALUES ('UNIT_HEAVY_CHARIOT', 'CLASS_SM_HORSE_CAVALRY');
INSERT INTO TypeTags (Type, Tag) VALUES ('UNIT_KNIGHT', 'CLASS_SM_HORSE_CAVALRY');

Sadly, no matter how I write it I still receive the error.

 

[Armakoir]

Do you have a duplicate entry somewhere? If INSERT INTO TypeTags (Type, Tag) VALUES ('UNIT_SCYTHIAN_HORSE_ARCHER', 'CLASS_SM_HORSE_CAVALRY'); has already been inserted and you try to insert that specific Tag again, I believe that causes a constraint failure.

Also, use SQLLite to narrow down the specific entry. Once the SQL reaches an error it seems to block all the following entries. So, if UNIT_SCYTHIAN_HORSE_ARCHER was accepted into the database, but UNIT_HORSEMAN was not, then you know the Horseman entry is your problem.

Edit: and if Horseman is your problem then the entries after Horseman should also NOT be in the database.

 

[Mopafid]

Also, use SQLLite to narrow down the specific entry. Once the SQL reaches an error it seems to block all the following entries. So, if UNIT_SCYTHIAN_HORSE_ARCHER was accepted into the database, but UNIT_HORSEMAN was not, then you know the Horseman entry is your problem.

Edit: and if Horseman is your problem then the entries after Horseman should also NOT be in the database.

Got no clue how to utilize SQLLite - Mind linking me a guide or something to get me started?

And, solved!
Your post gave me the idea to re-check anything else that might affect TypeTags, and apparently the following was the culprit:

UPDATE TypeTags --gives the saka its own class
    SET
        Tag = 'CLASS_SAKA_HORSE'
WHERE Type = 'UNIT_SCYTHIAN_HORSE_ARCHER';

I was loading the code in my first post BEFORE this update code, which for some reason caused the error.

I simply moved the UPDATE code further up the code than my INSERT INTO, and the error vanished - and with a quick test in-game, everything seems to work as it should.

 

[Armakoir]

Excellent!

Honestly, I can't remember where I found that SQL guide, but I think it's on these forums somewhere. There's probably many advanced uses for it but I use it mostly for referencing Databases (such as TypeTags) after I've started a game. It allows me to see if my entries made it in and allows me to track down changes I want to make. Regardless, it's a useful tool.

 

[LeeS]

These two SQL chunks are equivalent and behave in the exact same manner upon the game's database:

INSERT INTO Sandwiches (Type, BreadType)
        VALUES
            ('CHEESEBURGER', 'SOURDOUGH'),
            ('CHEESEBURGER', 'HOAGIE');

&

INSERT INTO Sandwiches (Type, BreadType) VALUES ('CHEESEBURGER', 'SOURDOUGH');
INSERT INTO Sandwiches (Type, BreadType) VALUES ('CHEESEBURGER', 'HOAGIE');

Both will result in two rows in the table with data

Type | BreadType
CHEESEBURGER | SOURDOUGH
CHEESEBURGER | HOAGIE

If, however, we then do this:

UPDATE Sandwiches
    SET
        BreadType = 'BUN'
WHERE Type = 'CHEESEBURGER';

We get a unique constraint error because now our Sandwiches table looks like this:

Type | BreadType
CHEESEBURGER | BUN
CHEESEBURGER | BUN

The order in which you perform Updates and Inserts into a table is important, and will have wildly different results on the table if reversed (ie, Update comes before Insert, or vice versa, and then you swap this order).

Note also that if table Sandwiches had 300 rows all with 'Type' as 'CHEESEBURGER', and then our code makes the "Update" shown, each and every one of these pre-existing 300 rows would be re-written with the same value for column 'BreadType'.