CODEWITHSUNDEEP
sqlsql-server
Andrzej
Andrzej

Reputation: 858

Unique index and treating multiple values as one SQL

I have a table like this:

CREATE TABLE [dbo].[BigTable]
(
    [BigTableId] [int] IDENTITY(1,1) NOT NULL,
    [MuchText] [nvarchar](10) NOT NULL,
    [Type] [smallint] NOT NULL
    (...)
)

The column MuchText can store any text value and column Type can store numbers 1, 2, 3, 4.

I would like to create unique index, or something that will give me the similar results, on MuchText and Type columns (so the combination of both values would be forced to be unique).

But here's the problem - I would like to treat some values in Type column as one. Let me explain how it should work:

  1. It is forbidden to insert record with values Type = 1 and MuchText = 'XXX' if the record with values Type = 1 and MuchText = 'XXX' already exists

  2. It is forbidden to insert record with values Type = 1 and MuchText = 'XXX' if the record with values Type = 2 and MuchText = 'XXX' already exists

  3. It is forbidden to insert record with values Type = 1 and MuchText = 'XXX' if the record with values Type = 3 and MuchText = 'XXX' already exists

    etc. etc. for types 1/2/3, but for Type = 4 it should behave like a classic unique index:

  4. For Type = 4, it is forbidden to insert record with values Type = 4 and MuchText = 'XXX' if the record with values Type = 4 and MuchText = 'XXX' already exists

So this should not be possible:

  1. Type = 1, MuchText = 'XXX'
  2. Type = 2, MuchText = 'XXX' (not allowed, because there is already a record with Type = 1 and the same MuchText value).

But this is OK:

  1. Type = 1, MuchText = 'XXX'
  2. Type = 4, MuchText = 'XXX'

Upvotes: 4

Views: 225

Answers (2)

Martin Smith
Martin Smith

Reputation: 453212

You can add a computed column that has the same value for all types that should be grouped together (Grp below) and include that in the constraint.

CREATE TABLE [dbo].[BigTable](
    [BigTableId] [int] IDENTITY(1,1) NOT NULL,
    [MuchText] [nvarchar](10) NOT NULL,
    [Type] [smallint] NOT NULL,
    Grp AS CASE WHEN [Type] IN (1,2,3) THEN 1 ELSE 2 END,
    UNIQUE(Grp, MuchText)
    )

Upvotes: 6

devio
devio

Reputation: 37215

You could use Filtered Indexes using CREATE INDEX ... WHERE (cond).

CREATE UNIQUE INDEX UK_BigTable123 ON BigTable (MuchText)
WHERE Type IN (1, 2, 3)

CREATE UNIQUE INDEX UK_BigTable4 ON BigTable (MuchText)
WHERE Type = 4

Upvotes: 3

Related Questions