跨多个表的SQL唯一约束

I am trying to create a unique constraint across multiple tables. I have found similar questions answered here but they don't quite capture the spirit of what I am trying to do.

我试图在多个表之间创建一个唯一约束。我在这里找到了类似的问题，但是他们并没有完全捕捉到我想要做的精神。

As an example, I have three tables, t_Analog, t_Discrete, t_Message

举个例子，我有三个表，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)
)

My goal is to make AppName and ItemName unique across all 3 tables. For instance, an item name of Y in application X cannot exist in both analog and discrete tables.

我的目标是在所有3个表中使AppName和ItemName唯一。例如，应用程序X中的项目名称Y不能同时存在于模拟和离散表中。

Please note that this example is contrived, the actual data for each Type is different and large enough to make combining tables and adding a Type column pretty ugly.

请注意，这个例子是设计的，每个Type的实际数据是不同的，大到足以组合表并添加一个非常难看的Type列。

If you have any suggestions on approaches to this, I would love to hear them!

如果您对此方法有任何建议，我很乐意听到它们！

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

----开始编辑2012-04-26 13:28 CST ----

Thank you all for your answers!

谢谢大家的答案！

It seems there may be cause to modify the schema of this database, and that is fine.

似乎可能有理由修改此数据库的架构，这很好。

Combining the tables into a single table is not really a viable option as there are on the order of 30 columns for each type that do not match (modifying these columns is, unfortunately, not an option). This could lead to large sections of columns not being used in each row, which seems like a bad idea.

将表组合到单个表中实际上并不是一个可行的选项，因为每个类型的列数大约为30列（不幸的是，修改这些列不是一个选项）。这可能导致每行中没有使用大部分列，这似乎是一个坏主意。

Adding a 4th table, like John Sikora and others mention, may be an option but I would like to verify this first.

添加第4个表，如John Sikora和其他人提到的，可能是一个选项，但我想首先验证这一点。

Modifying Schema to be:

修改架构为：

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

I only have one question regarding this approach. Does this enforce uniqueness across the sub tables?

关于这种方法，我只有一个问题。这是否会强制子表中的唯一性？

For instance, could there not exist an 'Item' that has 'id' 9 with tables t_Analog having 'itemId' of 9 with 'value' of 9.3 and, at the same time, t_Message have 'itemId' 9 with 'Value' of "foo"?

例如，是否存在“项目”，其中“id”9表格为t_Analog，其中“itemId”为9，“value”为9.3，同时t_Message的“itemId”为9，其中“value”为“富”？

I may not fully understand this extra table approach but I am not against it.

我可能不完全理解这种额外的表格方法，但我并不反对。

Please correct me if I am wrong on this.

如果我错了，请纠正我。

6 个解决方案

#1

Add a 4th table specifically for these values you want to be unique then link these keys from this table into the others using a one to many relationship. For example you will have the unique table with an ID, AppName and ItemName to make up its 3 columns. Then have this table link to the others.

添加第4个表专门针对您想要唯一的值，然后使用一对多关系将这些键从此表链接到其他键。例如，您将拥有一个唯一的表，其中包含ID，AppName和ItemName以构成其3列。然后将此表链接到其他人。

For how to do this here is a good example Create a one to many relationship using SQL Server

有关如何执行此操作，这是一个很好的示例使用SQL Server创建一对多关系

EDIT: This is what I would do but considering your server needs you can change what is needed:

编辑：这是我会做的，但考虑到您的服务器需求，您可以更改所需的内容：

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

From what I can tell your syntax is fine, I simply changed it to this way simply because I am more familiar with it but either should work.

从我可以告诉你的语法很好，我只是简单地改变它，因为我更熟悉它，但要么应该工作。

#2

While you may or may not want to alter your schema like other answers say, an indexed view can apply the constraint that you're talking about:

虽然您可能会或可能不想像其他答案那样改变您的架构，但索引视图可以应用您正在讨论的约束：

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

You will need a "Tally" or numbers table or have to otherwise generate one on the fly, Celko-style:

您将需要一个“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

#3

One thought might be to combine the three tables:

一种想法可能是将三个表组合在一起：

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

Your application logic would have to enforce that only one value was populated, and you could use a Type field to keep track of what type that record is.

您的应用程序逻辑必须强制只填充一个值，并且您可以使用“类型”字段来跟踪该记录的类型。

#4

You could also create a constraint that has a bit more logic and checks all three tables.

您还可以创建一个具有更多逻辑的约束并检查所有三个表。

Take a look here for an example of how to do this using a function.

看一下如何使用函数执行此操作的示例。

#5

This would suggest a normalisation / database design issue, specifically you should have the appname stored in one table on it’s own (as a unique / key whatever) then a 2nd column denoting the ID of what it is linked to, and perhaps a 3rd column indicating type.

这会建议规范化/数据库设计问题，特别是你应该将appname存储在一个表中（作为一个唯一/密钥），然后是第二列，表示它链接到的ID，也许是第3列指示类型。

EG:

例如：

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

#6

I used instead of insert and update triggers to resolve this issue like the following:

我使用而不是插入和更新触发器来解决此问题，如下所示：

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

One warning with using instead of triggers is when there is an identity field involved. This trigger prevents the OUTPUT clause of the INSERT INTO command and the @@IDENTITY variable from working properly.

使用而不是触发器的一个警告是涉及到身份字段时。此触发器可防止INSERT INTO命令的OUTPUT子句和@@ IDENTITY变量正常工作。