Designer Sync Trouble

I am trying to create a new Database and sync the designer to create the tables, columns, and foreign keys.

I have tried SQL CE, SQLITE and VistaDB3. The designer will create the tables and most of the columns but no associations from foreign keys. It seems on the first pass that all the columns are created(Except foreign key columns) then on the second sync it tries to add the foreign key columns. SQLITE does not support table modification. SQL CE threw errors and so did VistaDB. I have had minimal trouble with MS SQL EXPRESS but cant sync these embedded databases

Any help would be much appreciated.

Unfortunately this will be a problem on SQLite as we can't currently add reference columns to that.  This will need to wait until we fix the "can't create FKs at the same time as tables" limitation.

However, it should be okay on VistaDB and SQL CE.  Could you try a simple case (one or two tables) with the Log SQL option turned on and post the generated SQL?  Also if the designer displays any errors that are not logged in the SQL, please let us know.

If it's critical to get this working on SQLite as well as Vista or CE, please let us know.

 Doing them one/two at a time seems to work except on Single Table inheritance Wntities with associations on Child Entities. My problem with VistaDB at first was because the VistaDB Migration Wizard did not migrate the foreign Keys for the tables so the Lightspeed designer could not add the not null coulmns.

------------------------------------------------OUTPUT-----------------------------------------------------------------------------

-- Add column VenderId and associated foreign key to table ProspectAddress
ALTER TABLE [ProspectAddress] ADD [VenderId] UNIQUEIDENTIFIER NULL REFERENCES [Prospect](UID);;
-- Add column VenderId and associated foreign key to table ProspectCommunication
ALTER TABLE [ProspectCommunication] ADD [VenderId] UNIQUEIDENTIFIER NULL REFERENCES [Prospect](UID);;
-- Add column VenderId and associated foreign key to table ProspectContact
ALTER TABLE [ProspectContact] ADD [VenderId] UNIQUEIDENTIFIER NULL REFERENCES [Prospect](UID);;
-- Add column ProspectId and associated foreign key to table Company
ALTER TABLE [Company] ADD [ProspectId] UNIQUEIDENTIFIER NOT NULL REFERENCES [Prospect](UID);;
/* ERROR:
Prospect: Add column ProspectId and associated foreign key to table Company
  - Alter table only allows columns to be added which can contain null values. The column cannot be added to the table because it does not allow null values.
*/;
-- Add column CustomerId and associated foreign key to table JobCustomer
ALTER TABLE [JobCustomer] ADD [CustomerId] UNIQUEIDENTIFIER NULL REFERENCES [Prospect](Id);;
/* ERROR:
Prospect: Add column CustomerId and associated foreign key to table JobCustomer
  - Invalid column ID. [ Id ]
*/;
-- Add column VenderId and associated foreign key to table PO
ALTER TABLE [PO] ADD [VenderId] UNIQUEIDENTIFIER NULL REFERENCES [Prospect](Id);;
/* ERROR:
Prospect: Add column VenderId and associated foreign key to table PO
  - Invalid column ID. [ Id ]
*/;

-- One or more of the above statements failed.  Review the log for error info.
-- Depending on your database, changes may have been rolled back or may have
-- been partially applied.
 

Hmm, this looks like a VistaDB limitation with adding columns.  It looks like VistaDB can create non-nullable columns only at table creation.  This is obviously to do with nullability of the relationship rather than with STI inheritance and the only fix for this will be for us to address the "create FKs at the same time as tables" limitation.  I'll take a look at this but can't promise anything.

There also seems to be an issue where we're generating the wrong column name (Id rather than UID) in certain cases.  Can you confirm that these cases (the PO and JobCustomer tables) are where the reference is to a STI derived type of Prospect, and that Prospect has an Identity Column Name of UID?  Thanks!

Never mind about that second part -- I've spotted the bug and we'll be getting you a fix.  In the meantime a workaround is to explicitly set the Identity Column Name on the derived types to the same as the base class.

 Got it, Thanks

Dont know why I didnt change the Column name to Id from UID in the first place. I guess some old habits stick.

But then we would have never found this bug 

Cheers

I've committed a fix so that it will now correctly pick up the base class identity column name in STI scenarios, so you won't need to specify it on derived entities.

I've also committed a candidate fix for not being able to create relationships as part of table creation.  This should address the problem with the databases not being able to add the FK columns as part of the second pass.  Please let us know if you run into any trouble with this -- there are quite a few edge cases so there may be some bugs lurking in there.  Thanks!

Oops, forgot to say, these will be in the 5 May nightly build.