Thanks Mark



From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Sent: Thursday, July 23, 2015 8:22 AM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Upgrade Firebird 1.5.3 to 2.5.4 - CHECK Constraint Change Question





On Thu, 23 Jul 2015 08:15:35 -0500, "'stwizard' stwizard@...
[firebird-support]" <firebird-support@yahoogroups.com> wrote:

> Greetings All,
>
> In the Firebird 2 Migration & Installation guide
>

http://www.firebirdsql.org/file/documentation/release_notes/Firebird-2_1_6-I

> nstallation.pdf
> on page 4 it states the following:
>
> CHECK Constraint Change
> Formerly, CHECK constraints were not SQL standard-compliant in regard

to

> the handling of NULL. For example,
> CHECK (DEPTNO IN (10, 20, 30))
> should allow NULL in the DEPTNO column but it did not.
>
> In Firebird 2.0, if you need to make NULL invalid in a CHECK

constraint,

> you must do so explicitly by extending the constraint. Using the example
> above:
> CHECK (DEPTNO IN (10, 20, 30) AND DEPTNO IS NOT NULL)
>
> I have a field called FTP_FORCE_LOWER_CASE defined as SmallInt Not Null
> with
> the following constraint
>
> /* (0) False, (1) True */
> FTP_FORCE_LOWER_CASE IN (0, 1)
>
> Do I need to change it to this even though the Not Null column is

checked?

> /* (0) False, (1) True */
> FTP_FORCE_LOWER_CASE IN (0, 1) AND FTP_FORCE_LOWER_CASE IS NOT NULL

No, you don't need to change it, it is already enforced by the NOT NULL
constraint. The note in the release notes is just to let you know what to
check for if your application depended on the old behavior (intentionally
or unintentionally).

Mark





[Non-text portions of this message have been removed]