Allow specify of null constraint name in SET NOT NULL
-----------------------------------------------------
Key: CORE-5806
URL: http://tracker.firebirdsql.org/browse/CORE-5806
Project: Firebird Core
Issue Type: New Feature
Components: Documentation, Engine
Affects Versions: 3.0.4
Reporter: Karol Bieniaszewski
Few thinks about not null constraint and its name - i do not know if this
should be as one ticket or some releated tickets
1. Specification of NOT NULL constraint is undocumented feature avaiable for
years.
e.g we can do:
Create table A
(
FIELD1 INTEGER CONSTRAINT NK_A_FIELD1 NOT NULL
)
2. In previous Firebird versions (prior FB3.0)
there was possibility to do:
ALTER TABLE XXX ADD FIELDX INTEGER CONSTRAINT NK_XXX__FIELDX NOT NULL;
UPDATE TABLE XXX SET FIELDX=some calculations;
and after that we have named null constraint
but now in FB3 we can not do same. We must do:
ALTER TABLE XXX ADD FIELDX INTEGER;
UPDATE TABLE XXX SET FIELDX=some calculations;
ALTER TABLE XXX ALTER FIELDX SET NOT NULL;
and in SET NOT NULL we can not specify NOT NULL constraint name.
And this is good if user can name all self created constraint in the
database.
3. Will be good to see not null constraint name in error message like it is for
all other constraints like PK, FK, CK, UK.
this was discussed on the support mailing list and Mark Rotteveel say there:
"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
"
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
