SQL跨多个表的唯一约束 [英] SQL Unique constraint across multiple 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不能存在于模拟和离散表格中。

请注意，此示例是有创意的，每个类型的实际数据是不同的和足够大的，使组合表和添加一个类型列非常丑陋。

如果你有任何建议的方法，我很乐意听到他们！ p>

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

感谢大家的答案！

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

将表组合到一个表中并不是一个可行的选择，因为对于每个不匹配的类型，30列的顺序（修改这些列是不幸的是，不是一个选项）。这可能会导致每一列的大部分列不被使用，这似乎是一个坏主意。

添加第4个表，如John Sikora等人可能会提到作为一个选择，但我想首先验证。

修改模式是：

  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）参考t_AllItems（id）
）
 
 CREATE TABLE t_Discrete（
 [itemId] [bigint] NOT NULL，
 [Value] [bit] NOT NULL，
 FOREIGN KEY（itemId）参考t_AllItems（id）
）
 
 CREATE TABLE t_Message（
 [itemId] [bigint] NOT NULL，
 [Value] [nvarchar]（256）NOT NULL，
 FOREIGN KEY（itemId）参考t_AllItems（id）
）
  

我对此方法只有一个问题。这是否在子表中执行唯一性？

例如，不存在具有'id'9'表'的表't_Analog''itemId'为9的'Item' value为9.3，同时t_Message的itemId9的值为foo？

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

如果我错了，请更正我。

解决方案

添加第四个表，专门为这些值要独特，然后将这些表从这个表中链接到其他使用一对多的关系。
例如，您将拥有一个ID，AppName和ItemName的唯一表，以组成其3列。然后将此表链接到其他人。

这里是一个很好的例子
使用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模拟（
 [itemId] [int] NOT NULL，
 [Value] [float] NOT NULL 
）
 
 CREATE TABLE离散（
 [itemId] [int] NOT NULL，
 [Value] [bit] NOT NULL 
）
 
 CREATE TABLE消息（
 [itemId] [bigint] NOT NULL，
 [Value] [nvarchar]（256）NOT NULL 
）
 
 ALTER TABLE [Analog] WITH CHECK ADD CONSTRAINT [FK_Analog_AllItems] FOREIGN KEY itemId]）
参考[AllItems]（[id]）
 GO 
 ALTER TABLE [模拟]检查约束[FK_Analog_AllItems] 
 GO 
 
 ALTER TABLE [Discrete] WITH CHECK ADD CONSTRAINT [FK_Discrete_AllItems] FOREIGN KEY（[itemId]）
参考[AllItems]（[id]）
 GO 
 ALTER TABLE [Discrete] CHECK CONSTRAINT [FK_Discrete_AllItems] 
 GO 
 
 ALTER TABLE [消息] WITH CHECK ADD CONSTRAINT [FK_Message_AllItems] FOREIGN KEY（[itemId]）
参考[AllItems]（[id]）
 GO 
 ALTER TABLE [消息] CHECK CONSTRAINT [FK_Message_AllItems] 
 GO 
  

可以告诉你的语法是否正确，我只是简单地改变它，因为我更熟悉它，但应该工作。

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

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.

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!

---- BEGIN EDIT 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.

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

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

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

Please correct me if I am wrong on this.

解决方案

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.

For how to do this here is a good example Create a one to many relationship using 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.

这篇关于SQL跨多个表的唯一约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！