跨多个表的SQL唯一约束条件

33

我正在尝试在多个表之间创建一个唯一约束。我在这里找到了类似的问题,但它们并没有完全捕捉到我要做的事情的精髓。

例如,我有三个表,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)
)

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

请注意,这个例子是人为的,每种类型的实际数据都不同且足够大,以至于将表合并并添加类型列相当麻烦。

如果您有任何关于此方法的建议,我很乐意听取!

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

谢谢大家的答复!

看起来可能需要修改数据库的架构,这没问题。

将表合并到单个表中并不是一个可行的选项,因为每种类型都有大约30个不匹配的列(不幸的是,修改这些列不是一个选项)。 这可能导致每行中存在大量未使用的列,这似乎是一个糟糕的想法。

像John Sikora和其他人所提到的添加第4个表可能是一个选项,但我想先验证一下。

修改架构如下:

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

关于这种方法,我只有一个问题。这是否在子表中强制执行唯一性?

例如,是否可能存在一个名为“Item”的项目,其具有'id' 9,其中t_Analog具有'itemId'为9且'value'为9.3,同时t_Message也具有'itemId' 9和'value'为"foo"?

我可能没有完全理解这种额外的表格方法,但我不反对它。

如果我理解有误,请纠正我。


3
你的方向是正确的,但完整性约束条件不够好。例如,ID号码100可能会出现在每个表中。为了更严格地使用项目类型,请参见这个答案这个答案 - Mike Sherrill 'Cat Recall'
这不是一个限制,但您可以使用CREATE SEQUENCE来获取一个唯一的数字放入每个记录中:https://learn.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15 我认为此功能是在2012版本中添加的。 - John Gilmer
6个回答

17
添加第四张表,专门用于存储需要唯一的值,并使用一对多关系将该表中的键链接到其他表中。例如,您将具有唯一表格,其中包含ID、AppName和ItemName三列。然后将这个表格链接到其他表格中。
关于如何实现,请参考以下示例: 在 SQL Server 中创建一对多关系 编辑: 这是我会做的,但考虑到服务器需求,您可以更改需要的内容:
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

根据我所知道的,您的语法是正确的。我只是将它简单地更改成这种方式,因为我更熟悉这种方式,但两种方式都可以工作。


John,我编辑了我的帖子,添加了我认为你在谈论的模式。你能否验证一下,以确保我理解了你的建议?谢谢 :) - CoryC
如果您查看我的编辑,您会看到我习惯使用的内容,但是您的语法对我来说很好。 - John Sykor
10
你的方向是正确的,但这还不够好。问题在于没有限制防止ID号码100出现在这些表中的每一个。为了更严格地使用该项目类型,请参见 这个答案这个答案 - Mike Sherrill 'Cat Recall'
我个人会让查询语句弥补不足,而不是创建如此深入的表。例如,一旦 ID 在我们的主表中被使用,它就不能再被其他两个表使用。因此,我会在第一个表上添加一个标志,表示它正在使用中,如果检查了该标志,则不再可访问。不过也请看看 catcall 的回答,我对它不是很熟悉。 - John Sykor
Catcall,你的回答正是我所需要的。谢谢John和Cat的帮助! - CoryC

16

虽然你可能不想像其他答案所说的那样更改模式,但索引视图可以应用你所讨论的约束:

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

4
谢谢,我认为这是最优雅的解决方案。 - spinalfrontier
1
我同意 - 这是最优雅的解决方案。然而,你并不需要一个完整的具有10万行的计数表,只需要两行即可。因为你已经有了 WHERE ... t.N <= 2。我有一个名为 chk.TwoRows 的表格,专门用于这个目的,其中仅包含值1和2。 - Reversed Engineer
在这种情况下,幸运的是,就唯一约束而言,MS SQL将NULL视为一个值(当然这是错误的),因此即使在表b和c之间重复的值的情况下,它也能正常工作,此时a.AppName、a.ItemName都是NULL。在这里两个错误变成了正确。 - Reversed Engineer
P L U S _ O N E 无论如何 - 这确实有所帮助。 - Reversed Engineer
1
改进的Tally表生成: -- Celko风格的派生数字表达到100k SELECT a.N + b.N * 10 + c.N * 100 + d.N * 1000 + e.N * 10000 + 1 as N FROM (SELECT * FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS t(N)) AS a , (SELECT * FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS t(N)) AS b , (SELECT * FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS t(N)) AS c , (SELECT * FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS t(N)) AS d , (SELECT * FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS t(N)) AS e ORDER BY N - Dima
显示剩余2条评论

0
这表明存在规范化/数据库设计问题,具体来说,您应该将应用程序名称单独存储在一个表中(作为唯一/键),然后有第二列表示其链接到的ID,可能还有第三列表示类型。
例如:
AppName – PrimaryKey - unique
ID – Foreign Key of either Discrete, Analog or message
Type – SMALLINT representing Discrete, analog or message.

0

一个想法可能是将这三个表合并:

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

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


0

您还可以创建一个约束,其中包含更多的逻辑并检查所有三个表。

请参阅此处,了解如何使用函数执行此操作的示例。


Zimdanen,你能提供一个例子来说明这个约束如何针对多个表进行检查吗? - CoryC
这是一个使用函数的选项:https://dev59.com/UXE85IYBdhLWcg3w1HOF - zimdanen
zimdanen,看起来非常有前途,我可能会采用这种方法。 - CoryC
3
虽然这个链接可能回答了问题,但最好在这里包含答案的关键部分并提供链接以供参考。如果链接的页面发生更改,只有链接的回答可能会变得无效。- 来自审核 - ice1000
@ice1000:这适用于链接到 Stack Overflow 吗? - zimdanen

0
我使用了触发器来解决这个问题,而不是使用插入和更新触发器,具体如下:
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

使用触发器而不是的一个警告是当涉及到标识字段时。这个触发器会阻止INSERT INTO命令的OUTPUT子句和@@IDENTITY变量正常工作。

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文,
原文链接