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 28
    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.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!