跨多个表的 SQL 唯一约束

2021-12-26 00:00:00 sql-server database-design unique-constraint

我正在尝试跨多个表创建唯一约束.我在这里找到了类似的答案，但它们并没有完全抓住我想要做的事情的精神.

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.

举个例子，我有三个表，t_Analog、t_Discrete、t_Message

As an example, I have three tables, 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 不能同时存在于模拟表和离散表中.

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.

请注意，这个例子是人为的，每种类型的实际数据是不同的，而且大到足以使组合表和添加类型列变得非常难看.

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.

如果您对此方法有任何建议，我很乐意听取他们的意见！

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

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

谢谢大家的回答！

看来这个数据库的架构可能有修改的原因，没问题.

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

将表合并为一个表并不是一个真正可行的选择，因为每种类型有大约 30 列不匹配(不幸的是，修改这些列不是一个选项).这可能会导致每一行中没有使用大部分列，这似乎是个坏主意.

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.

像 John Sikora 和其他人提到的那样，添加第 4 个表可能是一种选择，但我想先验证一下.

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

修改架构为:

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?

例如，是否不存在具有 'id' 9 且表 t_Analog 具有 'itemId' 为 9 且 'value' 为 9.3 且同时具有 'itemId' 9 的 'Item' 和 'foo"的值?

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

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

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

如果我错了，请纠正我.

Please correct me if I am wrong on this.

推荐答案

专门为您希望唯一的这些值添加第 4 个表，然后使用一对多关系将这些键从该表链接到其他键.例如，您将拥有一个包含 ID、AppName 和 ItemName 的唯一表来组成它的 3 列.然后将此表链接到其他表.

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.

关于如何做到这一点，这里是一个很好的例子使用 SQL Server 创建一对多关系

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

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

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.

相关文章