Thanks Mark
From: [email protected] [mailto:[email protected]] Sent: Thursday, July 23, 2015 8:22 AM To: [email protected] 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' [email protected] [firebird-support]" <[email protected]> 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]
