SQL Server – Setting Up Foreign Key Constraints Referencing Multiple Tables

database-designsql serversql server 2014

I have a simple structure where I am not sure how to handle some of the issues that pop up.

There are 4 tables: Reports, ReportParameters, ScheduledReports, ScheduledReportParameters.

ReportParameters lists the needed parameters for the reports.

ScheduledReports is a scheduled instance of a report. (Report X to run on Thursday as an example).

ScheduledReportParameters is the list of parameters and their values to use when this instance runs.

Basic layout:

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)

This has the issue where the parameter associated with a scheduled report doesn't have to agree with what the base report actually expects, it could be to a different base report entirely and not the report the scheduled report is based off of.

So, I thought I could add the ReportID to the ScheduledReportParameters, but that doesn't help as there wouldn't be anything enforcing that the ReportID there agrees with what the ScheduledReport is based off of.

Am I missing something simple? Or, is this trickier than I had thought?

Having a scheduled report with parameters that don't actually go with the report isn't a big deal to me as when I pull them, the joins will automatically filter out any potential bad ones but I would like to prevent bad data going in the first place. I know I could use triggers to do this, but that can get ugly quickly.

Best Answer

It looks like you are not aware that foreign key can be based on several columns.

So, yes, add a column ReportID to ScheduledReportParameters and include it in two foreign keys to enforce the constraints.

One foreign key between ScheduledReportParameters and ScheduledReports using two fields (ScheduledReportID, ReportID).

Second foreign key between ScheduledReportParameters and ReportParameters using two fields (ReportParameterID, ReportID).

Full script

Reports

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

ReportParameters

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

ScheduledReports

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

ScheduledReportParameters

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

It is necessary to have unique index on two columns that are used by the foreign key. I added explicit indexes in this example (IX_ReportParameterID_ReportID and IX_ScheduledReportID_ReportID). You can also add the second column into the primary key of the corresponding table.

Sample data

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');

Testing constraints

Add parameter values for report 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)

Try to add value for the parameter that doesn't belong to the report (parameter 3 belongs to report 2, not 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.

Try to add parameter value for the wrong scheduled report (while parameter 3 belongs to report 2, schedule 1 is not for report 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.

Missing parameter values

Please note, that this schema doesn't prevent missing values for parameters. For example, you can delete value V2:

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

(1 row(s) affected)

Thus now the schedule S1 has value only for parameter P1 and doesn't have value for parameter P2.

Related Question