SQL Server Unique Index across tables -

it's possible create unique index across tables, using view , unique index.

i have problem though.

given 2 (or three) tables.

company - id - name  brand - id - companyid - name - code  product - id - brandid - name - code 

i want ensure uniqueness combination of:

company / brand.code 

and

company / brand.product/code 

are unique.

create view testview schemabinding     select b.companyid, b.code     dbo.brand b      union      select b.companyid, p.code     dbo.product p          inner join dbo.brand b on p.brandid = b.brandid 

the creation of view successful.

create unique clustered index uix_uniqueprefixcode     on testview(companyid, code) 

this fails because of union

how can solve scenario?

basically code both brand/product cannot duplicated within company.

notes:

error is:

msg 10116, level 16, state 1, line 3 cannot create index on view 'xxxx.dbo.testview' because contains 1 or more union, intersect, or except operators. consider creating separate indexed view each query input union, intersect, or except operators of original view.

notes 2:

when i'm using sub query following error:

msg 10109, level 16, state 1, line 3 cannot create index on view "xxxx.dbo.testview" because references derived table "a" (defined select statement in clause). consider removing reference derived table or not indexing view.

**notes 3: **

so given brands:

from @spaghettidba's answer.

insert 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 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, brand code + company or product code + company unique, if expand 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 have brand , product same code.

insert brand (     id,     companyid,     name,     code ) values  (6, 1, 'brand 6', 999)  insert product (     id,     brandid,     name,     code ) values (1006, 2, 'product 1006', 999) 

the product belongs different company, get

company / brand|product code 1 / 999 <-- brand 2 / 999 <-- product 

this unique.

but if have 2 brands, , 1 product.

insert brand (     id,     companyid,     name,     code ) values  (7, 1, 'brand 7', 777) (8, 1, 'brand 8', 888)  insert product (     id,     brandid,     name,     code ) values (1007, 8, 'product 1008', 777) 

this produce

company / brand|product code 1 / 777 <-- brand 1 / 888 <-- brand 1 / 777 <-- product 

this not allowed.

hope makes sense.

notes 4:

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

i've managed solve creating separate index on brand table:

create unique nonclustered index uix_uniqueprefixcode23     on brand(companyid, code)     code not null; 

i blogged similar solution in 2011. can find post here: http://spaghettidba.com/2011/08/03/enforcing-complex-constraints-with-indexed-views/

basically, have create table contains 2 rows , use table in cross join duplicate rows violate business rules.

in case, indexed view bit harder code because of way expressed business rule. in fact, checking uniqueness on unioned tables through indexed view not permitted, have seen.

however, constraint can expressed in different way: since companyid implied brand, can avoid union , use join between product , brand , check uniqueness adding join predicate on code itself.

you didn't provide sample data, hope won't mind if i'll 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 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 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 can tell, no rows violating business rules present yet.

now need indexed view , 2 rows table:

create table tworows (     n int )  insert tworows values (1),(2) go 

and here's indexed view:

create view testview schemabinding select 1 one dbo.brand b inner join dbo.product p     on p.brandid = b.id     , p.code = b.code cross join dbo.tworows t go  create unique clustered index ix_testview on dbo.testview(one) 

this update should break business rules:

update product set code = 300 code = 301 

in fact error:

msg 2601, level 14, state 1, line 1 cannot insert duplicate key row in object 'dbo.testview' unique index 'ix_testview'. duplicate key value (1). statement has been terminated. 

hope helps.