Constrain new table values - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Constrain new table values

Author

Topic

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-11-29 : 13:13:40

Hi,

I am creating new table and declaring two columns with two types of values. First Column is with dates, second column is populated with value 200 across all dates. Table looks like that:

Dates: Value:
2011-12-01 ,200
2011-12-02 ,200
2011-12-03 ,200
2011-12-04 ,200
2011-12-05 ,200
2011-12-06 ,200
2011-12-31 ,200
I want to set the value in the second column to be 200 in all rows BUT the last one when the dtae = 2011-12-31. How do i do that and where in the process from declare table, declare columns, declare values, to insert values into table I include the clause? Thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-11-29 : 14:01:36

Is it the actual value '2011-12-31', or that it is the "last one" of the dates that control that constraint? If the latter it will be difficult to have a declared constraint to enforce it.

Since all but 1 row will have the same value, why store it in the table at all? What is that value supposed to mean?

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-11-29 : 14:44:41

It can be the actual value '2011-12-31' - is there a way to incorporate this? thanks!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-11-29 : 14:51:22

CREATE TABLE ConstraintTest(dateCol datetime NOT NULL,
valueCol int NOT NULL,
CONSTRAINT CHK_ValueCol CHECK (valueCol=200 OR dateCol='2011-12-31'))

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-11-29 : 15:04:07

Actually, I use the Declare @table TABLE (dateCol datetime, ValueCol int)...it seems that it doesnt work when I add thwe constraint in the brackets..

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-11-29 : 15:13:19

DECLARE @table TABLE (dateCol datetime NOT NULL,
valueCol int NOT NULL,
CHECK (valueCol=200 OR dateCol='2011-12-31'))

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-11-29 : 15:27:59

it doesnt give me an error now but this time the constraint doesn't work and I get a complete column of 200s..i think the reason might be because I am inserting values later on in the table and one of the values is the ValueCol = 200. However, if I dont do this, the column values become all NULL...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-11-29 : 15:35:01

There's no way the columns can store nulls if they are declared NOT NULL.

The following should fix the constraint:

DECLARE @table TABLE (dateCol datetime NOT NULL,
valueCol int NOT NULL,
CHECK ((valueCol=200 AND dateCol<>'2011-12-31') OR (valueCol<>200 AND dateCol='2011-12-31')))

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-11-29 : 15:44:34

sorry for the trouble but the last solution eliminates the row itslef with date '2011-12-31' while I still need this row... thanks so much for your help!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-11-29 : 15:46:19

DECLARE @table TABLE (dateCol datetime NOT NULL,
valueCol int NOT NULL,
CHECK ((valueCol=200 AND dateCol<>'2011-12-31') OR dateCol='2011-12-31'))

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-11-29 : 16:10:01

still not working - date row is there but the value 200 populated the row as well

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-11-29 : 16:22:18

You'll have to post your actual code and show exactly what is wrong/failing. I've done every variation of this constraint I can think of.

Remember that constraints DO NOT CHANGE values, they only prevent invalid values from being inserted or updated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources