On 03/31/15 12:56, Dmitry Yemanov wrote:
> 31.03.2015 07:20, Adriano dos Santos Fernandes wrote:
>> Currently there is inconsistencies in fields [NOT] NULL flag on its
>> (field) flag does not matches the domain.
>>
>> Use CONNECT or CREATE DATABASE to specify a database
>> SQL> create database 'z.fdb';
>> SQL> create domain d1n integer not null;
>> SQL> create table t1 (n1 d1n);
>> SQL>
>> SQL> insert into t1 values (null); -- ok
>> Statement failed, SQLSTATE = 23000
>> validation error for column "T1"."N1", value "*** null ***"
>> SQL>
>> SQL> alter table t1 alter n1 null;
>> SQL>
>> SQL> show table t1;
>> N1 (D1N) INTEGER Not Null
>> SQL>
>> SQL> insert into t1 values (null);
> Before discussing the problem, I think we should change the syntax. The
> SQL spec suggests ALTER [COLUMN] N1 {SET | DROP} NOT NULL instead of our
> current ALTER [COLUMN] N1 [NOT] NULL. And I like this, as formally NOT
> NULL is a constraint. BTW, we seem to miss a DDL ability to change the
> column-level CHECK constraint.
>
> With SET NOT NULL and DROP NOT NULL, we don't just set the field flag
> but explicitly declare that we want a constraint or we don't want a
> constraint. So even if a constraint was inherited from a domain, I think
> this should mean to change the constraint. In other words, the engine is
> correct in treating the new definition as an override.
According to SQL specification:
Let C be a column of a base table T. C is known not nullable if and only
if _at least one_ of the following is true:
.... <some conditions>
— C is based on a domain that has a domain constraint that is not
deferrable and that simply contains a <search
condition> that is a <boolean value expression> that is a
readily-known-not-null condition for VALUE.
.... <other condiitons follow>
I.e. we can not override NOT NULL constraint set in the domain.
------------------------------------------------------------------------------
Dive into the World of Parallel Programming The Go Parallel Website, sponsored
by Intel and developed in partnership with Slashdot Media, is your hub for all
things parallel software development, from weekly thought leadership blogs to
news, videos, case studies, tutorials and more. Take a look and join the
conversation now. http://goparallel.sourceforge.net/
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
