Foreign Keys

Module Development
1

Is there a way to define foreign keys in a database created by Ignition?

Example:
MakeTable:
StringField makeid = new StringField(AssetManagerDataSettings.META,“makeid”).setDefault(“makeid”);

DBTableSchema mySchema = new DBTableSchema(“maketable”, context.getDatasourceManager().getDatasource(settings.getDatasourceId()).getTranslator());
mySchema.addRequiredColumn(“makeid”, DataType.Int8,
ColumnProperty.Primary);

ModelTable:
StringField modelid = new StringField(AssetManagerDataSettings.META, “modelid”).setDefault(“modelid”);
StringField makeid = new StringField(AssetManagerDataSettings.META,“makeid2”).setDefault(“makeid”);

DBTableSchema mySchema = new DBTableSchema(“modeltable”, context.getDatasourceManager().getDatasource(settings.getDatasourceId()).getTranslator());
mySchema.addRequiredColumn(“modelid”, DataType.Int8,
ColumnProperty.Primary);
mySchema.addRequiredColumn(“makeid2”, DataType.Int8,null);

Now I need a foreign key connection for the two makeids.

2

Your code’s a little confusing, as you’re mixing two different concepts: the persistent record system, with StringField, which is used for storing data in the Internal DB, and the DBTableSchema class, which helps you make tables in a database.

In the persistent record system, you can make foreign keys by using the ReferenceField field type.

With DBTableSchema, unfortunately there’s no way to define a foreign key. However, it’s just generating sql for you, so you could probably just add the constraint afterwards. That is, build your DBTableSchema, and then, instead of calling [tt]verifyAndUpdate[/tt], call [tt]refreshTableState[/tt]. That will give you a state that will tell you whether the table exists. If false, call [tt]createTable[/tt], and then execute your own ALTER statement against the connection. Here’s the syntax, according to mysql:

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name, ...) REFERENCES tbl_name (index_col_name,...) [ON DELETE reference_option] [ON UPDATE reference_option]
I believe the base syntax of “ALTER TABLE ADD CONSTRAINT” should work on all the dbs. You can do your own rough translation if needed by looking at the DatabaseVendor on the connection, if necessary.

Ultimately we should include support for foreign keys on that class, but Ignition currently doesn’t require them (some relational table could use them, like SQLTags, but we’ve never decided to actually enforce it).

Regards,

3

Thank you. That was helpful.