SSMS中Stocks与OrderDetails外键列未显示，Dataverse迁移求助

我有一个基于Microsoft Visual Studio Lightswitch开发、SQL为后端的应用，现在要在PowerApps中重构并迁移数据库到Dataverse。遇到了Stocks表与OrderDetails表的关系问题：

• 两者是多对一关系，但在SSMS里看不到外键引用列，不过关系在Keys节点和数据库图中可见，应用也能正常使用这个关系
• 其他表的关系都正常显示，需要让这两个表显示关联ID以完成Dataverse迁移

OrderDetails表脚本 #

USE [BCG_Maintenance]
GO

/****** Object:  Table [dbo].[OrderDetails]    Script Date: 03/05/2024 15:46:06 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[OrderDetails](
    [Id] [int] NOT NULL CONSTRAINT [DF_OrderDetails_Id]  DEFAULT ((0)),
    [RowVersion] [timestamp] NOT NULL,
    [Quantity] [float] NOT NULL CONSTRAINT [DF_OrderDetails_Quantity]  DEFAULT ((0)),
    [StaticSellingPrice] [decimal](18, 2) NOT NULL CONSTRAINT [DF_OrderDetails_StaticSellingPrice]  DEFAULT ((0)),
    [DiscountPercent] [decimal](18, 2) NULL,
    [StaticAppOrderItem] [nvarchar](255) NULL,
    [OrderDetails_OrderHeaders] [int] NOT NULL CONSTRAINT [DF_OrderDetails_OrderDetails_OrderHeaders]  DEFAULT ((0)),
 CONSTRAINT [PK_OrderDetails] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[OrderDetails]  WITH CHECK ADD  CONSTRAINT [OrderDetail_OrderHeaders] FOREIGN KEY([OrderDetails_OrderHeaders])
REFERENCES [dbo].[OrderHeaders] ([Id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[OrderDetails] CHECK CONSTRAINT [OrderDetail_OrderHeaders]
GO

ALTER TABLE [dbo].[OrderDetails]  WITH CHECK ADD  CONSTRAINT [OrderDetail_Stock] FOREIGN KEY([Id])
REFERENCES [dbo].[Stocks] ([Id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[OrderDetails] CHECK CONSTRAINT [OrderDetail_Stock]
GO

Stocks表脚本 #

USE [BCG_Maintenance]
GO

/****** Object:  Table [dbo].[Stocks]    Script Date: 03/05/2024 15:59:57 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Stocks](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [RowVersion] [timestamp] NOT NULL,
    [CustomStockID] [nvarchar](255) NULL,
    [StockName] [nvarchar](255) NOT NULL CONSTRAINT [DF_Stocks_StockName]  DEFAULT (''),
    [SerialNumber] [nvarchar](255) NULL,
    [Licence] [nvarchar](255) NULL,
    [DateReceived] [datetime] NOT NULL CONSTRAINT [DF_Stocks_DateReceived]  DEFAULT ((0)),
    [BCGOrderNo] [nvarchar](255) NULL,
    [BCGInvoiceNo] [nvarchar](255) NULL,
    [Location] [nvarchar](255) NULL,
    [SellingPrice] [decimal](18, 2) NOT NULL CONSTRAINT [DF_Stocks_SellingPrice]  DEFAULT ((0)),
    [UnitPrice] [decimal](18, 2) NULL,
    [ContractLineDiscountPercent] [decimal](18, 9) NULL,
    [BCGID] [nvarchar](255) NULL,
    [Stock_Model] [int] NOT NULL CONSTRAINT [DF_Stocks_Stock_Model]  DEFAULT ((0)),
    [Stock_Contract] [int] NULL,
    [Stock_NextNums] [int] NOT NULL CONSTRAINT [DF_Stocks_Stock_NextNums]  DEFAULT ((0)),
    [Stock_DeliveryNotes] [int] NULL,
 CONSTRAINT [PK_Stocks] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Stocks]  WITH CHECK ADD  CONSTRAINT [Stock_Contract] FOREIGN KEY([Stock_Contract])
REFERENCES [dbo].[Contracts] ([Id])
GO

ALTER TABLE [dbo].[Stocks] CHECK CONSTRAINT [Stock_Contract]
GO

ALTER TABLE [dbo].[Stocks]  WITH CHECK ADD  CONSTRAINT [Stock_DeliveryNotes] FOREIGN KEY([Stock_DeliveryNotes])
REFERENCES [dbo].[DeliveryNotesSet] ([Id])
GO

ALTER TABLE [dbo].[Stocks] CHECK CONSTRAINT [Stock_DeliveryNotes]
GO

ALTER TABLE [dbo].[Stocks]  WITH CHECK ADD  CONSTRAINT [Stock_NextNums] FOREIGN KEY([Stock_NextNums])
REFERENCES [dbo].[NextNumsSet] ([Id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[Stocks] CHECK CONSTRAINT [Stock_NextNums]
GO

ALTER TABLE [dbo].[Stocks]  WITH CHECK ADD  CONSTRAINT [Stock_Products] FOREIGN KEY([Stock_Model])
REFERENCES [dbo].[Products] ([Id])
GO

ALTER TABLE [dbo].[Stocks] CHECK CONSTRAINT [Stock_Products]
GO

问题根源与修复方案 #

根源分析 #

从脚本可以看到，当前OrderDetail_Stock外键约束是用OrderDetails.Id关联Stocks.Id，这本质是一对一关系，但你实际需要的是多对一（多个OrderDetails对应一个Stock）。这种反向关联导致SSMS无法识别为常规多对一外键列，所以看不到单独的关联ID字段。

修复步骤 #

1. 删除错误的外键约束

   ALTER TABLE [dbo].[OrderDetails] DROP CONSTRAINT [OrderDetail_Stock]
   

2. 添加正确的外键列
   在OrderDetails表中新增存储Stock ID的列：

   ALTER TABLE [dbo].[OrderDetails]
   ADD [OrderDetails_Stock] INT NOT NULL DEFAULT 0
   

3. 创建正确的多对一外键约束
   基于新增列关联Stocks主键：

   ALTER TABLE [dbo].[OrderDetails] WITH CHECK
   ADD CONSTRAINT [OrderDetail_Stock] FOREIGN KEY([OrderDetails_Stock])
   REFERENCES [dbo].[Stocks]([Id])
   ON DELETE CASCADE
   

4. 同步现有数据
   如果之前OrderDetails.Id与Stocks.Id一一对应，执行数据映射：

   UPDATE [dbo].[OrderDetails]
   SET [OrderDetails_Stock] = [Id]
   

5. 验证修改
   刷新SSMS表结构，OrderDetails表会显示OrderDetails_Stock外键列，数据库关系也会正常显示为多对一。

Dataverse迁移注意事项 #

• 修改完成后，Power Platform数据迁移向导会自动识别该外键关系，生成对应的Dataverse lookup字段
• 迁移前务必备份数据库，避免数据丢失

内容的提问来源于stack exchange，提问作者user24853966