Уникальное ограничение SQL для нескольких таблиц

Добавьте четвертую таблицу специально для тех значений, которые вы хотите сделать уникальными, а затем свяжите эти ключи из этой таблицы с другими, используя отношение «один ко многим». Например, у вас будет уникальная таблица с идентификатором, AppName и ItemName, чтобы составить ее 3 столбца. Затем свяжите эту таблицу с другими.

Как это сделать, вот хороший пример Создайте один для многие отношения с использованием SQL Server

EDIT: Я бы сделал так, но, учитывая потребности вашего сервера, вы можете изменить то, что необходимо:

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

Из того, что я могу сказать, ваш синтаксис в порядке, я просто изменил его на этот просто потому, что я более знаком с ним, но любой из них должен работать.

Хотя вы можете или не хотите изменять свою схему, как говорят другие ответы, индексированное представление может применить ограничение, о котором вы говорите:

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

Вам понадобится "Tally" или таблица чисел, либо вы должны создать ее иным образом. на лету, в стиле 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

Одна мысль может состоять в том, чтобы объединить три таблицы:

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

Логика вашего приложения должна обеспечивать заполнение только одного значения, и вы можете использовать поле Type, чтобы отслеживать, к какому типу относится эта запись.

Вы также можете создать ограничение, которое имеет немного больше логики и проверяет все три таблицы.

Посмотрите здесь пример того, как это сделать с помощью функции.

Это предполагает проблему нормализации/дизайна базы данных, в частности, вы должны иметь имя приложения, хранящееся в одной таблице отдельно (как уникальный/ключ), а затем второй столбец, обозначающий идентификатор того, с чем он связан, и, возможно, третий столбец с указанием типа.

EG:

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

Вместо триггеров вставки и обновления я использовал для решения этой проблемы следующее:

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

Одно предупреждение с использованием вместо триггеров — когда задействовано поле идентификации. Этот триггер предотвращает правильную работу предложения OUTPUT команды INSERT INTO и переменной @@IDENTITY.