On 24-4-2018 20:47, 'livius' liviuslivius@...
[firebird-support] wrote:

>
>
> >
> >

Hi,

> > I need to add Field not null constraint with my custom

name.

> > in previous versions of Firebird we can do:
> > 1.

ALTER TABLE XXX ADD FIELDX INTEGER CONSTRAINT NK_XXX__FIELDX NOT NULL;

>
> >>Interestingly, that syntax is not documented in the Firebird

2.5

> >>language reference.
> it has been available for years.

And we use it also in table creation

> Create table A
> (
>

FIELD1 INTEGER CONSTRAINT NK_A_FIELD1 NOT NULL

> )
> and this syntax

is interchangeably between Firebird and Interbase.

> There is difference

in message when null is inserted into not null field.

> In Interbase there

is constraint name in message in Firebird it is not

> included.
>

In Firebird we have “TABLE”.”FieldName” in message.

> Will be good if

Firebird will also contain constraint name.

> And this is good if user can

name all self created constraint in the

> database like it is for all

other PK, FK, UK, CK.

> You can then have in app standardized handling

about e.g. error messages.

Given named not null constraints are an undocumented feature, you can't
expect too much from it. I suggest you create tickets to get this
documented, and maybe to extend support to allow naming the constraint
when using alter table xxx alter yyy set not null.

I'd suggest something like expanding ALTER TABLE ADD <tconstraint> by
adding the option to tconstraint:

[CONSTRAINT <constraint-name>] NOT NULL (<column-name>)

Or maybe

ALTER TABLE <table name> ALTER <field name> SET [CONSTRAINT
<constraintname>] NOT NULL

Although that might conflict with the oddity of also supporting ALTER
TABLE <table name> ALTER <field name> SET NULL, which is not defined in
the SQL standard and is not a real constraint, and shouldn't get named.

Interestingly, the SQL standard also supports named not null
constraints, but there also naming it using ALTER COLUMN ... SET NOT
NULL is not supported.

Mark
--
Mark Rotteveel