sql-server - tipos - restricciones de nombres que puede tener una base de datos.

SQL Restricción única en varias tablas

Question

Estoy tratando de crear una restricción única en múltiples tablas. He encontrado respuestas similares aquí, pero no captan el espíritu de lo que trato de hacer.

Como ejemplo, tengo tres tablas, t_Analog, t_Discrete, t_Message

CREATE TABLE t_Analog(
    [AppName] [nvarchar](20) NOT NULL,
    [ItemName] [nvarchar](32) NOT NULL,
    [Value] [float] NOT NULL,
    CONSTRAINT [uc_t_Analog] UNIQUE(AppName, ItemName)
)

CREATE TABLE t_Discrete(
    [AppName] [nvarchar](20) NOT NULL,
    [ItemName] [nvarchar](32) NOT NULL,
    [Value] [bit] NOT NULL,
    CONSTRAINT [uc_t_Discrete] UNIQUE(AppName, ItemName)
)

CREATE TABLE t_Message(
    [AppName] [nvarchar](20) NOT NULL,
    [ItemName] [nvarchar](32) NOT NULL,
    [Value] [nvarchar](256) NOT NULL,
    CONSTRAINT [uc_t_Message] UNIQUE(AppName, ItemName)
)

Mi objetivo es hacer que AppName y ItemName sean únicos en las 3 tablas. Por ejemplo, un nombre de elemento de Y en la aplicación X no puede existir en tablas análogas y discretas.

Tenga en cuenta que este ejemplo es inventado, los datos reales para cada Tipo son diferentes y lo suficientemente grandes como para hacer que la combinación de tablas y la adición de una columna de Tipo sea bastante desagradable.

Si tiene alguna sugerencia sobre enfoques para esto, ¡me encantaría escucharlos!

---- BEGIN EDIT 2012-04-26 13:28 CST ----

¡Gracias a todos por sus respuestas o comentarios!

Parece que puede haber una causa para modificar el esquema de esta base de datos, y eso está bien.

Combinar las tablas en una sola tabla no es realmente una opción viable, ya que hay en el orden de 30 columnas para cada tipo que no coinciden (lamentablemente, no es una opción modificar estas columnas). Esto podría llevar a que grandes secciones de columnas no se usen en cada fila, lo que parece una mala idea.

Agregar una 4ta mesa, como lo mencionan John Sikora y otros, puede ser una opción, pero me gustaría verificar esto primero.

Modificar el esquema para que sea:

CREATE TABLE t_AllItems(
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [itemType] [int] NOT NULL,
    [AppName] [nvarchar](20) NOT NULL,
    [ItemName] [nvarchar](32) NOT NULL,
    CONSTRAINT [pk_t_AllItems] PRIMARY KEY CLUSTERED ( [id] )
    CONSTRAINT [uc_t_AllItems] UNIQUE([id], [AppName], [ItemName])
) ON [PRIMARY]

CREATE TABLE t_Analog(
    [itemId] [bigint] NOT NULL,
    [Value] [float] NOT NULL,
    FOREIGN KEY (itemId) REFERENCES t_AllItems(id)
)

CREATE TABLE t_Discrete(
    [itemId] [bigint] NOT NULL,
    [Value] [bit] NOT NULL,
    FOREIGN KEY (itemId) REFERENCES t_AllItems(id)
)

CREATE TABLE t_Message(
    [itemId] [bigint] NOT NULL,
    [Value] [nvarchar](256) NOT NULL,
    FOREIGN KEY (itemId) REFERENCES t_AllItems(id)
)

Solo tengo una pregunta con respecto a este enfoque. ¿Esto exige unicidad en las sub tablas?

Por ejemplo, podría no existir un ''Artículo'' que tenga ''id'' 9 con tablas t_Analog que tenga ''itemId'' de 9 con ''valor'' de 9.3 y, al mismo tiempo, t_Message tenga ''itemId'' 9 ​​con ''Value'' de "foo"?

