Foreign Keys in SQlite/MySQL?

Hi all,

I'm trying to model some very simple relations between two classes with the Modeler - but when I try to update the database schema, the SQlite and MySQL providers report that they don't support adding references. Right, but how can these relations be modeled with one of these providers?

Thanks for your ideas!

Philipp

Hello Philipp,

You can still model these relations in the designer; what the message means is that the designer can't automatically update the database structure.  So you just need to go into your database design tool (e.g. MySQL Administrator) and add the required column and foreign key by hand.  (You can use the Update Database dialog as a reminder of what you need to do.)

By the way, we have now removed this restriction for MySQL.  This enhancement is in current nightlies which you can get from http://www.mindscape.co.nz/Products/LightSpeed/nightlybuilds.aspx (Express edition) or the store (Standard and higher editions).  I will check on the SQLite situation and see whether we can remove the limitation there as well; I seem to remember that there were some limitations in what SQLite.net allowed us to do in terms of changing existing table schemas, but will investigate further.

Ivan,

Thanks for your help - now that you tell me, it seems like an obvious solution. The only drawback is that even if I introduce the column(s) manually, I can't use the update database afterwards - LightSpeed tries do delete the manually added column and add it again afterwards (I used the SQLite provider to test that). Other than that, a great solution - I'm considering using it for our new ERP system.

It sounds like there is some sort of mismatch between the columns and foreign keys that you have created and LightSpeed's assumptions about how databases are laid out.  LightSpeed uses the presence of a foreign key to determine that a column represents an association: a column without a foreign key is interpreted as an entity property, a column with a foreign key is interpreted as an association.  So what you're seeing is consistent with the database containing a column but not a foreign key.  This would make LightSpeed think, "Oh, there's a column called [say] ContributorId that's in the database but not in the model, so I'll offer to delete it; and there's an association to the Contributor entity that's in the model but not in the database, so I'll offer to create it" (rather than thinking, "Right, there's an association to the Contributors table in the database and an association to the Contributor entity in the model, jolly good").

So the first thing to do is check your database to ensure that the foreign key is there.  If not, you will need to add the foreign key by hand using your database design tool.  That should stop LightSpeed getting confused.

Another possibility is if the names of the association and the column do not match up.  The designer assumes that the column name is the BackreferenceName of the association followed by "Id".  For example, if the association has a BackreferenceName of ApprovedBy, the column should be called ApprovedById.

If the foreign key is there (or the problem persists after you add the foreign key), could you post the SQLite .db3 file and the .lsmodel file please?  This will enable us to reproduce the behaviour and advise you on what is happening (or fix it if it's a bug).  You can attach files via the Options tab or email them via the contact form.  Thanks!

As an interim workaround, you can still use Update Database, you will just need to uncheck the "delete the manually added column" and "add it in again" checkboxes in the Update dialog.  LightSpeed applies only the selected updates.  Unfortunately you will need to do this each time because we don't currently have a "don't offer again" option.

I think the problem is that SQLite doesn't support foreign keys - you can use the usual SQL syntax to create a constraint while creating a table, but ALTER TABLE doesn't seem to understand

ALTER TABLE Anschrift
ADD CONSTRAINT FOREIGN KEY (FirmaId) REFERENCES Firma(Id);

I didn't try with MySQL.

So if I understand you correctly, LightSpeed will also check for the existance of the foreign key, not just for the appropriate columns? That would IMHO rule out SQLite and MySQL from working properly in the designer, wouldn't it?

Yes, you are correct that LightSpeed will check for the existence of the foreign key, not just the column.

However, SQLite does support foreign keys; you can define them in CREATE TABLE.  But we have seen the same issue as you: that there doesn't seem to be any support for adding a foreign key after the table has been created (i.e. via ALTER TABLE).  This is why the designer can't add relationships to an existing SQLite table, and you will need to use an alternative tool that is more specialised to SQLite (which might do it by going behind DDL's back, including its own extensions to the SQLite core or silently doing a save data-drop-recreate cycle).  We will continue to look at this if it is a pain point though.

MySQL also supports foreign keys and does allow them to be added via ALTER TABLE.  Recent nightlies of the designer therefore support adding relationships to existing MySQL tables.  (This didn't make it into RTM because adding a foreign key in MySQL was slightly more complex than our other databases.)