locked
Relationship based on a type column RRS feed

  • Question

  • Lets say I have the following tables:

    CREATE TABLE AAA (id INT PRIMARY KEY IDENTITY(1, 1) NOT NULL);
    CREATE TABLE BBB (id INT PRIMARY KEY IDENTITY(1, 1) NOT NULL);
    CREATE TABLE CCC (id INT PRIMARY KEY IDENTITY(1, 1) NOT NULL
    , xtype INT NOT NULL -- 1 AAA, 2 BBB 
    , xid INT NOT NULL
    )
    Go

    Can I create relationships from AAA to CCC, and from BBB to CCC in LS?

    Using database workarounds, it is achiveable in many ways (computed columns-updateable views-etc), but what about inside LS?

    Thanks


    Tuesday, February 19, 2013 10:12 PM

All replies

  • I might be missing something here. Are you attaching to an external database? If so, why don't you create the relationships in the database itself, before attaching it in LightSwitch. Any existing relationships in an attached data source will also be modelled in LightSwitch.

    If this is using the intrinsic data (ApplicationData), I don't see where the problem is.


    Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands
     
    Please click "Mark as Answer" if a reply answers your question. Please click "Vote as Helpful" , if you find a reply helpful.
     
    By doing this you'll help others to find answers faster.

    Wednesday, February 20, 2013 12:26 AM
    Moderator
  • I have tried both through the entity designer and raw tables and attaching an external datasource.

    The design would allow for inconsistant data, and LightSwitch wont allow it...

    "Foreign key constraint 'FK_ccc_bbb' has been omitted from the storage model. Column 'idx' of table 'Tbd.ccc' is a foreign key participating in multiple relationships. A one-to-one Entity Model will not validate since data inconsistency is possible."

    Wednesday, February 20, 2013 12:28 AM
  • Thank you Yann 

    It is an external database, and a foreign key can be created in database but it wouldn’t provide the expected referential integrity or the expected cascade actions.

    The following insert statements will better explain the problem:
    SET IDENTITY_INSERT AAA ON
    INSERT INTO AAA (ID) VALUES (1), (2)
    SET IDENTITY_INSERT AAA OFF
    GO
    SET IDENTITY_INSERT BBB ON
    INSERT INTO BBB (ID)VALUES (2), (3)
    SET IDENTITY_INSERT BBB OFF
    GO
    INSERT INTO CCC (XTYPE, XID) VALUES (1, 1), (1, 2), (2, 2), (2, 3)
    GO

    The following ALTER TABLE CCC ADD CONSTRAINT FK_CCC_AAA FOREIGN KEY (XID) REFERENCES AAA, cannot be created.

    What I would like to be created is something like: ALTER TABLE CCC ADD CONSTRAINT FK_CCC_AAA FOREIGN KEY (XTYPE,XID) REFERENCES AAA (1, id)
    Which of course isn’t possible in Sql server (in other databases?).

    Is it possible to do something like this in LS entity designer?

    Wednesday, February 20, 2013 6:46 AM
  • Thank you Glenn

    Pls check my answer to Yann. It is an old database schema that I have to use, and I would like to make minimum modifications as possible.

    One solution (in another version) could be to be able to use filtered queries in relationship creation.

    Wednesday, February 20, 2013 6:54 AM