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

Ответ 1

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

Как сделать это здесь - хороший пример Создайте отношения "один ко многим" с помощью 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

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

Ответ 2

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

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-style:

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

Ответ 3

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

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

Ответ 4

Можно подумать о том, чтобы объединить три таблицы:

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

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

Ответ 5

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

EG:

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

Ответ 6

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

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.