Strange behaviour when creating EDM
Hi,
This is my first venture into Orcas & EDM so go easy on me OK
I'm trying to create an Entity Data Model in a ADO.Net project. When I do so I'm getting the following message:
The Primary Key in the table 'LocationHistory' cannot be used as a foreignKey for the relationship 'FK_locationHistory_User', the relationship between table 'dbo.User' and table 'dbo.LocationHistory' was excluded.
It doesn't make any sense to me because I'm not trying to use the PK of that table as the FK. Here's the DDL for my database (yes, its incredibly simple):
Code Snippet
CREATE TABLE [dbo].[User](
[UserID] [int] NOT NULL,
[UserName] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED
( [UserID] ASC)
)
CREATE TABLE [dbo].[LocationHistory](
[UserID] [int] NOT NULL,
[LocationTime] [datetime] NOT NULL,
[Latitude] [decimal](18, 3) NOT NULL,
[Longitude] [decimal](18, 3) NOT NULL,
CONSTRAINT [PK_locationHistory] PRIMARY KEY CLUSTERED
( [UserID] ASC, [LocationTime] ASC)
)
GO
ALTER TABLE [dbo].[LocationHistory] WITH CHECK ADD CONSTRAINT [FK_locationHistory_User] FOREIGN KEY([UserID])
REFERENCES [dbo].[User] ([UserID])
GO
ALTER TABLE [dbo].[LocationHistory] CHECK CONSTRAINT [FK_locationHistory_User]
Very simple stuff. Can anyone reproduce this? Better still, anyone know how to fix it?
Thanks
Jamie
My guess is that you are not using June CTP bits. Before June CTP, we were not allowing any column that participates in primary key to be used as part of a foreign key. In your case, the column UserID in LocationHistory table is participating in both primary key and foreign key constraints.
We have added this support in June CTP with a feature which we usally refer to as RI Constraints.
Thanks,
Srikanth
First, apologies for the rapid churn in bits.We want to get the latest features out in a timely fashion to get your input, but sometimes that means less-than-optimal installation experiences.The latest CTP, which has the RI constraint functionality that addresses your requirements, is a prime example; the only way we could get the functionality out in timely manner was by piggybacking on the .NET Framework 3.5 June 2007 CTP that is part of Visual Web Developer Codename "Orcas" Express Edition June 2007 CTP, which unfortunately does not work side-by-side with the rest of Visual Studio that’s part of Orcas Beta 2.Full instructions for installing the June CTP can be found here. We have a beta version of the ADO.NET Entity Framework targeted for the end of August that will be compatible with the Visual Studio Orcas Beta 2 bits, and be a superset of the functionality of this June CTP.
Again, sorry for the churn; I hope the new features prove worth the extra effort...
-Mike
Thanks guys. I'll wait until the August drop. For now I've amended my data model and stuck an artificial PK in there and made the previous PK a unique key instead. Seems to have worked OK.
I understand the trade-off between giving us things that work and giving us things to try out. So no need to apologise.
Thanks again.
Jamie
Srikanth Mandadi - MSFT wrote: