Home

MSSQL - foreign key relation between schemas

I have problem with foreign key relation in linqpad when its between 2 different schema in same database.
Lets say i have these 2 table: "dbo.Customer" and "cash.CashRegister"

cash.CashRegister have FK_CustomerID with a foreign key relation.

When i query CashRegister the Foreign Key relation are not shown? - how come?
It works fine if they are in same schema? - How can i add the relation?

Comments

  • Could you post your data model here? Because I just tried using the following example and 'it works on my machine' - Customer has a property of type IEnumerable<CashRegister> and CashRegister a Customer property:
    CREATE SCHEMA cash
    
    -- Tables
    
    CREATE TABLE dbo.Customer (
      ID UNIQUEIDENTIFIER PRIMARY KEY ROWGUIDCOL NOT NULL
    )
    
    CREATE TABLE cash.CashRegister (
      ID UNIQUEIDENTIFIER PRIMARY KEY ROWGUIDCOL NOT NULL,
      CustomerID UNIQUEIDENTIFIER,
      CONSTRAINT FK_CustomerID FOREIGN KEY ( CustomerID ) REFERENCES dbo.Customer ( ID )
    )
    
    -- Data
    
    INSERT Customer VALUES ( NEWID() )
    
    INSERT cash.CashRegister
    SELECT NEWID(), ID
    FROM Customer
  • edited October 2018
    Hi nescafe, you right, that acutally works fine and now i found the issue.
    If you have 2 tables with the same name, in 2 different schema then the last created table wont have references.

    With this you can see:
    CREATE TABLE dbo.Customer (
      ID UNIQUEIDENTIFIER PRIMARY KEY ROWGUIDCOL NOT NULL
    )
    CREATE TABLE dbo.CashRegister (
      ID UNIQUEIDENTIFIER PRIMARY KEY ROWGUIDCOL NOT NULL,
      CustomerID UNIQUEIDENTIFIER,
      CONSTRAINT FK_CustomerID FOREIGN KEY ( CustomerID ) REFERENCES dbo.Customer ( ID )
    )
    CREATE TABLE cash.CashRegister (
      ID UNIQUEIDENTIFIER PRIMARY KEY ROWGUIDCOL NOT NULL,
      CustomerID UNIQUEIDENTIFIER,
      CONSTRAINT FK_CustomerID FOREIGN KEY ( CustomerID ) REFERENCES dbo.Customer ( ID )
    )
    
    -- Data
    
    INSERT Customer VALUES ( NEWID() )
    
    INSERT cash.CashRegister
    SELECT NEWID(), ID
    FROM Customer
Sign In or Register to comment.