locked
How do you enforce PK & FK relationships between 2 different DBs ? RRS feed

  • Question

  • User-973886032 posted

    hi guys

    I have a hierarchy of objects e.g Users, financials, logistics etc which tend to be models structures in my different database. (sometimes different servers) 

    How do I enforce a PK and FK relationships across multiple databases e.g.

    A registered user with PK ASPNetUserID(From the table ASPNET Identity)  in database "USERS" database related to another database "ROOT" ? (in another table User_Accounts) 

    Also how does this relate in the asp.net core models ??? with code below

    amespace Accounts.Models.Accounts
    {
        public class User_Accounts
        {
            [Key]
            public int AccountID { get; set; }
    
            [ForeignKey("ASPNetUsers")]
            public string ASPNetID { get; set; }               // How do i map this record both in the db and models
            public string Email { get; set; }
            public string Account_Type { get; set; }
    
         }
    }

    How do I enforce this ?

    many thanks

    Ehi

    Wednesday, July 17, 2019 1:11 PM

All replies

  • User475983607 posted

    How do I enforce a PK and FK relationships across multiple databases e.g.

    This is code that you must design and write.  A trigger works well If the databases are on the same server.  If the database are hosted on separate server then you are forced to write code in to manage the keys and enforce integrity. 

    A registered user with PK ASPNetUserID(From the table ASPNET Identity)  in database "USERS" database related to another database "ROOT" ? (in another table User_Accounts) 

    Also how does this relate in the asp.net core models ??? with code below

    Generally, this is a nonissue because the username, roles, and claims are persisted in an auth cookie or token depending on your design.   The current user is always filtering by the persisted data.

    EF uses the master db making this design impossible. 

    amespace Accounts.Models.Accounts
    {
        public class User_Accounts
        {
            [Key]
            public int AccountID { get; set; }
    
            [ForeignKey("ASPNetUsers")]
            public string ASPNetID { get; set; }               // How do i map this record both in the db and models
            public string Email { get; set; }
            public string Account_Type { get; set; }
    
         }
    }

    Wednesday, July 17, 2019 2:56 PM
  • User665608656 posted

    Hi afrika,

    According to your description, If your databases are on the same server side, you can create foreign and primary keys between two databases by executing this method in sql(excute this method in DB2):

    IF OBJECT_ID(N'[dbo].[func_CHECK_ITEMCODEREFERENCE]', N'FN')IS NOT NULL
       BEGIN
           DROP FUNCTION [dbo].[func_CHECK_ITEMCODEREFERENCE];
       END
    GO
    CREATE FUNCTION [dbo].[func_CHECK_ITEMCODEREFERENCE]
           (
              @p_ITEMCODE   INT = 0
           )
    RETURNS INT
    AS
    BEGIN
        DECLARE @v_RetVal   INT;
         SET @v_RetVal = 0;
         IF EXISTS(SELECT *
                    FROM [DB1].[dbo].[tbl_ITEM]
                    WHERE ITEMCODE = @p_ITEMCODE)
            BEGIN
               SET @v_RetVal = 1;
            END
        RETURN @v_RetVal;
    END

    For more details , you could refer to this link : https://dzone.com/articles/foreign-key-relation-across

    Or use a trigger to manage the referential constraint across databases, for more details , you can refer to this link: Add Foreign Key relationship between two Databases

    Hope these will help you.

    Best Regards,

    YongQing.

    Thursday, July 18, 2019 6:05 AM