Alter table constraint with variable?  
Author Message
Zevon





PostPosted: Thu Sep 02 21:59:22 CDT 2004 Top

SQL Server Developer >> Alter table constraint with variable?

I need to give my users the ability to change default values. I can
obviously use a hard-coded value here, but I cannot use a variable to re-add
the constraint. Any ideas?

ALTER TABLE Sales
ADD CONSTRAINT
DF_Sales_EstSubManDays


SQL Server187  
 
 
Earl





PostPosted: Thu Sep 02 21:59:22 CDT 2004 Top

SQL Server Developer >> Alter table constraint with variable? A little experimentation, and found that this works:

EXEC ('alter table Sales ADD CONSTRAINT DF_Sales_EstSubManDays DEFAULT ' +
@EstSubManDays + ' FOR EstSubManDays')



> I need to give my users the ability to change default values. I can
> obviously use a hard-coded value here, but I cannot use a variable to
re-add
> the constraint. Any ideas?
>
> ALTER TABLE Sales
> ADD CONSTRAINT
> DF_Sales_EstSubManDays

>
>


 
 
REMOVE_BEFORE_REPLYING_dportas





PostPosted: Fri Sep 03 06:13:06 CDT 2004 Top

SQL Server Developer >> Alter table constraint with variable? Certainly that will work but I suggest that it's unwise to allow end-users to
change the value of column defaults since those changes may impact others and
also break existing code. Don't you have a DBA with responsibility for change
control in your database? Or maybe your "user" is an "administrator" who
doesn't know how to do these things for him or her -self?!

If you need a user-configurable default, why not put the default values in
their own table? That way you could have a different default for each user or
other entity as required.

--
David Portas
SQL Server MVP
--

 
 
Earl





PostPosted: Fri Sep 03 09:57:40 CDT 2004 Top

SQL Server Developer >> Alter table constraint with variable? Thanks for the ideas David. I'll first note that indeed my initial solution
was to create a table with the default values. Frankly, I do not recall why
I rejected that approach. But I do see the safety factor involved in using
the separate table versus changing column defaults, so although I have the
code finished to implement my default changes, I may yet go with your
approach.

The reports that are generated out of this particular table require non-null
and non-zero values in several columns (cleaning the return value in code
would simply create inaccurate data). The situation is such that the default
values do indeed impact the organization system-wide -- but only to the
extent of showing what default values appear on a particular form. So
"different defaults for each user" would be irrelevant, although certainly I
can see that would be the best approach in most scenarios. And yes, none of
the users would have access directly to the server (MSDE).

In any event, I learned some interesting ways of manipulating the database
that I would not have if I had not gone the more difficult path.

Thanks again.



> Certainly that will work but I suggest that it's unwise to allow end-users
to
> change the value of column defaults since those changes may impact others
and
> also break existing code. Don't you have a DBA with responsibility for
change
> control in your database? Or maybe your "user" is an "administrator" who
> doesn't know how to do these things for him or her -self?!
>
> If you need a user-configurable default, why not put the default values in
> their own table? That way you could have a different default for each user
or
> other entity as required.
>
> --
> David Portas
> SQL Server MVP
> --
>