Can I use a 'Conditional CHECK' constraint ?

Hi SQL Gurus,
Using Sql2000, I have a table to maintain stock availibility, the
requirement is qty_in_orders must be <= qty_on_hand, so I use CHECK
constraint like below :
CREATE TABLE StockAvailibility
(prod_code CHAR(5) NOT NULL PRIMARY KEY,
qty_on_hand INTEGER NOT NULL,
qty_in_orders INTEGER NOT NULL,
CHECK (qty_on_hand >= qty_in_orders));
The problem is : user need more flexible approach, they want to decide at
initial implementation / runtime where they need this constraint, there is a
parameter table to store this setting.
CREATE TABLE App_Parameter
(Check_Stock bit);
The above CHECK constraint should only be run if App_Parameter.Check_Stock = 1.
How can I do this ?
TIA,
KristYou could possibly use a UDF which returns 0 or 1 and which checks against the other table. Watch
out, though, that if you don't refer to the column in the check constraint, then the optimizer might
not deem is necessary to validate the CHECK constraint. This can happen when you have CHECK
constraint which uses UDF can work against other tables. I don't think that it will be a problem in
your case, though. You could have something like:
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"tristant" <krislioe@.cbn.net.id> wrote in message news:uZGQ$YYuDHA.2368@.TK2MSFTNGP09.phx.gbl...
> Hi SQL Gurus,
> Using Sql2000, I have a table to maintain stock availibility, the
> requirement is qty_in_orders must be <= qty_on_hand, so I use CHECK
> constraint like below :
> CREATE TABLE StockAvailibility
> (prod_code CHAR(5) NOT NULL PRIMARY KEY,
> qty_on_hand INTEGER NOT NULL,
> qty_in_orders INTEGER NOT NULL,
> CHECK (qty_on_hand >= qty_in_orders));
> The problem is : user need more flexible approach, they want to decide at
> initial implementation / runtime where they need this constraint, there is a
> parameter table to store this setting.
> CREATE TABLE App_Parameter
> (Check_Stock bit);
> The above CHECK constraint should only be run if App_Parameter.Check_Stock => 1.
> How can I do this ?
> TIA,
> Krist
>
>
>|||Tibor,
Did you intend to give an example'
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:OIZOqdYuDHA.1740@.TK2MSFTNGP12.phx.gbl...
> You could possibly use a UDF which returns 0 or 1 and which checks against
the other table. Watch
> out, though, that if you don't refer to the column in the check
constraint, then the optimizer might
> not deem is necessary to validate the CHECK constraint. This can happen
when you have CHECK
> constraint which uses UDF can work against other tables. I don't think
that it will be a problem in
> your case, though. You could have something like:
>
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "tristant" <krislioe@.cbn.net.id> wrote in message
news:uZGQ$YYuDHA.2368@.TK2MSFTNGP09.phx.gbl...
> > Hi SQL Gurus,
> > Using Sql2000, I have a table to maintain stock availibility, the
> > requirement is qty_in_orders must be <= qty_on_hand, so I use CHECK
> > constraint like below :
> >
> > CREATE TABLE StockAvailibility
> > (prod_code CHAR(5) NOT NULL PRIMARY KEY,
> > qty_on_hand INTEGER NOT NULL,
> > qty_in_orders INTEGER NOT NULL,
> > CHECK (qty_on_hand >= qty_in_orders));
> >
> > The problem is : user need more flexible approach, they want to decide
at
> > initial implementation / runtime where they need this constraint, there
is a
> > parameter table to store this setting.
> > CREATE TABLE App_Parameter
> > (Check_Stock bit);
> >
> > The above CHECK constraint should only be run if
App_Parameter.Check_Stock => > 1.
> > How can I do this ?
> >
> > TIA,
> > Krist
> >
> >
> >
> >
> >
>|||I guess I did, but the hunger overwhelmed me and I went for lunch ;-). Below is a very stripped down
to bare essentials example:
create table t(c1 bit)
GO
create function f() returns bit as
begin
RETURN (SELECT TOP 1 c1 FROM dbo.t)
end
GO
Create table t1(c1 int)
GO
ALTER TABLE t1 ADD CONSTRAINT x CHECK(dbo.f() = 0 OR (dbo.f() = 1 AND c1 < 10))
GO
INSERT t values(0) --Means do not check
INSERT t1 values(10)
GO
UPDATE t SET c1 = 1 --Means check
INSERT t1 values(10) --Fails
UPDATE t1 SET c1 = 11 --Also fails
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"SriSamp" <ssampath@.sct.co.in> wrote in message news:%23$o2JsYuDHA.2308@.TK2MSFTNGP11.phx.gbl...
> Tibor,
> Did you intend to give an example'
> --
> HTH,
> SriSamp
> Please reply to the whole group only!
> http://www32.brinkster.com/srisamp
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:OIZOqdYuDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > You could possibly use a UDF which returns 0 or 1 and which checks against
> the other table. Watch
> > out, though, that if you don't refer to the column in the check
> constraint, then the optimizer might
> > not deem is necessary to validate the CHECK constraint. This can happen
> when you have CHECK
> > constraint which uses UDF can work against other tables. I don't think
> that it will be a problem in
> > your case, though. You could have something like:
> >
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "tristant" <krislioe@.cbn.net.id> wrote in message
> news:uZGQ$YYuDHA.2368@.TK2MSFTNGP09.phx.gbl...
> > > Hi SQL Gurus,
> > > Using Sql2000, I have a table to maintain stock availibility, the
> > > requirement is qty_in_orders must be <= qty_on_hand, so I use CHECK
> > > constraint like below :
> > >
> > > CREATE TABLE StockAvailibility
> > > (prod_code CHAR(5) NOT NULL PRIMARY KEY,
> > > qty_on_hand INTEGER NOT NULL,
> > > qty_in_orders INTEGER NOT NULL,
> > > CHECK (qty_on_hand >= qty_in_orders));
> > >
> > > The problem is : user need more flexible approach, they want to decide
> at
> > > initial implementation / runtime where they need this constraint, there
> is a
> > > parameter table to store this setting.
> > > CREATE TABLE App_Parameter
> > > (Check_Stock bit);
> > >
> > > The above CHECK constraint should only be run if
> App_Parameter.Check_Stock => > > 1.
> > > How can I do this ?
> > >
> > > TIA,
> > > Krist
> > >
> > >
> > >
> > >
> > >
> >
> >
>