Is There a Way to Avoid Spurious Foreign Key Constraint Omissions from SQL Server Data Sources?

Question

In a relatively simple Course Catalog SQL Server 2012 Express database with foreign key constraints defined in a database diagram, I receive what I belive to be a spurious error message stating that "a foreign key participating in multiple relationships" causes the constraint to be omitted in the storage model. See the screen capture in step 8 of the "Setting Up the OakmontSQL Data Source" section of my A Visual Studio 2012 LightSwitch “Course Manager” Application Clone with Real-World Data: Part 1 blog post.

I can add the offending relationship without encountering an warning message, as shown in steps 12 through 14, which indicates to me that the warning is inappropriate.

Is there a means for avoiding the need to recreate such relationships?

--rj

---

Microsoft Access 2010 In Depth (QUE Publishing)
OakLeaf Blog
Access 2010 Blog
Amazon Author Blog

Answer 1

Are your PK/FK columns all integers?

---

Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands

If you find a reply helpful, please click "Vote as Helpful", if a reply answers your question, please click "Mark as Answer"

By doing this you'll help people find answers faster.

Answer 2

Yann,

No, the PK/FK columns for this (and most other tables) are strings (natural keys).

Thanks for your interest,

--rj

---

Microsoft Access 2010 In Depth (QUE Publishing)
OakLeaf Blog
Access 2010 Blog
Amazon Author Blog

Answer 3

Hi Roger,

I see you're an Access expert (I thought I recognised your name, I used to be an Access developer myself, years ago - moved to Access/SQL Server, then WPF/SQL Server, then LS).

Sometimes, having expertise in a different technology can be confusing to people, when they start to use LightSwitch. Some things just have to be "unlearned". LS has its own way of doing various things, & while there are occasions where the LS way can be "circumvented" (at the risk of coming back to haunt down the track), it's recommended that things be done the way LS is expecting.

While you can often "get away with" using non-integer PKs, you'll find that LightSwitch will be much happier, & give you less problems, if you do use integer PKs.

Just as an "aside", I don't personally believe in, or use, so-called "natural" keys. But that's just my opinion.

LightSwitch tries to use "best practices" wherever possible, & using auto-incrementing integer PKs this is one of those times. In the intrinsic database LS exclusively uses integer PKs. However, when using attached data sources, LS will "try" to use whatever primary key is currently used in the tables of the attached data source. Sometimes it works flawlessly, sonetimes it doesn't.

While I understand it may not be feasoible to go changing the PK/FK of pre-existing tables, but whever possible I *strongly* recommend an interger PK (or a GUID if needs must).

---

Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands

If you find a reply helpful, please click "Vote as Helpful", if a reply answers your question, please click "Mark as Answer"

By doing this you'll help people find answers faster.