Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
johnstern
Yak Posting Veteran
67 Posts |
 Posted - 2007-08-01 : 20:43:48
|
| Is it posible to disregard a foreing key contrain "sometimes" or does this whole idea defeats the purpose., I have a table that a client has it with a foreing key constrain, however in some instances that field maybe be set to null which triggers an constrain exception. is it possible to make a stored procedure that will for this time forget the key constrain ? does it make sense or is this bad programminglike so:if @Region = nullBEGIN ForeingContrains OFFUpdate VehiclesTable SET Region=nullEND ELSE Update VehiclesTable SET Region= @Region |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-08-01 : 22:02:47
|
| If the column allows NULL values, NULL values are always allowed regardless of any foreign key constraints.- Jeffhttp://weblogs.sqlteam.com/JeffS |
 |
|
|
hrishi_des
Yak Posting Veteran
76 Posts |
Posted - 2007-08-02 : 00:38:36
|
| you can disable the constraint and then enable it.When solution is simple, God is answering…. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-08-02 : 02:06:16
|
quote:
Originally posted by hrishi_des
you can disable the constraint and then enable it.
ummm, no. that won't work. Jeff has the right answer. You need to have a NULL in the table the FK points to, otherwise you get a FK violation. -ec |
|
|
|
hrishi_des
Yak Posting Veteran
76 Posts |
Posted - 2007-08-02 : 06:05:14
|
| we have done that in our projects!!!!When solution is simple, God is answering…. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-08-02 : 06:25:30
|
| Well you could use WITH NOCHECK, but then any other concurrent updates will be NOCHECK too ... and a concurrent update re-enabling the constraint will muck up your session, won't it?Seems like a daft idea to me.The NULL thingie should be OK as others have said. For something more "conditional" than that I would use a Trigger to enforce it, rather than an FK.Kristen |
|
|
|
johnstern
Yak Posting Veteran
67 Posts |
Posted - 2007-08-02 : 10:02:04
|
| when I first enter a record, I can leave that field to null, my problem is when updating this field from a integer to a null region 4 to region null |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-08-02 : 11:07:40
|
| do you also have a unique constraint on that column?_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
|
|
|
|
|
disregard foreign Constrain sometimes ? posible ?