主页 / dba / 问题 / 131524
Accepted
Eric S
Asked: 2016-03-08 10:19:46 +0800 CST

如何设置引用多个表的外键约束

  • 772

我有一个简单的结构，我不确定如何处理弹出的一些问题。

有 4 个表：Reports, ReportParameters, ScheduledReports, ScheduledReportParameters。

ReportParameters列出报告所需的参数。

ScheduledReports是报表的预定实例。（以周四报 X 运行为例）。

ScheduledReportParameters是此实例运行时要使用的参数及其值的列表。

基本布局：

Reports:
ReportID (PK)

ReportParameters:
ReportParameterID (PK)
ReportID (FK to Reports)
Name

ScheduledReports:
ScheduledReportID (PK)
ReportID (FK to reports)

ScheduledReportParameters:
ScheduledReportID (FK to ScheduledReports)
ReportParameterID (FK to ReportParameters)
Value
(PK is formed by the ScheduledReportID and ReportParameterID)

这有一个问题，即与计划报告关联的参数不必与基础报告的实际预期一致，它可能是完全不同的基础报告，而不是计划报告所基于的报告。

所以，我想我可以将 the 添加ReportID到 the ScheduledReportParameters，但这无济于事，因为没有任何强制要求ReportIDthere 同意 there 的ScheduledReport基础。

我错过了一些简单的东西吗？或者，这比我想象的更棘手吗？

有一个计划的报告，其中的参数实际上并不与报告一起使用，这对我来说没什么大不了的，因为当我提取它们时，连接会自动过滤掉任何潜在的坏数据，但我想防止坏数据进入第一名。我知道我可以使用触发器来做到这一点，但这很快就会变得丑陋。

sql-server database-design

1 个回答

  1. Best Answer

    看起来您不知道外键可以基于多个列。

    所以，是的，添加一列并将ReportID其ScheduledReportParameters包含在两个外键中以强制执行约束。

    两个字段之间ScheduledReportParameters和使用一个外键。ScheduledReports(ScheduledReportID, ReportID)

    ScheduledReportParameters两个字段之间和ReportParameters使用两个字段之间的第二个外键(ReportParameterID, ReportID)。

    完整脚本

    报告

    CREATE TABLE [dbo].[Reports](
    [ReportID] [int] NOT NULL,
    [ReportName] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Reports] PRIMARY KEY CLUSTERED 
(
    [ReportID] ASC
))

    报告参数

    CREATE TABLE [dbo].[ReportParameters](
    [ReportParameterID] [int] NOT NULL,
    [ReportID] [int] NOT NULL,
    [ParameterName] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_ReportParameters] PRIMARY KEY CLUSTERED 
(
    [ReportParameterID] ASC
))
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_ReportParameterID_ReportID] 
ON [dbo].[ReportParameters]
(
    [ReportParameterID] ASC,
    [ReportID] ASC
)
GO

ALTER TABLE [dbo].[ReportParameters]  WITH CHECK ADD  
CONSTRAINT [FK_ReportParameters_Reports] FOREIGN KEY([ReportID])
REFERENCES [dbo].[Reports] ([ReportID])
GO

ALTER TABLE [dbo].[ReportParameters] CHECK CONSTRAINT [FK_ReportParameters_Reports]
GO

    预定报告

    CREATE TABLE [dbo].[ScheduledReports](
    [ScheduledReportID] [int] NOT NULL,
    [ReportID] [int] NOT NULL,
    [ScheduleName] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_ScheduledReports] PRIMARY KEY CLUSTERED 
(
    [ScheduledReportID] ASC
))
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_ScheduledReportID_ReportID] 
ON [dbo].[ScheduledReports]
(
    [ScheduledReportID] ASC,
    [ReportID] ASC
)
GO

ALTER TABLE [dbo].[ScheduledReports]  WITH CHECK ADD  
CONSTRAINT [FK_ScheduledReports_Reports] FOREIGN KEY([ReportID])
REFERENCES [dbo].[Reports] ([ReportID])
GO

