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 |
|
Jusvistin
Yak Posting Veteran
81 Posts |
 Posted - 2003-08-16 : 07:31:05
|
Again, Rob Viera's book (chapter 7)I've got a table with a smalldatetime field (DateInSystem), there are two constraints on the field 1) A Default Constraint: GETDATE()2) A check constraint: CHECK ([DateInSystem] <= GETDATE())I'm doing an insert, and I'm using the value GETDATE() as the value for this field - but I'm getting the following error:
INSERT statement conflicted with COLUMN CHECK constraint 'CN_CustomerDateInSystem'. The conflict occurred in database 'Accounting', table 'Customers', column 'DateInSystem'.The statement has been terminated.
Seems to me the date the insert is getting may have a time stamp later than what the constraint is looking at, but I'm not sure. Anybody know why I'm getting this error ?Thanks,Kevin |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-08-16 : 07:59:42
|
| Smalldatetime has a resolution to the nearest minute. GetDate() also includes seconds and milliseconds. It's possible that these parts are causing rounding issues in the minute portion and violating the constraint.See if this fixes it:... CONSTRAINT CN_CustomerDateInSystem CHECK ([DateInSystem]<=CAST(GetDate() AS smalldatetime)) |
 |
|
|
Jusvistin
Yak Posting Veteran
81 Posts |
Posted - 2003-08-16 : 08:07:40
|
| Thanks,That did the trick.Perhaps this was in his errata for the book.Kevin |
|
|
|
Jusvistin
Yak Posting Veteran
81 Posts |
Posted - 2003-08-16 : 08:12:06
|
| Shouldn't I be doing the cast on the insert rather than trying to stuff the GetDate() result into a smaller field ?Kevin |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-08-16 : 08:21:52
|
| You can:DEFAULT CAST(GetDate() AS smalldatetime)But you'd still need the check constraint to do the conversion to make sure the same problem doesn't crop up. Someone may still try to insert an explicit date value:INSERT Customers(DateInSystem) VALUES(GetDate())Without the CAST in the check constraint that insert would fail like before (I'm only using GetDate() as an example, another date value could be used) |
|
|
|
Jusvistin
Yak Posting Veteran
81 Posts |
Posted - 2003-08-16 : 08:39:34
|
| Ok,I guess I'm still thinking too "my application" specific, and ignoring that eventually somebody else may use some "other" method to update the data.Thanks,Kevin |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-08-16 : 08:57:45
|
| Yes, that's something to be extremely careful of. You should define and implement all of you data integrity in the database, and lock it down BEFORE you start working on the application side. At the same time, you should also keep whatever integrity checks you have in the application as well. It's more work, but it also provides and extra layer of integrity checking (in case you miss a check in one layer, the other will still catch it) |
|
|
|
|
|
|
|
|
One more Constraint problem