On 24-4-2018 20:47, 'livius' liviusliv...@poczta.onet.pl [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