ALTER TABLE [dbo].[ScheduledReports] CHECK CONSTRAINT [FK_ScheduledReports_Reports]
GO

    预定报告参数

    CREATE TABLE [dbo].[ScheduledReportParameters](
    [ScheduledReportParametersID] [int] NOT NULL,
    [ScheduledReportID] [int] NOT NULL,
    [ReportID] [int] NOT NULL,
    [ReportParameterID] [int] NOT NULL,
    [ParameterValue] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_ScheduledReportParameters] PRIMARY KEY CLUSTERED 
(
    [ScheduledReportParametersID] ASC
))
GO

ALTER TABLE [dbo].[ScheduledReportParameters]  WITH CHECK ADD  
CONSTRAINT [FK_ScheduledReportParameters_ReportParameters] 
FOREIGN KEY([ReportParameterID], [ReportID])
REFERENCES [dbo].[ReportParameters] ([ReportParameterID], [ReportID])
GO

ALTER TABLE [dbo].[ScheduledReportParameters] 
CHECK CONSTRAINT [FK_ScheduledReportParameters_ReportParameters]
GO

ALTER TABLE [dbo].[ScheduledReportParameters]  WITH CHECK ADD  
CONSTRAINT [FK_ScheduledReportParameters_ScheduledReports] 
FOREIGN KEY([ScheduledReportID], [ReportID])
REFERENCES [dbo].[ScheduledReports] ([ScheduledReportID], [ReportID])
GO

ALTER TABLE [dbo].[ScheduledReportParameters] 
CHECK CONSTRAINT [FK_ScheduledReportParameters_ScheduledReports]
GO

    外键使用的两列必须有唯一索引。我在这个例子中添加了明确的索引（IX_ReportParameterID_ReportID和IX_ScheduledReportID_ReportID）。您还可以将第二列添加到相应表的主键中。

    样本数据

    INSERT INTO [dbo].[Reports] 
    ([ReportID]
    ,[ReportName]) 
VALUES
    (1,'R1'),
    (2,'R2');

INSERT INTO [dbo].[ReportParameters]
    ([ReportParameterID]
    ,[ReportID]
    ,[ParameterName])
VALUES
    (1,1,'R1P1'),
    (2,1,'R1P2'),
    (3,2,'R2P3'),
    (4,2,'R2P4'),
    (5,2,'R2P5');

INSERT INTO [dbo].[ScheduledReports]
    ([ScheduledReportID]
    ,[ReportID]
    ,[ScheduleName])
VALUES
    (1,1,'S1');

    测试约束

    为报表 1 添加参数值：

    INSERT INTO [dbo].[ScheduledReportParameters]
    ([ScheduledReportParametersID]
    ,[ScheduledReportID]
    ,[ReportID]
    ,[ReportParameterID]
    ,[ParameterValue])
VALUES
    (1,1,1,1,'R1 P1 V1'),
    (2,1,1,2,'R1 P2 V2');

(2 row(s) affected)

    尝试为不属于报表的参数添加值（参数 3 属于报表 2，而不是 1：

    INSERT INTO [dbo].[ScheduledReportParameters]
    ([ScheduledReportParametersID]
    ,[ScheduledReportID]
    ,[ReportID]
    ,[ReportParameterID]
    ,[ParameterValue])
VALUES
    (3,1,1,3,'V');

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_ScheduledReportParameters_ReportParameters". The conflict occurred in database "AdventureWorks2014", table "dbo.ReportParameters".
The statement has been terminated.

    尝试为错误的计划报告添加参数值（参数 3 属于报告 2，计划 1 不用于报告 2）：

    INSERT INTO [dbo].[ScheduledReportParameters]
    ([ScheduledReportParametersID]
    ,[ScheduledReportID]
    ,[ReportID]
    ,[ReportParameterID]
    ,[ParameterValue])
VALUES
    (3,1,2,3,'V');

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_ScheduledReportParameters_ScheduledReports". The conflict occurred in database "AdventureWorks2014", table "dbo.ScheduledReports".
The statement has been terminated.

    缺少参数值

    请注意，此模式不会阻止参数的缺失值。例如，您可以删除值V2：

    DELETE FROM [dbo].[ScheduledReportParameters]
WHERE [ScheduledReportParametersID] = 2;

(1 row(s) affected)

    因此，现在 scheduleS1仅具有 parameterP1的值，而没有 parameter 的值P2。

    • 3

相关问题