Check Constraint -- sql server programming

alekas
8/31/2004 9:25:08 PM

((x is NULL) or (cast(x as float) between 0 and 99))
and
(
(cast(y as float) between 1 and 99)
and
(cast(z as float) between 1 and 99)
and
(cast(z as float)>cast(y as float)
)

May be it's not the best way, but should work :)

Alex

[quoted text, click to view]

Joe Celko
9/1/2004 8:32:10 AM

Can explain what attribute that column represetns in your data model?
I'll bet it starts to look like metadata pretty quick. And there is no
compact way until you have full Regular Expressions in T-SQL (the
SIMILAR TO predicate from SQL-92).

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***

Stefan Berglund
9/1/2004 8:36:05 AM

On Wed, 01 Sep 2004 09:03:35 +0200, Hugo Kornelis
[quoted text, click to view]
in <c6saj0duoqf15bt0d4b0aktqlh8frviire@4ax.com>

[quoted text, click to view]

That was pretty much my take on it as well in terms of the
multiple LIKE phrases. I think I'll just ignore it and perhaps
it will go away. <g>

The client has decided that rather than the byte field which
contains a person's age, they'd rather have an age range. I'm
going to inform them that I'll not be constraining input at all
in this field. The real shame of it all is that this information
is never used for anything other than display, yet I'm not
permitted to just simply discard it.

---

Joe Celko
9/1/2004 9:02:10 AM

[quoted text, click to view]
it will go away. <<

That works more often than people think <g>

[quoted text, click to view]

That sounds like a pair of (low_birthdate, high_birthdate)
with CHECK (low_birthdate <= high_birthdate) and the rule that when we
know it exactly, they are equal.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***

Hugo Kornelis
9/1/2004 9:03:35 AM

[quoted text, click to view]

Hi Stefan,

I don't think that there is a *COMPACT* way to do this. Is it really
necessary to have this in one column? Patterns like this are often an
indication that several logical attributes are combined into one column.
Also known as "bad design".

Also: you say that x may be blank. You are aware that a VARCHAR(5) column
holding one blank is different from the empty string (0 characters length)
and that both are different from NULL?

I think the shortest way to do this is:

ALTER TABLE MyTable
ADD CONSTRAINT MyCheck
CHECK ( MyColumn = ' '
OR MyColumn LIKE '[0-9]'
OR MyColumn LIKE '[1-9]+'
OR MyColumn LIKE '[1-9][0-9]+'
OR ( ( MyColumn LIKE '[1-8]-[2-9]'
OR MyColumn LIKE '[1-9]-[1-9][0-9]'
OR MyColumn LIKE '[1-9][0-9]-[1-9][0-9]')
AND CAST(LEFT(MyColumn,
CHARINDEX('-', MyColumn) - 1)
AS int)
< CAST(SUBSTRING(MyColumn,
CHARINDEX('-', MyColumn) + 1,
2)
AS int)))
(untested)

Best, Hugo
--

Stefan Berglund
9/1/2004 7:10:36 PM

On Wed, 01 Sep 2004 09:02:10 -0700, Joe Celko
[quoted text, click to view]
in <eEQ3K0DkEHA.2948@TK2MSFTNGP11.phx.gbl>

[quoted text, click to view]

Hi Joe-

That's a much better solution Joe, but actually this clientele
won't unnecessarily provide their ages let alone their birth
dates so they get to enter whatever they like in five chars or
less and bear the responsibility for content on the lone report
on which this info appears. I've essentially given them a five
character scratch pad with which they are free to shoot
themselves in the foot. C'est la vie!

---

Hugo Kornelis
9/2/2004 12:04:07 AM

[quoted text, click to view]

Hi Stefan,

Would it be acceptable for your client to decide on a set number of age
ranges to select from? In that case, a simple lookup table would do the
trick.

Best, Hugo
--

Stefan Berglund
9/2/2004 10:06:51 AM

On Thu, 02 Sep 2004 00:04:07 +0200, Hugo Kornelis
[quoted text, click to view]
in <knhcj098mo8qf3ib0ktof8gmcfe95i24a8@4ax.com>

[quoted text, click to view]

That was my first thought, but unfortunately the client is unable
to limit their choices sufficiently and the resulting dropdown
would be unwieldy.

Faced with concocting a check constraint that's longer than the
CREATE TABLE DDL, I've decided to let this column be a five
character scratch pad with no significance other than to the
client who might make use of it.

---

Anthony Faull
9/3/2004 11:27:39 AM

There's no shame in writing long check constraints. The equivalent stored
procedure or trigger logic would be even longer.

CREATE TABLE #scratch
(
scratch_pad varchar(5)
CONSTRAINT shot_in_the_foot
CHECK
(
scratch_pad LIKE '[0-9]'
OR scratch_pad LIKE '[0-9][0-9]'
OR scratch_pad LIKE '[1-9]+'
OR scratch_pad LIKE '[1-9][0-9]+'
OR scratch_pad LIKE '[1-9]-[1-9]'
OR scratch_pad LIKE '[1-9]-[1-9][0-9]'
OR scratch_pad LIKE '[1-9][0-9]-[1-9]'
OR scratch_pad LIKE '[1-9][0-9]-[1-9][0-9]'
)
)

Anthony

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : Check Constraint