Adding FOREIGN KEY to an existing table

[ITcore]

I'm trying to see if adding the AdjacentDistrict column to the District table will allow restriction of district placement. Currently, no. I'm able to add the column no problem. Issue is trying to add a Foreign Key for it to reference keeps giving me a syntax error. Here is the line:

ALTER TABLE Districts
    ADD FOREIGN KEY (AdjacentDistrict) REFERENCES Buildings(AdjacentDistrict) ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;

I get a syntax error near FOREIGN.

 

[LeeS]

I think it is interpretting your attempt as an attempt to add a new column called "FOREIGN" but the rest of the syntax on the line does not conform to adding a column to an existing table.

I'm not sure you can use "ADD" when specifying a Foreign Key to an existing table. But I would try just using

FOREIGN KEY (AdjacentDistrict) REFERENCES Buildings(AdjacentDistrict) ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;

I would also try adding the column and the Foreign Key constraint all in the same chunk, but I'm not enough of an SQL expert to know whether it will accept that either.

https://www.w3schools.com/sql/sql_alter.asp

Also, your Reference in the posted code is to table Buildings column AdjacentDistrict

That all said, the larger issue is you will just be making extra inert text in the database since it's highly unlikely the DLL will do anything with the new column.

 

[ITcore]

I tried to add it in one command but I don't think the SQL format that Civ6 uses supports it for ALTER TABLE. That said, it seems you are right about the DLL not doing anything with the column. I was hoping it would just use the column like it does in the Buildings table but it doesn't seem to be that easy.

 

[LeeS]

Yeah, each column in each table is specifically implemented within the DLL.

Column-names that are the same across multiple tables often function in the same way across those tables, so long as they were added by Firaxis to the game and its DLL. But there are cases in Civ6 (like there were many many cases in civ5) where an exactly-same-named column in different tables does not function the same.

----------------------------------

As in civ5, adding a column to a table requires an implementation method, such as an lua script, to execute the functionality the DLL would handle for the existing Firaxis-supplied columns within the table. Otherwise adding a column to a table has no effect.

And as in civ5, there appear to be "orphan" columns in certain tables in the Firaxis-supplied game that do nothing.

 

[ITcore]

The waiting game it is, then.

 

[LeeS]

someday we will have access to the DLL

Well, at least we can hope.

 

[ITcore]

Introducing Sid Meier's Civilization VII!*

*includes DLL for Civilization VI

 

[Gedemon]

The "no promises right now" in the latest interview with Ed Beach about the DLL source code sounded a lot like a "not soon, if ever"

While I can understand the "not soon" part, no DLL source code at all would be a death sentence for civ6 modding, they are already too far behind civ5 in term of available methods in Lua

And I doubt it will get better now that they are working on the extensions, usually that means more hardcoding for the new features because of dev time constraints, which is compensated (from a modder PoW) by the direct access to the source code.