Indexed Views & Constraints

Is using indexed views to create constraints a good idea? If not, what
database design flaws usually lead to this sort of construct?Can you give an example of how you would use an indexed view to create a
constraint? I've heard of indexed views, but never heard of using an
indexed view to create constraints.
"Wes" <Wes@.discussions.microsoft.com> wrote in message
news:EA28B504-0098-4314-8014-8082AD6662B7@.microsoft.com...
> Is using indexed views to create constraints a good idea? If not, what
> database design flaws usually lead to this sort of construct?
>|||For a constraint on a view to be meaningful, the view would have to be
updatable. The restrictions on updatable views are that
-the columns in the view must be directly related to the columns in the base
tables (no aggregate functions)
-any update to the view must affect one one of the base tables involved at a
time.
So, if you wanted to create a primary key constraint using two columns in a
view that orginated it two different base tables, it would not act as a
constraint per se. Any insert or update you tried to perform on the view tha
t
modified both columns simultaneously would fail because it would violate the
second rule above. In other word, you would have the constaint, but you
wouldn't be able to use it in the way that you would like.
The index (as a result of the primary key constraint) would help with
SELECTs though.
A unique clustered index on a view also might not provide the constraint
enforcement you are looking for. Consider the following:
create table testa (aid int, aval char(1), PRIMARY KEY (aid))
go
create table testb (bid int, bval char(1), PRIMARY KEY (bid))
go
INSERT INTO testa VALUES (1, 'M')
INSERT INTO testa VALUES (2, 'A')
INSERT INTO testa VALUES (3, 'R')
INSERT INTO testa VALUES (4, 'K')
INSERT INTO testa VALUES (5, 'O')
INSERT INTO testb VALUES (1, 'W')
INSERT INTO testb VALUES (2, 'I')
INSERT INTO testb VALUES (3, 'L')
INSERT INTO testb VALUES (4, 'L')
INSERT INTO testb VALUES (5, 'Y')
CREATE VIEW dbo.testc WITH SCHEMABINDING AS
(SELECT a.aid, a.aval, b.bval from dbo.testa a inner join dbo.testb b on
a.aid=b.bid)
CREATE UNIQUE CLUSTERED INDEX PK_testc_aval_bval ON dbo.testc (aval,bval)
INSERT INTO testa VALUES (6,'Z')
--You would think this would fail
INSERT INTO testb VALUES (6,'Z')
--But it doesn't.
select * from testc
--last row will be (6,'Z','Z'), something that should have violated the uniq
ue
--constraint.
"Wes" wrote:

> Is using indexed views to create constraints a good idea? If not, what
> database design flaws usually lead to this sort of construct?
>|||the only thing I can think of right now is to create a unique index on
an indexed view. I cannot think of a real life situation when it would
be feasible to do so.
Note that for Oracle you can also create check constraints on
materialized views, which lets you enforse business rules like 'no more
than 15 employees report to a manager'. Yet in Oracle world that is a
very complex way, recommended to use only as the very last resort.|||also this might be relevant:
http://www.dbazine.com/oracle/or-articles/tropashko8|||> Can you give an example of how you would use an indexed view to create a
> constraint? I've heard of indexed views, but never heard of using an
To require all non-null values in a particular column to be unique but to
allow any number of nulls.|||I assume this would mean creating a unique index on view.Column1
and the view would be:
select XYZ from table
where Column1 is not null
The next question would be what is the alternative way to do this without a
view? I can think of a use defined function that checks for the existence
of the value in the table, or for null.
i.e. @.Column1 is null or not exists(select 1 from table where column1 =
@.column1)
Would such an aproach be worse for performance?
"Scott Morris" <bogus@.bogus.com> wrote in message
news:OXYynSGHGHA.3624@.TK2MSFTNGP09.phx.gbl...
> To require all non-null values in a particular column to be unique but to
> allow any number of nulls.
>|||>I assume this would mean creating a unique index on view.Column1
Correct.

> The next question would be what is the alternative way to do this without
> a
> view? I can think of a use defined function that checks for the existence
> of the value in the table, or for null.
You are assuming a single alternative. A trigger is another. I'm sure
there are others - perhaps involving some overly clever and obscure
technique.

> Would such an aproach be worse for performance?
Judging whether one approach is superior to another requires an evaluation
of the pros and cons of all alternatives and an evaluation of the impact of
each on the system in general. As usual, it is impossible to say that any
approach is superior to any other without delving into the specifics of the
system and its usage.|||Scott,
Thanks for the clarification.
"Scott Morris" <bogus@.bogus.com> wrote in message
news:ONQCLyGHGHA.3100@.tk2msftngp13.phx.gbl...
> Correct.
>
without
existence
> You are assuming a single alternative. A trigger is another. I'm sure
> there are others - perhaps involving some overly clever and obscure
> technique.
>
> Judging whether one approach is superior to another requires an evaluation
> of the pros and cons of all alternatives and an evaluation of the impact
of
> each on the system in general. As usual, it is impossible to say that any
> approach is superior to any other without delving into the specifics of
the
> system and its usage.
>|||We have a table that list a personid, relationtypeid (~CEO,CFO,…Employee),
and companyid.
These 3 items are unique per row but the relationtypeid and companyid should
also be unique when the relationtypeid is of a certain type. The thought i
s
to create a schema bound view with a unique clustered index on it. The view
would have the applicable filter based on the relationtypeid.
"Jim Underwood" wrote:

> Can you give an example of how you would use an indexed view to create a
> constraint? I've heard of indexed views, but never heard of using an
> indexed view to create constraints.
>
> "Wes" <Wes@.discussions.microsoft.com> wrote in message
> news:EA28B504-0098-4314-8014-8082AD6662B7@.microsoft.com...
>
>