Início / dba / Perguntas / 131524
Accepted
Eric S
Asked: 2016-03-08 10:19:46 +0800 CST

Como configurar restrições de chave estrangeira referenciando mais de uma tabela

  • 772

Tenho uma estrutura simples em que não tenho certeza de como lidar com alguns dos problemas que surgem.

Existem 4 tabelas: Reports, ReportParameters, ScheduledReports, ScheduledReportParameters.

ReportParameterslista os parâmetros necessários para os relatórios.

ScheduledReportsé uma instância agendada de um relatório. (Relatório X a ser executado na quinta-feira como exemplo).

ScheduledReportParametersé a lista de parâmetros e seus valores a serem usados ​​quando esta instância for executada.

Disposição básica:

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)

Isso tem o problema em que o parâmetro associado a um relatório agendado não precisa concordar com o que o relatório base realmente espera, pode ser um relatório base totalmente diferente e não o relatório no qual o relatório agendado se baseia.

Então, pensei que poderia adicionar o ReportIDao ao ScheduledReportParameters, mas isso não ajuda, pois não haveria nada obrigando que o ReportIDlá concordasse com o que o ScheduledReporté baseado.

Estou perdendo algo simples? Ou isso é mais complicado do que eu pensava?

Ter um relatório agendado com parâmetros que na verdade não acompanham o relatório não é um grande problema para mim, pois quando eu os puxo, as junções filtrarão automaticamente quaisquer possíveis erros, mas eu gostaria de evitar que dados ruins entrem no primeiro lugar. Eu sei que poderia usar gatilhos para fazer isso, mas isso pode ficar feio rapidamente.

sql-server database-design

1 respostas

  1. Best Answer

    Parece que você não está ciente de que a chave estrangeira pode ser baseada em várias colunas.

    Então, sim, adicione uma coluna e inclua- ReportIDa ScheduledReportParametersem duas chaves estrangeiras para impor as restrições.

    Uma chave estrangeira entre ScheduledReportParameterse ScheduledReportsusando dois campos (ScheduledReportID, ReportID).

    Segunda chave estrangeira entre ScheduledReportParameterse ReportParametersusando dois campos (ReportParameterID, ReportID).

    roteiro completo

    Relatórios

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

    Parâmetros do relatório

    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

    Relatórios agendados

    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

    Parâmetros do Relatório Agendado

    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

    É necessário ter índice único em duas colunas que são utilizadas pela chave estrangeira. Adicionei índices explícitos neste exemplo ( IX_ReportParameterID_ReportIDe IX_ScheduledReportID_ReportID). Você também pode adicionar a segunda coluna à chave primária da tabela correspondente.

    Dados de amostra

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

    Restrições de teste

    Adicione valores de parâmetro para o relatório 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)

    Tente adicionar valor para o parâmetro que não pertence ao relatório (o parâmetro 3 pertence ao relatório 2, não 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.

    Tente adicionar o valor do parâmetro para o relatório programado errado (enquanto o parâmetro 3 pertence ao relatório 2, o cronograma 1 não é para o relatório 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.

    Valores de parâmetros ausentes

    Observe que esse esquema não evita valores ausentes para parâmetros. Por exemplo, você pode excluir o valor V2:

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

(1 row(s) affected)

    Assim, agora o cronograma S1tem valor apenas para o parâmetro P1e não tem valor para o parâmetro P2.

    • 3

relate perguntas