title: Unique index and treating multiple values as one SQL
body: <p>I have a table like this:</p>
  
  <pre><code>CREATE TABLE [dbo].[BigTable]
  (
      [BigTableId] [int] IDENTITY(1,1) NOT NULL,
      [MuchText] [nvarchar](10) NOT NULL,
      [Type] [smallint] NOT NULL
      (...)
  )
  </code></pre>
  
  <p>The column <code>MuchText</code> can store any text value and column <code>Type</code> can store numbers 1, 2, 3, 4.</p>
  
  <p>I would like to create unique index, or something that will give me the similar results, on <code>MuchText</code> and <code>Type</code> columns (so the combination of both values would be forced to be unique).</p>
  
  <p>But here's the problem - I would like to treat some values in <code>Type</code> column as one. Let me explain how it should work:</p>
  
  <ol>
  <li><p>It is forbidden to insert record with values <code>Type = 1</code> and <code>MuchText = 'XXX'</code> if the record with values <code>Type = 1</code> and <code>MuchText = 'XXX'</code> already exists</p></li>
  <li><p>It is forbidden to insert record with values <code>Type = 1</code> and <code>MuchText = 'XXX'</code> if the record with values <code>Type = 2</code> and <code>MuchText = 'XXX'</code> already exists</p></li>
  <li><p>It is forbidden to insert record with values <code>Type = 1</code> and <code>MuchText = 'XXX'</code> if the record with values <code>Type = 3</code> and <code>MuchText = 'XXX'</code> already exists</p>
  
  <p>etc. etc. for types 1/2/3, but for Type = 4 it should behave like a classic unique index:</p></li>
  <li><p>For <code>Type = 4</code>, it is forbidden to insert record with values <code>Type = 4</code> and <code>MuchText = 'XXX'</code> if the record with values <code>Type = 4</code> and <code>MuchText = 'XXX'</code> already exists </p></li>
  </ol>
  
  <p>So this should not be possible:</p>
  
  <ol>
  <li>Type = 1, MuchText = 'XXX'</li>
  <li>Type = 2, MuchText = 'XXX' (not allowed, because there is already a record with Type = 1 and the same MuchText value).</li>
  </ol>
  
  <p>But this is OK:</p>
  
  <ol>
  <li>Type = 1, MuchText = 'XXX'</li>
  <li>Type = 4, MuchText = 'XXX'</li>
  </ol>