ID column

[qqqbbb]

I need to add a couple of entries into Improvement_BonusYieldChanges table. It has "Id" column. And it has to be a unique integer for every entry. How do modders usually handle it? Pick a large number and hope noone else uses it?

 

[LeeS]

The Primary Key constraints for the table are

PRIMARY KEY(Id, ImprovementType, YieldType),

So long as the combination of Id, ImprovementType, and YieldType is unique to all other such combinations, there will not be a problem with whatever number you chose to use for the Id column. Firaxis in fact uses '26' and '27' twice for the Id column when the R&F and GS expansions are both active.

 

[Zugaikotsu]

LeeS is absolutely correct with this table. The three PKs make it easier to mitigate the problem of a sole PK incrementor. For the sake of discussion in a table where it does have the Id as the only PK, I would suggest grabbing the last Id from the rows in the table and incrementing it by one. It is slower but much safer than picking a random number, and generally at a time where speed is not much of a concern. With that said, due to the int ranges (-2147483647 to 2147483647 in C++ and -9223372036854775808 to 9223372036854775808 in SQLite), picking an arbitrary number, as long as it is fairly unique should handle most cases well enough.

INSERT INTO [Improvement_BonusYieldChanges] ([Id], [ImprovementType], [YieldType], [BonusYieldChange])
VALUES(((SELECT [Improvement_BonusYieldChanges].[Id]
FROM [Improvement_BonusYieldChanges]
ORDER BY [Improvement_BonusYieldChanges].[Id] DESC
LIMIT 1) + 1), 'IMPROVEMENT_PAIRIDAEZA', 'YIELD_CULTURE', 4)

 

[qqqbbb]

Zugaikotsu, why did you not use

 MAX(Id)

instead of ?

SELECT [Improvement_BonusYieldChanges].[Id]
FROM [Improvement_BonusYieldChanges]
ORDER BY [Improvement_BonusYieldChanges].[Id] DESC
LIMIT 1) + 1)

 

[Zugaikotsu]

@qqqbb, these would be functionally identical, but, honestly, MAX() just slipped my mind. No real reason.

 

[LeeS]

You need to exercise care in assigning an integer ID value to a table where ID is both integer and the sole primary key. A quick inspection of all such tables in the database shows that none of them have an ID number assigned to a <Row> statement. The game appears to be automatically asserting the ID numbers as the rows are added to the table.

This is similar to the Civ5 behavior wherein parent-table ID #'s were auto-generated by the game as new rows were added to these tables. In these cases in Civ5 it was better except in very specialized circumstances to not attempt to state a row ID number when using either XML or SQL to add a new row to parent tables.