MSSQL - foreign key relation between schemas

NoxiaZ · October 2018

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?

nescafe · October 2018

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

NoxiaZ · 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

MSSQL - foreign key relation between schemas

Comments

Categories