help with Check contraints

I need to add a check contraint to a table in SQL Server 2000 that would do
the following
if the value of [type] = 1 then [year] cannot be null
is this possible to do? because for the other values of [type] i want the
[year] to be null
thanks for any help!
benALTER TABLE your_table
ADD CONSTRAINT CK_your_table__type_year_match
CHECK ((type = 1 AND year IS NOT NULL) OR (type <> 1 AND year IS NULL))
Jacco Schalkwijk
SQL Server MVP
"Ben" <ben_1_ AT hotmail DOT com> wrote in message
news:8E1D564C-58FC-4DFC-8D0E-C4E32D9C322D@.microsoft.com...
>I need to add a check contraint to a table in SQL Server 2000 that would do
> the following
> if the value of [type] = 1 then [year] cannot be null
> is this possible to do? because for the other values of [type] i want the
> [year] to be null
> thanks for any help!
> ben|||Thank you very much. That was exactly what i was looking for
"Jacco Schalkwijk" wrote:

> ALTER TABLE your_table
> ADD CONSTRAINT CK_your_table__type_year_match
> CHECK ((type = 1 AND year IS NOT NULL) OR (type <> 1 AND year IS NULL))
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Ben" <ben_1_ AT hotmail DOT com> wrote in message
> news:8E1D564C-58FC-4DFC-8D0E-C4E32D9C322D@.microsoft.com...
>
>|||Sorry, one more question about check contraints.
is it possible to have a contraint that only allowed entries where for every
pair of columns [a] and [b] there is only 1 value in the [type] column? or
can this only be done through stored procedures and functions?
thanks again
"Jacco Schalkwijk" wrote:

> ALTER TABLE your_table
> ADD CONSTRAINT CK_your_table__type_year_match
> CHECK ((type = 1 AND year IS NOT NULL) OR (type <> 1 AND year IS NULL))
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Ben" <ben_1_ AT hotmail DOT com> wrote in message
> news:8E1D564C-58FC-4DFC-8D0E-C4E32D9C322D@.microsoft.com...
>
>|||Please explain "only 1 value in the [type] column".
ML|||well, ill have 3 possible valuse for type: 1,2,3. the other columns
category_ID, area_ID are used to specify a location in a grid like system.
i
want there to only be 1 value in the type column for every combination of
category_id and area_id. this will have to be similar to a query on the
entire table to ensure that the pair doesnt have an entry with type = 1 and
a
entry with type = 2 but multiple entries of type=1 is alowed.
i hope that helped a little
"ML" wrote:

> Please explain "only 1 value in the [type] column".
>
> ML|||On Fri, 28 Oct 2005 08:16:08 -0700, Ben <ben_1_ AT hotmail DOT com>
wrote:

>Sorry, one more question about check contraints.
>is it possible to have a contraint that only allowed entries where for ever
y
>pair of columns [a] and [b] there is only 1 value in the [type] column? or
>can this only be done through stored procedures and functions?
Hi Ben,
UNIQUE (a, b, type)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||that is a valid check contraint? i tried that and it was giving me errors.
sql server 2k. also, i dont think that will allow me to have multiple type=
1
values for each a,b pair. I need that to be allowed, but the type values
cannot be different.
"Hugo Kornelis" wrote:

> On Fri, 28 Oct 2005 08:16:08 -0700, Ben <ben_1_ AT hotmail DOT com>
> wrote:
>
> Hi Ben,
> UNIQUE (a, b, type)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||In that case you should have a separate table with (category_ID, area_ID) as
the Primary Key and Type as the other column. Your database is not properly
normalised, and this will cause all kinds of problems.
Feel free to post your table definitions and a description of your business
problem, and people will give you advise on how to improve your database.
In the mean time, if you don't have the scope or authority to make these
changes, you can apply a band aid with an indexed view (untested):
CREATE vw_chk_your_table
WITH SCHEMA_BINDING
AS
SELECT category_ID, area_ID, Type, COUNT_BIG(*) AS cnt
FROM your_table
GROUP BY category_ID, area_ID, Type
GO
CREATE UNIQUE CLUSTERED INDEX ixc_vw_chk_your_table
ON vw_chk_your_table (category_ID, area_ID)
Jacco Schalkwijk
SQL Server MVP
"Ben" <ben_1_ AT hotmail DOT com> wrote in message
news:45CE651A-D484-4FD1-8230-1D64BA2E34E8@.microsoft.com...
> well, ill have 3 possible valuse for type: 1,2,3. the other columns
> category_ID, area_ID are used to specify a location in a grid like system.
> i
> want there to only be 1 value in the type column for every combination of
> category_id and area_id. this will have to be similar to a query on the
> entire table to ensure that the pair doesnt have an entry with type = 1
> and a
> entry with type = 2 but multiple entries of type=1 is alowed.
> i hope that helped a little
> "ML" wrote:
>|||X-Newsreader: Forte Agent 1.91/32.564
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Complaints-To: abuse@.supernews.com
Lines: 52
Path: TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-online.de!newshub.sd
su.edu!newsfeed.news2me.com!newsfeed2.easynews.com!newsfeed1.easynews.com!ea
synews.com!easynews!sn-xit-03!sn-xit-10!sn-xit-01!sn-post-02!sn-post-01!supe
rnews.com!corp.supernews.co
m!not-for-mail
Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:562181
On Fri, 28 Oct 2005 23:44:03 -0700, Ben <ben_1_ AT hotmail DOT com>
wrote:
(cut topposting)
>"Hugo Kornelis" wrote:
>
(paste topposting)
>that is a valid check contraint? i tried that and it was giving me errors.
>sql server 2k.
Yes, it's valid. What were the errors you got? And what was the exact
text of the complete statement you used it in?

>also, i dont think that will allow me to have multiple type=1
>values for each a,b pair. I need that to be allowed, but the type values
>cannot be different.
Re-reading what you write, I now see I misinterpreted your question. The
asnwer to your original question should have been:
UNIQUE (a, b)
That would allow only 1 value for type for every pair of values for a
and b, as you originally requested. As such, it would NOT allow multiple
type=1 for a a,b pair, since multiple type=1 is incompatible with "only
1 value in the [type] column".
At this point, I have sincere doubts if these columns really should be
combined in the same table at all. But I must also admit that I'm no
longer sure if I actuallly understand your requirement. It might help if
you posted a few concrete examples of rows of data that can or can not
be in the table at the same time. It would also be a tremendous help if
you could explain the actual business problem that you're trying to
solve.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)