title: SQL Server Unique Index across tables
body: <p>It's possible to create a unique index across tables, basically using a view and a unique index.</p>
  
  <p>I have a problem though. </p>
  
  <p>Given two (or three) tables. </p>
  
  <pre><code>Company
  - Id
  - Name
  
  Brand
  - Id
  - CompanyId
  - Name
  - Code
  
  Product
  - Id
  - BrandId
  - Name
  - Code
  </code></pre>
  
  <p>I want to ensure uniqueness that the combination of:</p>
  
  <pre><code>Company / Brand.Code
  </code></pre>
  
  <p>and</p>
  
  <pre><code>Company / Brand.Product/Code
  </code></pre>
  
  <p>are unique.</p>
  
  <pre><code>CREATE VIEW TestView
  WITH SCHEMABINDING
  AS
      SELECT b.CompanyId, b.Code
      FROM dbo.Brand b
  
      UNION ALL
  
      SELECT b.CompanyId, p.Code
      FROM dbo.Product p
           INNER JOIN dbo.Brand b ON p.BrandId = b.BrandId
  </code></pre>
  
  <p>The creation of the view is successful. </p>
  
  <pre><code>CREATE UNIQUE CLUSTERED INDEX UIX_UniquePrefixCode
      ON TestView(CompanyId, Code)
  </code></pre>
  
  <p>This fails because of the <code>UNION</code></p>
  
  <p>How can I solve this scenario?</p>
  
  <p>Basically Code for both <code>Brand/Product</code> cannot be duplicated within a company. </p>
  
  <p><strong>Notes:</strong></p>
  
  <p>Error that I get is:</p>
  
  <blockquote>
    <p>Msg 10116, Level 16, State 1, Line 3 Cannot create index on view
    'XXXX.dbo.TestView' because it contains one or more UNION, INTERSECT,
    or EXCEPT operators. Consider creating a separate indexed view for
    each query that is an input to the UNION, INTERSECT, or EXCEPT
    operators of the original view.</p>
  </blockquote>
  
  <p><strong>Notes 2:</strong></p>
  
  <p>When I'm using the sub query I get the following error: </p>
  
  <blockquote>
    <p>Msg 10109, Level 16, State 1, Line 3 Cannot create index on view
    "XXXX.dbo.TestView" because it references derived table "a"
    (defined by SELECT statement in FROM clause). Consider removing the
    reference to the derived table or not indexing the view.</p>
  </blockquote>
  
  <p>**Notes 3: **</p>
  
  <p>So given the Brands:</p>
  
  <p>From @spaghettidba's answer. </p>
  
  <pre><code>INSERT INTO Brand
  (
      Id,
      CompanyId,
      Name,
      Code
  )
  VALUES 
  (1, 1, 'Brand 1', 100 ),
  (2, 2, 'Brand 2', 200 ),
  (3, 3, 'Brand 3', 300 ),
  (4, 1, 'Brand 4', 400 ),
  (5, 3, 'Brand 5', 500 )
  
  INSERT INTO Product
  (
      Id,
      BrandId,
      Name,
      Code
  )
  VALUES
  (1001, 1, 'Product 1001', 1 ),
  (1002, 1, 'Product 1002', 2 ),
  (1003, 3, 'Product 1003', 3 ),
  (1004, 3, 'Product 1004', 301 ),
  (1005, 4, 'Product 1005', 5 )
  </code></pre>
  
  <p>The expectation is, the <code>Brand Code + Company</code> or <code>Product Code + Company</code> is unique, if we expand the results out.</p>
  
  <pre><code>Company / Brand|Product Code
  1 / 100 &lt;-- Brand
  1 / 400 &lt;-- Brand
  1 / 1   &lt;-- Product
  1 / 2   &lt;-- Product
  1 / 5   &lt;-- Product
  
  2 / 200 &lt;-- Brand
  
  3 / 300 &lt;-- Brand
  3 / 500 &lt;-- Brand
  3 / 3   &lt;-- Product
  3 / 301 &lt;-- Brand
  </code></pre>
  
  <p>There's no duplicates. If we have a brand and product with the same code. </p>
  
  <pre><code>INSERT INTO Brand
  (
      Id,
      CompanyId,
      Name,
      Code
  )
  VALUES 
  (6, 1, 'Brand 6', 999)
  
  INSERT INTO Product
  (
      Id,
      BrandId,
      Name,
      Code
  )
  VALUES
  (1006, 2, 'Product 1006', 999)
  </code></pre>
  
  <p>The product belongs to a different Company, so we get</p>
  
  <pre><code>Company / Brand|Product Code
  1 / 999 &lt;-- Brand
  2 / 999 &lt;-- Product
  </code></pre>
  
  <p>This is unique.</p>
  
  <p>But if you have 2 brands, and 1 product. </p>
  
  <pre><code>INSERT INTO Brand
  (
      Id,
      CompanyId,
      Name,
      Code
  )
  VALUES 
  (7, 1, 'Brand 7', 777)
  (8, 1, 'Brand 8', 888)
  
  INSERT INTO Product
  (
      Id,
      BrandId,
      Name,
      Code
  )
  VALUES
  (1007, 8, 'Product 1008', 777)
  </code></pre>
  
  <p>This would produce</p>
  
  <pre><code>Company / Brand|Product Code
  1 / 777 &lt;-- Brand
  1 / 888 &lt;-- Brand
  1 / 777 &lt;-- Product
  </code></pre>
  
  <p>This would not be allowed.</p>
  
  <p>Hope that makes sense.</p>
  
  <p><strong>Notes 4:</strong></p>
  
  <p>@spaghettidba's answer solved the cross-table problem, the 2nd issue was duplicates in the Brand table itself. </p>
  
  <p>I've managed to solve this by creating a separate index on the brand table:</p>
  
  <pre><code>CREATE UNIQUE NONCLUSTERED INDEX UIX_UniquePrefixCode23
      ON Brand(CompanyId, Code)
      WHERE Code IS NOT NULL;
  </code></pre>