This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
I have avery confusing problem. My model contains around 50 tables, and all works great including relationships. However, the are two table, both of which have relationships with other tables, that I cannot create a relationship between them. As usual, I created all the relationships in SQL Server, then used the update from database option to update the model. It says there are no updates, and yet it does not create the relationship in the model. To check the table is updating correctly, in SQL I added a column to both tables, performed the update form database again, and the columns appeared in the model. I removed the columns and added the relationship, updated again, the columns disappeared as expected, but the relationship still did not appear. I have tried to remove both tables frm the model, and readded them. All the relationships these tables have with other tables are recreated in the model, but the relationship between these two tables is not. Has anyone any idea why, a procedure I have followed a fundred times, does not work now? |
|
|
Does it work if you just create a simple database with the two table definitions and the associated FK relationship and then create a new model? |
|
|
I have created a new model and add two new tables with a relationship, and it works fine. I then created another new model, and added the original two tables only. Again it does not show the relationship between them. Yet, if I add the tables that these two have relationships to, it shows the relationships. Very confusing..... |
|
|
The mystery grows.... I just tried to work the reverse of normal. I removed the relationship, and FK column in SQL. I then in the LS model, created the relationship, set nullable and eager load, then told it to update the datebase. It created the relationship correctly between the two tables. At this point I was very happy.... Then I made a completly unrelated change to the SQL database, and asked the model to update from the database. At this point the relationship I created in the model earlier vanished. But was still there in the database? The only way I can get it to return again is to recreate it in the model. But each time I perform an update form the database, the relation vanishes again. This is really confusing, and Imust say anoying....
|
|
|
We haven't seen this before, so I'm guessing here, but this sounds like it *might* be a bug with our mapping between singular entity names and plural table names. What are the names of the two tables involved? Can you share the CREATE TABLE scripts for them? If you set the Table Name explicitly on each entity, does it help? Thanks! |
|
|
To test the theory of theplural table names causing the problem, I renamed in SQL the second table so that it is no longer pluaral. I then removed and re-added the tables to the model. Still the relationship does not appear. Below are the create table scripts from SQL. CREATE TABLE [dbo].[PoSVoucher]( ALTER TABLE [dbo].[PoSVoucher] CHECK CONSTRAINT [FK_PoSVoucher_PoSInvoice] ALTER TABLE [dbo].[PoSVoucher] WITH CHECK ADD CONSTRAINT [FK_PoSVoucher_PoSVoucherSale] FOREIGN KEY([PoSVoucherSaleId]) ALTER TABLE [dbo].[PoSVoucher] CHECK CONSTRAINT [FK_PoSVoucher_PoSVoucherSale] ALTER TABLE [dbo].[PoSVoucher] ADD CONSTRAINT [DF_tblVouchers_DateIssued] DEFAULT (getdate()) FOR [DateIssued] ALTER TABLE [dbo].[PoSVoucher] ADD CONSTRAINT [DF_PoSVoucher_InvoiceNo] DEFAULT ((0)) FOR [InvoiceId] ALTER TABLE [dbo].[PoSVoucher] ADD CONSTRAINT [DF_PoSVoucher_FolioId] DEFAULT ((0)) FOR [FolioId] ALTER TABLE [dbo].[PoSVoucher] ADD CONSTRAINT [DF_PoSVoucher_ProductId] DEFAULT ((0)) FOR [ProductId] ALTER TABLE [dbo].[PoSVoucher] ADD CONSTRAINT [DF_PoSVoucher_Value] DEFAULT ((0)) FOR [Value]
CREATE TABLE [dbo].[PoSVoucherSale]( GO ALTER TABLE [dbo].[PoSVoucherSale] WITH CHECK ADD CONSTRAINT [Session_VoucherSale] FOREIGN KEY([SessionId]) ALTER TABLE [dbo].[PoSVoucherSale] CHECK CONSTRAINT [Session_VoucherSale] ALTER TABLE [dbo].[PoSVoucherSale] ADD CONSTRAINT [DF__PoSVouche__Sales__3C751A5B] DEFAULT ((0)) FOR [SalesPrice] ALTER TABLE [dbo].[PoSVoucherSale] ADD CONSTRAINT [DF__PoSVouche__Curre__3D693E94] DEFAULT ((0)) FOR [CurrencyAmount] ALTER TABLE [dbo].[PoSVoucherSale] ADD CONSTRAINT [DF__PoSVouche__Curre__3E5D62CD] DEFAULT ((1)) FOR [CurrencyRate] ALTER TABLE [dbo].[PoSVoucherSale] ADD CONSTRAINT [DF__PoSVouche__Chang__3F518706] DEFAULT ((0)) FOR [ChangeGiven] ALTER TABLE [dbo].[PoSVoucherSale] ADD CONSTRAINT [DF__PoSVouche__Amoun__4045AB3F] DEFAULT ((0)) FOR [AmountReceived] |
|
|
Hmm, I've run those scripts (with dummy PoSInvoice and PoSCashBoxHistory tables) and the relationship is coming up fine for me. I can only imagine there's something else going on which is throwing a spanner in the works. Is it possible for you to send us a backup of your database? (We don't need the data, just the schema, so feel free to scrub the data.) |
|
|
Can you give me an email address I can send it to, thanks. |
|
|
ivan @ the obvious domain name. Let me know when you've sent it in case Gmail decides to go on one of its occasional blocking sprees! |
|
|
I have sent you the database backup. Thanks |
|
|
Thank you Mark -- just to confirm we have now been able to reproduce the issue and are investigating. |
|
|
Okay, the issue is that there is both a unique constraint *and* a primary key on PoSVoucherSale.Id. For some reason when the foreign key gets created, SQL Server records it against the unique constraint name in preference to the primary key constraint name, which defeats the query LightSpeed uses to resolve foreign keys (basically because the unique constraint doesn't appear in the views we consult). If I delete the UQ_PoSVoucherSales constraint from PoSVoucherSale, then it starts working again (because the FK now refers to the PK constraint instead of the unique constraint). Could you give this a try and let us know if it works for you? As far as I am aware this is benign because the primary key will implicitly impose a unique constraint. If you feel the unique constraint is required in addition to the PK, let me know and I'll see if we can work something out. |
|
|
I have found and removed the unique key. I am not sure where it came from. I agree it is not needed and has been removed. The relationship now shows in the model as expected. Many thanks for your help, that really was confusing me. |
|