Puede que no entienda completamente este enfoque de mesa extra, pero no estoy en contra.

Por favor corrígeme si me equivoco en esto.

Answer 1

Agregue una 4ta tabla específicamente para estos valores que desea que sean únicos, luego vincule estas claves de esta tabla a las otras usando una relación de uno a muchos. Por ejemplo, tendrá la tabla única con una ID, AppName y ItemName para componer sus 3 columnas. Luego, haga que esta tabla se vincule con las demás.

Para saber cómo hacer esto aquí hay un buen ejemplo Crear una relación de uno a muchos utilizando SQL Server

EDITAR: Esto es lo que haría, pero teniendo en cuenta las necesidades de tu servidor, puedes cambiar lo que se necesita:

CREATE TABLE AllItems(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [itemType] [int] NOT NULL,
    [AppName] [nvarchar](20) NOT NULL,
    [ItemName] [nvarchar](32) NOT NULL,
    CONSTRAINT [pk_AllItems] PRIMARY KEY CLUSTERED ( [id] ASC )
) ON [PRIMARY]

CREATE TABLE Analog(
    [itemId] [int] NOT NULL,
    [Value] [float] NOT NULL
)

CREATE TABLE Discrete(
    [itemId] [int] NOT NULL,
    [Value] [bit] NOT NULL
)

CREATE TABLE Message(
    [itemId] [bigint] NOT NULL,
    [Value] [nvarchar](256) NOT NULL
)

ALTER TABLE [Analog] WITH CHECK 
    ADD CONSTRAINT [FK_Analog_AllItems] FOREIGN KEY([itemId])
REFERENCES [AllItems] ([id])
GO
ALTER TABLE [Analog] CHECK CONSTRAINT [FK_Analog_AllItems]
GO

ALTER TABLE [Discrete] WITH CHECK 
    ADD CONSTRAINT [FK_Discrete_AllItems] FOREIGN KEY([itemId])
REFERENCES [AllItems] ([id])
GO
ALTER TABLE [Discrete] CHECK CONSTRAINT [FK_Discrete_AllItems]
GO

ALTER TABLE [Message] WITH CHECK 
    ADD CONSTRAINT [FK_Message_AllItems] FOREIGN KEY([itemId])
REFERENCES [AllItems] ([id])
GO
ALTER TABLE [Message] CHECK CONSTRAINT [FK_Message_AllItems]
GO

Por lo que puedo decir, tu sintaxis está bien, simplemente la cambié a esta forma simplemente porque estoy más familiarizado con ella, pero cualquiera de las dos debería funcionar.

Answer 2

Esto sugeriría un problema de normalización / diseño de base de datos, específicamente, debería tener el nombre de la aplicación almacenado en una sola tabla (como una clave única), luego una segunda columna que denota la ID a la que está vinculada y quizás una tercera columna indicando tipo.

P.EJ:

AppName – PrimaryKey - unique
ID – Foreign Key of either Discrete, Analog or message
Type – SMALLINT representing Discrete, analog or message.

Answer 3

Si bien puede o no puede modificar su esquema como lo hacen otras respuestas, una vista indizada puede aplicar la restricción de la que está hablando:

CREATE VIEW v_Analog_Discrete_Message_UK WITH SCHEMABINDING AS
SELECT a.AppName, a.ItemName
FROM dbo.t_Analog a, dbo.t_Discrete b, dbo.t_Message c, dbo.Tally t
WHERE (a.AppName = b.AppName and a.ItemName = b.ItemName)
    OR (a.AppName = c.AppName and a.ItemName = c.ItemName)
    OR (b.AppName = c.AppName and b.ItemName = c.ItemName)
    AND t.N <= 2
GO
CREATE UNIQUE CLUSTERED INDEX IX_AppName_ItemName_UK
    ON v_Analog_Discrete_Message_UK (AppName, ItemName)
GO

Necesitará una tabla de "Tally" o números o tendrá que generar una sobre la marcha, estilo Celko:

