Foreign keys?

[Thalassicus]

What's the proper way to set up a foreign key? This didn't work...

CREATE TABLE IF NOT EXISTS Building_PrereqBuildingClassesPercentage (
    FOREIGN KEY(BuildingType) text REFERENCES Buildings(Type),
    FOREIGN KEY(BuildingClassType) text REFERENCES BuildingClasses(Type),
    PercentBuildingNeeded integer default 0
);

 

[MdREV]

I'm not sure why you would worry too much about foreign keys in a system which you control, they are only really of use when you have external parties inserting records and you need to maintain referential integrity. If you are maintaining the data yourself it isn't required.

 

[Thalassicus]

they are only really of use when you have external parties inserting records and you need to maintain referential integrity.

That's the reason, this is a table that will see external use, in particular Gazebo for his CSD mod and possibly others. It's read by lua code so the references will add a layer of error checking my code won't have to be responsible for.

Also I like to follow the same programming practices as whatever group/company's code I'm working with and Firaxis uses references. I basically need to convert this to its sql equivalent:

<Table name="Building_PrereqBuildingClassesPercentage">
    <Column name="BuildingType" type="text" reference="Buildings(Type)"/>
    <Column name="BuildingClassType" type="text" reference="BuildingClasses(Type)"/>
    <Column name="PercentBuildingNeeded" type="integer"/>
</Table>

 

[SamBC]

I believe each column is done like

TYPE name CONSTRAINT (CONSTRAINT)*

(TYPE and name may well be the other way around, as you have them, I'm brain-foggy right now) And "REFERENCES table(column)" is a foreign key constraint. In SQL in general, they can also be specified as table constraints after the column specifications, as

FOREIGN KEY column REFERENCES table(column)

I'm working from memory, so details may be off, but that's about the gist of it. And yes, this means the words 'foreign key' don't need to appear anywhere if they are defined as column constraints.

 

[Thalassicus]

Thanks! Leaving off the "foreign key" clause solved the problem.

CREATE TABLE IF NOT EXISTS Building_PrereqBuildingClassesPercentage (
    BuildingType text REFERENCES Buildings(Type),
    BuildingClassType text REFERENCES BuildingClasses(Type),
    PercentBuildingNeeded integer default 0
);