Tenho um banco de dados SQL Server com as seguintes tabelas:
CREATE TABLE [dbo].[Rule]
(
[RuleId] [int] IDENTITY(1,1) NOT NULL,
[Value1] [int] NOT NULL,
[Value2] [int] NOT NULL,
[Value3] [int] NOT NULL,
CONSTRAINT [PK_Rule] PRIMARY KEY CLUSTERED ([RuleId] ASC)
)
GO
CREATE TABLE [dbo].[Type]
(
[TypeId] [INT] IDENTITY(1,1) NOT NULL,
[Description] [varchar](50) NOT NULL,
CONSTRAINT [PK_Type] PRIMARY KEY CLUSTERED ([TypeId] ASC)
)
GO
CREATE TABLE [dbo].[RuleType]
(
[RuleId] [int] NOT NULL,
[TypeId] [int] NOT NULL,
CONSTRAINT [PK_RuleType] PRIMARY KEY CLUSTERED ([RuleId] ASC, [TypeId] ASC)
)
GO
ALTER TABLE [dbo].[RuleType] WITH CHECK ADD CONSTRAINT [FK_RuleType_Type] FOREIGN KEY([TypeId])
REFERENCES [dbo].[Type] ([TypeId])
GO
ALTER TABLE [dbo].[RuleType] CHECK CONSTRAINT [FK_RuleType_Type]
GO
ALTER TABLE [dbo].[RuleType] WITH CHECK ADD CONSTRAINT [FK_RuleType_Rule] FOREIGN KEY([RuleId])
REFERENCES [dbo].[Rule] ([RuleId])
GO
ALTER TABLE [dbo].[RuleType] CHECK CONSTRAINT [FK_RuleType_Rule]
GO
/*Test data*/
INSERT INTO [dbo].[Type] ([Description]) VALUES ('Type1'), ('Type2'), ('Type3');
GO
INSERT INTO [dbo].[Rule] ([Value1], [Value2], [Value3]) VALUES (1,1,1), (2,2,2), (3,3,3);
GO
INSERT INTO [dbo].[RuleType] ([RuleId], [TypeId]) VALUES (1,1), (1,2), (1,3), (2,2), (2,3);
GO
O procedimento armazenado a seguir retorna todas as regras e um valor agregado de seus tipos associados:
CREATE OR ALTER PROCEDURE SelectRules
AS
SELECT
R.[RuleId],
R.[Value1],
R.[Value2],
R.[Value3],
[AssoctiatedTypes] = STRING_AGG(RT.TypeId, ', ')
FROM
[dbo].[Rule] R
LEFT JOIN
[dbo].[RuleType] RT ON RT.RuleId = R.RuleId
GROUP BY
R.[RuleId],
R.[Value1],
R.[Value2],
R.[Value3]
GO
Pergunta:
Existe uma maneira de impor exclusividade na combinação Value1, Value2, Value3 e AssociatedTypes?
O que preciso evitar é a criação de uma nova regra com os mesmos valores (Valor1, Valor2 e Valor3) e os mesmos tipos associados de uma regra existente. A mesma aplicação deverá aplicar-se à atualização de uma regra existente.
Infelizmente, a resposta do @SQLpro não vai funcionar porque
STRING_AGG
não é suportada em visualizações indexadas.Em vez disso, você pode usar um gatilho para impor isso, armazenando o agregado
TypeId
novamenteRule
e adicionando umaUNIQUE
restrição a ele.Insira seus dados agregados e adicione uma restrição exclusiva
E a lógica do gatilho é a seguinte:
inserted
edeleted
e junte-as para obter alterações exclusivasRuleId
RuleType
RuleId
e obtenha todosTypeId
os valores.Rule
e atualize a coluna.banco de dados<> violino
Notas adicionais:
varchar(1600)
. Uma agregação maior falhará com um erro de truncamento.varbinary
algum tipo de algoritmo de compactação de bits. Qualquer coisa muito maior precisará de uma solução completamente diferente.NULL
os valores também são considerados únicos, portanto você não poderia ter váriosRule
s sem nenhumTypeId
. Se isso não for desejado, altere sua restrição para um índice exclusivo filtrado.SQLpro sugere uma boa resposta, mas a junção/agregação externa não permite isso, até onde eu sei.
Como alternativa, você pode:
Crie uma restrição exclusiva na regra nos valores 1,2,3 e ID da regra.
Crie uma restrição exclusiva em RuleType para que nenhum tipo de regra possa ter a mesma definição.
Acho que isso acontecerá desde que 2) seja tolerável.
Como a exclusividade não pode ser única quando os valores são NULL, você pode criar uma visualização indexada com um índice UNIQUE CLUSTERED como: