default project code of 5650000. Every other project code must be greater
than that number and then must be distinct, but because we have many
projects pending that have not been assigned project codes yet there are
multiple default values of 5650000. Is there any way to apply a check
constraint to this? I recently had a problem where a user added a project
code that had already been used. What can I do to prevent this in the
future?
Thanks for any help
MikeThis is a multi-part message in MIME format.
--=_NextPart_000_00F6_01C3529D.605F7E80
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
You can use an indexed view to enforce the uniqueness for codes > =5650000:
create view dbo.MyView
as
select ProjectCode
from dbo.MyTable
where ProjectCode > 5650000
go
create unique clustered index idx on MyView (ProjectCode)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Mike" <Mike@.nospam.com> wrote in message =news:eIkPv3rUDHA.1688@.TK2MSFTNGP11.phx.gbl...
I have an integer column that is used for our project codes. We use a
default project code of 5650000. Every other project code must be =greater
than that number and then must be distinct, but because we have many
projects pending that have not been assigned project codes yet there are
multiple default values of 5650000. Is there any way to apply a check
constraint to this? I recently had a problem where a user added a =project
code that had already been used. What can I do to prevent this in the
future?
Thanks for any help
Mike
--=_NextPart_000_00F6_01C3529D.605F7E80
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
You can use an indexed view to enforce =the uniqueness for codes > 5650000:
create view =dbo.MyView
as
select ProjectCode
from dbo.MyTable
where ProjectCode > =5650000
go
create unique clustered index =idx on MyView (ProjectCode)
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Mike"
--=_NextPart_000_00F6_01C3529D.605F7E80--|||This is a multi-part message in MIME format.
--=_NextPart_000_001D_01C3529E.BB1064B0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
I am kind of new to this. I tried this and I got the message
Cannot create index on view 'myview' because the view is not schema =bound.
What does this mean?
Mike
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:eArxy7rUDHA.1916@.TK2MSFTNGP12.phx.gbl...
You can use an indexed view to enforce the uniqueness for codes > =5650000:
create view dbo.MyView
as
select ProjectCode
from dbo.MyTable
where ProjectCode > 5650000
go
create unique clustered index idx on MyView (ProjectCode)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Mike" <Mike@.nospam.com> wrote in message =news:eIkPv3rUDHA.1688@.TK2MSFTNGP11.phx.gbl...
I have an integer column that is used for our project codes. We use a
default project code of 5650000. Every other project code must be =greater
than that number and then must be distinct, but because we have many
projects pending that have not been assigned project codes yet there =are
multiple default values of 5650000. Is there any way to apply a check
constraint to this? I recently had a problem where a user added a =project
code that had already been used. What can I do to prevent this in the
future?
Thanks for any help
Mike
--=_NextPart_000_001D_01C3529E.BB1064B0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
I am kind of new to this. I tried =this and I got the message
Cannot create index on view 'myview' =because the view is not schema bound.
What does this mean?
Mike
"Tom Moreau"
You can use an indexed view to =enforce the uniqueness for codes > 5650000:
create view =dbo.MyView
as
select =ProjectCode
from dbo.MyTable
where ProjectCode > 5650000
go
create unique clustered index =idx on MyView (ProjectCode)
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Mike"
--=_NextPart_000_001D_01C3529E.BB1064B0--|||This is a multi-part message in MIME format.
--=_NextPart_000_002F_01C352AD.D241BFD0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Makes sense. Thanks for the help.
Mike
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:eYrWjDsUDHA.3152@.tk2msftngp13.phx.gbl...
Oops. Here's the revised code:
create view dbo.MyView
with schemabinding
as
select ProjectCode
from dbo.MyTable
where ProjectCode > 5650000
go
Schema binding ensures that any attempt to change an object referenced =by the view will fail.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Mike" <Mike@.nospam.com> wrote in message =news:u0GmDBsUDHA.1928@.TK2MSFTNGP12.phx.gbl...
I am kind of new to this. I tried this and I got the message
Cannot create index on view 'myview' because the view is not schema =bound.
What does this mean?
Mike
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:eArxy7rUDHA.1916@.TK2MSFTNGP12.phx.gbl...
You can use an indexed view to enforce the uniqueness for codes > =5650000:
create view dbo.MyView
as
select ProjectCode
from dbo.MyTable
where ProjectCode > 5650000
go
create unique clustered index idx on MyView (ProjectCode)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Mike" <Mike@.nospam.com> wrote in message =news:eIkPv3rUDHA.1688@.TK2MSFTNGP11.phx.gbl...
I have an integer column that is used for our project codes. We use =a
default project code of 5650000. Every other project code must be =greater
than that number and then must be distinct, but because we have many
projects pending that have not been assigned project codes yet there =are
multiple default values of 5650000. Is there any way to apply a =check
constraint to this? I recently had a problem where a user added a =project
code that had already been used. What can I do to prevent this in =the
future?
Thanks for any help
Mike
--=_NextPart_000_002F_01C352AD.D241BFD0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Makes sense. Thanks for the help.
Mike
"Tom Moreau"
Oops. Here's the revised code:
create view =dbo.MyView
with schemabinding
as
select =ProjectCode
from dbo.MyTable
where ProjectCode > 5650000
go
Schema binding ensures that any =attempt to change an object referenced by the view will fail.
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Mike"
I am kind of new to this. I =tried this and I got the message
Cannot create index on view 'myview' =because the view is not schema bound.
What does this mean?
Mike
"Tom Moreau"
You can use an indexed view to =enforce the uniqueness for codes > 5650000:
create view =dbo.MyView
as
select =ProjectCode
from =dbo.MyTable
where ProjectCode > 5650000
go
create unique clustered =index idx on MyView (ProjectCode)
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Mike"
--=_NextPart_000_002F_01C352AD.D241BFD0--
No comments:
Post a Comment