SQL Server Unique Index across tables

Question

It's possible to create a unique index across tables, basically using a view and a unique index.

I have a problem though.

Given two (or three) tables.

Company
- Id
- Name

Brand
- Id
- CompanyId
- Name
- Code

Product
- Id
- BrandId
- Name
- Code

I want to ensure uniqueness that the combination of:

Company / Brand.Code

and

Company / Brand.Product/Code

are unique.

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

The creation of the view is successful.

CREATE UNIQUE CLUSTERED INDEX UIX_UniquePrefixCode
    ON TestView(CompanyId, Code)

This fails because of the UNION

How can I solve this scenario?

Basically Code for both Brand/Product cannot be duplicated within a company.

Notes:

Error that I get is:

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.

Notes 2:

When I'm using the sub query I get the following error:

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.

**Notes 3: **

So given the Brands:

From @spaghettidba's answer.

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 )

The expectation is, the Brand Code + Company or Product Code + Company is unique, if we expand the results out.

Company / Brand|Product Code
1 / 100 <-- Brand
1 / 400 <-- Brand
1 / 1   <-- Product
1 / 2   <-- Product
1 / 5   <-- Product

2 / 200 <-- Brand

3 / 300 <-- Brand
3 / 500 <-- Brand
3 / 3   <-- Product
3 / 301 <-- Brand

There's no duplicates. If we have a brand and product with the same 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)

The product belongs to a different Company, so we get

Company / Brand|Product Code
1 / 999 <-- Brand
2 / 999 <-- Product

This is unique.

But if you have 2 brands, and 1 product.

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)

This would produce

Company / Brand|Product Code
1 / 777 <-- Brand
1 / 888 <-- Brand
1 / 777 <-- Product

This would not be allowed.

Hope that makes sense.

Notes 4:

@spaghettidba's answer solved the cross-table problem, the 2nd issue was duplicates in the Brand table itself.

I've managed to solve this by creating a separate index on the brand table:

CREATE UNIQUE NONCLUSTERED INDEX UIX_UniquePrefixCode23
    ON Brand(CompanyId, Code)
    WHERE Code IS NOT NULL;

Revelation

Are you talking about Indexed view/materilized view – Cushy

@Cushy in SQL Server a Materialized View is called an Indexed View. – Revelation

Answer 1

I blogged about a similar solution back in 2011. You can find the post here: http://spaghettidba.com/2011/08/03/enforcing-complex-constraints-with-indexed-views/

Basically, you have to create a table that contains exactly two rows and you will use that table in CROSS JOIN to duplicate the rows that violate your business rules.

In your case, the indexed view is a bit harder to code because of the way you expressed the business rule. In fact, checking uniqueness on the UNIONed tables through an indexed view is not permitted, as you already have seen.

However, the constraint can be expressed in a different way: since the companyId is implied by the brand, you can avoid the UNION and simply use a JOIN between product and brand and check uniqueness by adding the JOIN predicate on the code itself.

You didn't provide some sample data, I hope you won't mind if I'll do it for you:

CREATE TABLE Company (
    Id int PRIMARY KEY,
    Name varchar(50)
)

CREATE TABLE Brand (
    Id int PRIMARY KEY,
    CompanyId int,
    Name varchar(50),
    Code int
)

CREATE TABLE Product (
    Id int PRIMARY KEY,
    BrandId int,
    Name varchar(50),
    Code int
)
GO

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 )

As far as I can tell, no rows violating the business rules are present yet.

Now we need the indexed view and the two rows table:

CREATE TABLE tworows (
    n int
)

INSERT INTO tworows values (1),(2)
GO

And here's the indexed view:

CREATE VIEW TestView
WITH SCHEMABINDING
AS
SELECT 1 AS one
FROM dbo.Brand b
INNER JOIN dbo.Product p
    ON p.BrandId = b.Id
    AND p.code = b.code
CROSS JOIN dbo.tworows AS t
GO

CREATE UNIQUE CLUSTERED INDEX IX_TestView ON dbo.TestView(one)

This update should break the business rules:

UPDATE product SET code = 300 WHERE code = 301

In fact you get an error:

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.TestView' with unique index 'IX_TestView'. The duplicate key value is (1).
The statement has been terminated.

Hope this helps.

Pablopabon

Looks like we both had this idea in 2011! I've used this in practice a few times now. Sometimes a bit of a pain as SQL Server checks all constraints at the end of the statement rather than transaction so sometimes need to make changes in the correct order to avoid violations. Especially a pain if ORMs are involved. – Syllabism

I think this only guarantees uniqueness within one product and its associated brands. But I can see how to fix it by just removing the join condition p.BrandId = b.Id. Do you agree? – Tonguelash

@Tonguelash I'm not sure, the requirements are quite confusing IMHO. Maybe Phill can answer that. – Pablopabon

@Pablopabon - I updated with some more notes. I can't try your solution until I get to work tomorrow but keen to try it!!!! Thanks a lot! – Revelation

Hmmm @Pablopabon it works great for preventing duplicate products but its allowing duplicate brand codes :( playing around trying to figure out how to prevent both. – Revelation

AH @Pablopabon - Added another note to my question, fixed the duplicates in Brand table just creating an index in there. – Revelation