SQL Unique constraint across multiple tables

Question

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.

Chokedamp

You're on the right track, but the integrity constraints aren't good enough. For example, ID number 100 might appear in every table. For tighter use of the item type, see this answer and this answer. – Eiderdown

This isn't a constraint, but you can use CREATE SEQUENCE to get a unique number to put in each record: learn.microsoft.com/en-us/sql/t-sql/statements/… I think this feature was added in version 2012. – Xerophthalmia

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

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.

Abstriction

John, I edited my post to add the schema I believe you are talking about. Could you please verify to make sure I understand your suggestion? Thanks :) – Chokedamp

if you look at my edit you see what I am used to but your syntax looks good to me – Abstriction

You're on the right track, but this isn't quite good enough. The problem is that there's no constraint to prevent ID number 100 from appearing in every one of those tables. For tighter use of the item type, see this answer and this answer. – Eiderdown

I presonally would just make the queries make up for in instead of creating such in-depth tables. For instance. Once an ID is used in our main table, it no longer is usable by our other 2 tables. So essentially I would add a flag on the first table saying it is in use and if that flag is checked it is no longer accessible. Tho check out catcall's Answer as well, I am just not familiar with it. – Abstriction

Catcall, your answers were exactly what I needed. Thank you John and Cat for your help! – Chokedamp

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

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

Trio

I agree - this is the most elegant solution. However, you don't need a full tally table with 100,000 rows, just two rows. You have WHERE ... t.N <= 2 anyways. I have a table called chk.TwoRows for that purpose, with just the values 1 and 2. – Prurigo

Fortunately in this case, MS SQL views NULL as a value as far as unique constraints go (which of course is wrong), so it works even for values duplicated between tables b and c, in which case a.AppName, a.ItemName are both NULL. Two wrongs make a right here – Prurigo

P L U S _ O N E anyways - this has really helped – Prurigo

An improved Tally table generation: -- 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 * 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 – Singe

this is great and super flexible – Complacency

re: "tally tables": SQL Server 2022 adds GENERATE_SERIES( start, [stop, [step]] ): learn.microsoft.com/en-us/sql/t-sql/functions/… – Ecclesiastical

Answer 3

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.

Cordelia

Answer 4

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.

EG:

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

Preposterous

Answer 5

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.

Kepi

Answer 6

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.

Boyfriend

Zimdanen, could you provide an example of how this constraint would check against multiple tables? – Chokedamp

Here's one option with a function: #2588572 – Boyfriend

zimdanen, that looks very promising, I may take that approach. – Chokedamp

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - From Review – Jonathanjonathon

@ice1000: Does that apply for links to SO? – Boyfriend