constraints on views

Hi.
At first let me present the problem:
T(a int, b int, c bit)
I would like to see to it that (a,b) is unique where c = 1. The easiest way
to accomplish this would be creating a view like this:
create view V as select a,b from T where c = 1
And then putting a unique constraint on (a,b) in V.
I am open to other solutions as well, of course.
Thx,
AgostonCreate an indexed view with a unique index on (a,b).
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:570905D0-1D39-4CAF-995C-2DD91A2EC6B4@.microsoft.com...
> Create an indexed view with a unique index on (a,b).
Does this feature exists in SQL Server 7? Unfortunately I'm forced to work
with that, and I remember reading about this feature not being present in
SQL 7.
Thx,
Agoston

> --
> David Portas
> SQL Server MVP
> --
>|||You are correct, indexed views are not available in SQL 7. Probably the best
thing you can do in SQL 7 is to create a trigger that enforces this for you.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:evpzFRF2EHA.3408@.tk2msftngp13.phx.gbl...
> Hi.
> At first let me present the problem:
> T(a int, b int, c bit)
> I would like to see to it that (a,b) is unique where c = 1. The easiest
way
> to accomplish this would be creating a view like this:
> create view V as select a,b from T where c = 1
> And then putting a unique constraint on (a,b) in V.
> I am open to other solutions as well, of course.
> Thx,
> Agoston
>|||It is not clear from your message if you want to use regular view or
indexed view, but in any case you can only have constraint on indexed
view.
Another way that you can use is to work with stored procedure.
Instead of giving the users permissions to work directly with the
table, you can grant them execute permissions on a stored procedure
that inserts the data to the table (or rejects the data) according to
your criteria.
Another way is to use trigger or instead of trigger (instead of
trigger would be better).
My favorite of all of those is the stored procedure.
Adi|||You could also consider a UNIQUE CONSTRAINT on the combination of a, b, and
c. What is the rule when c <> 1?
Sincerely,
Anthony Thomas
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:evpzFRF2EHA.3408@.tk2msftngp13.phx.gbl...
Hi.
At first let me present the problem:
T(a int, b int, c bit)
I would like to see to it that (a,b) is unique where c = 1. The easiest way
to accomplish this would be creating a view like this:
create view V as select a,b from T where c = 1
And then putting a unique constraint on (a,b) in V.
I am open to other solutions as well, of course.
Thx,
Agoston