-- Celko-style derived numbers table to 100k
select a.N + b.N * 10 + c.N * 100 + d.N * 1000 + e.N * 10000 + 1 as N
from (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) a
      , (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) b
      , (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) c
      , (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) d
      , (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) e
order by N

Answer 4

También podría crear una restricción que tenga un poco más de lógica y verifique las tres tablas.

Eche un vistazo here para ver un ejemplo de cómo hacer esto usando una función.

Answer 5

Un pensamiento podría ser combinar las tres tablas:

CREATE TABLE t_Generic(
[AppName] [nvarchar](20) NOT NULL,
[ItemName] [nvarchar](32) NOT NULL,
[Type] [nvarchar](32) NOT NULL,
[AnalogValue] [Float] NULL,
[DiscreteValue] [bit] NULL,
[MessageValue] [nvarchar](256) NULL,
CONSTRAINT [uc_t_Generic] UNIQUE(AppName, ItemName)
)

Su lógica de aplicación tendría que exigir que solo se rellenara un valor, y podría usar un campo Tipo para realizar un seguimiento de qué tipo es ese registro.

Answer 6

Usé en lugar de insertar y actualizar desencadenadores para resolver este problema como el siguiente:

CREATE TRIGGER tI_Analog ON t_Analog
INSTEAD OF INSERT
AS 
BEGIN
    SET NOCOUNT ON ;

    IF EXISTS (SELECT 1 FROM inserted AS I INNER JOIN t_Analog AS T
                   ON T.AppName = I.AppName AND T.ItemName = I.ItemName
               UNION ALL
               SELECT 1 FROM inserted AS I INNER JOIN t_Discrete AS T
                   ON T.AppName = I.AppName AND T.ItemName = I.ItemName
               UNION ALL
               SELECT 1 FROM inserted AS I INNER JOIN t_Message AS T
                   ON T.AppName = I.AppName AND T.ItemName = I.ItemName
              )
    BEGIN
        RAISERROR(''Duplicate key'', 16, 10) ;
    END
    ELSE
    BEGIN
        INSERT INTO t_Analog ( AppName, ItemName, Value )
        SELECT AppName, ItemName, Value FROM inserted ;
    END
END
GO

CREATE TRIGGER tU_Analog ON t_Analog
INSTEAD OF UPDATE
AS 
BEGIN
    SET NOCOUNT ON ;

    IF EXISTS (SELECT TOP(1) 1
                 FROM (SELECT T.AppName, T.ItemName, COUNT(*) AS numRecs
                         FROM
                            (SELECT I.AppName, I.ItemName
                               FROM inserted AS I INNER JOIN t_Analog AS T
                                 ON T.AppName = I.AppName AND T.ItemName = I.ItemName
                             UNION ALL
                             SELECT I.AppName, I.ItemName
                               FROM inserted AS I INNER JOIN t_Discrete AS T
                                 ON T.AppName = I.AppName AND T.ItemName = I.ItemName
                             UNION ALL
                             SELECT I.AppName, I.ItemName
                               FROM inserted AS I INNER JOIN t_Message AS T
                                 ON T.AppName = I.AppName AND T.ItemName = I.ItemName
                            ) AS T
                          GROUP BY T.AppName, T.ItemName
                        ) AS T
                WHERE T.numRecs > 1
              )
    BEGIN
        RAISERROR(''Duplicate key'', 16, 10) ;
    END
    ELSE
    BEGIN
        UPDATE T
           SET AppName = I.AppName
             , ItemName = I.ItemName
             , Value = I.Value
          FROM inserted AS I INNER JOIN t_Message AS T
            ON T.AppName = I.AppName AND T.ItemName = I.ItemName
        ;
    END
END
GO

Una advertencia con el uso en lugar de desencadenantes es cuando hay un campo de identidad involucrado. Este disparador evita que la cláusula OUTPUT del comando INSERT INTO y la variable @@ IDENTITY funcionen correctamente.