Alter table not reflected in syscolums.
User reported that ALTER TABLE MODIFY statements weren't persisting column nullability changes in syscolumns. Responders clarified that MODIFY sets exactly what's specified—it doesn't toggle properties. The final MODIFY statement used "DEFAULT NULL" which implicitly resets nullability to nullable (the default). Cannot specify NOT NULL with DEFAULT NULL; user needed to adjust their Django backend code accordingly.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi,
I am currently writing an Informix backend for Django using the IBM supplied
clidriver. When running the testsuite I have the following issue:
CREATE TABLE "test_alfl_pony" ("id" serial NOT NULL PRIMARY KEY, "pink"
integer NOT NULL, "weight" double precision NOT NULL)
SELECT * FROM syscolumns WHERE tabid = (SELECT tabid FROM systables WHERE
tabname = ?)
-> (u'pink', 340, 2, 258, 4, None, None, 0, 0, 0)
ALTER TABLE "test_alfl_pony" MODIFY "pink" integer NULL
SELECT * FROM syscolumns WHERE tabid = (SELECT tabid FROM systables WHERE
tabname = ?)
-> (u'pink', 340, 2, 2, 4, None, None, 0, 0, 0)
As you can see the coltype (item 4 in the resultset) changed from 258 to 2 (ie
from "integer not null" to "integer").
No if I run:
ALTER TABLE "test_alfl_pony" MODIFY "pink" integer DEFAULT 3
UPDATE "test_alfl_pony" SET "pink" = 3 WHERE "pink" IS NULL
ALTER TABLE "test_alfl_pony" MODIFY "pink" integer NOT NULL
ALTER TABLE "test_alfl_pony" MODIFY "pink" integer DEFAULT NULL
SELECT * FROM syscolumns WHERE tabid = (SELECT tabid FROM systables WHERE
tabname = ?)
-> (u'pink', 340, 2, 2, 4, None, None, 0, 0, 0)
The table still is nullable as seen by the 2 in the syscolumns. Do I somehow
need to flush something or similar? Any idea what I am missing?
Thanks,
Florian
The server did just as you requested: integer default null
What you wanted, presumably: integer not null default null
which consequently would get you:
592: Cannot specify column to be not null when the default value is=20
null.
The result of a MODIFY will be exactly what you specified in the MODIFY=20
clause, it does not just add or remove properties.
HTH,
Andreas
From: "FLORIAN APOLLONER" <florian.apolloner@bap.at>
To: ids@iiug.org
Date: 09.05.2017 11:52
Subject: Alter table not reflected in syscolums. [39143]
Sent by: ids-bounces@iiug.org
Hi,=20
I am currently writing an Informix backend for Django using the IBM=20
supplied=20
clidriver. When running the testsuite I have the following issue:=20
CREATE TABLE "test=5Falfl=5Fpony" ("id" serial NOT NULL PRIMARY KEY, "pink"=
=20
integer NOT NULL, "weight" double precision NOT NULL)=20
SELECT * FROM syscolumns WHERE tabid =3D (SELECT tabid FROM systables WHERE==20
tabname =3D ?)=20
-> (u'pink', 340, 2, 258, 4, None, None, 0, 0, 0)=20
ALTER TABLE "test=5Falfl=5Fpony" MODIFY "pink" integer NULL=20
SELECT * FROM syscolumns WHERE tabid =3D (SELECT tabid FROM systables WHERE==20
tabname =3D ?)=20
-> (u'pink', 340, 2, 2, 4, None, None, 0, 0, 0)=20
As you can see the coltype (item 4 in the resultset) changed from 258 to 2 =
(ie=20
from "integer not null" to "integer").=20
No if I run:=20
ALTER TABLE "test=5Falfl=5Fpony" MODIFY "pink" integer DEFAULT 3=20
UPDATE "test=5Falfl=5Fpony" SET "pink" =3D 3 WHERE "pink" IS NULL=20
ALTER TABLE "test=5Falfl=5Fpony" MODIFY "pink" integer NOT NULL=20
ALTER TABLE "test=5Falfl=5Fpony" MODIFY "pink" integer DEFAULT NULL=20
SELECT * FROM syscolumns WHERE tabid =3D (SELECT tabid FROM systables WHERE==20
tabname =3D ?)=20
-> (u'pink', 340, 2, 2, 4, None, None, 0, 0, 0)=20
The table still is nullable as seen by the 2 in the syscolumns. Do I=20
somehow=20
need to flush something or similar? Any idea what I am missing?=20
Thanks,=20
Florian=20
***************************************************************************=
****=20
Forum Note: Use "Reply" to post a response in the discussion forum.=20
Florian:
That's because your last alter:
ALTER TABLE "test_alfl_pony" MODIFY "pink" integer DEFAULT NULL;
Set the nullability constraint back to allow NULL which is the default.
That statement is the same as having done:
ALTER TABLE "test_alfl_pony" MODIFY "pink" integer NULL DEFAULT NULL;
So the correct statement to accomplish what you wanted was:
ALTER TABLE "test_alfl_pony" MODIFY "pink" integer NOT NULL DEFAULT NULL;
Which is an illegal statement. You cannot have a NOT NULL column that
defaults to NULL!
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on the IIUG, nor any other organization with which I am
associated either explicitly, implicitly, or by inference. Neither do
those opinions reflect those of other individuals affiliated with any
entity with which I am affiliated nor those of the entities themselves.
On Tue, May 9, 2017 at 5:51 AM, FLORIAN APOLLONER <florian.apolloner@bap.at>
wrote:
> Hi,
>
> I am currently writing an Informix backend for Django using the IBM
> supplied
> clidriver. When running the testsuite I have the following issue:
>
> CREATE TABLE "test_alfl_pony" ("id" serial NOT NULL PRIMARY KEY, "pink"
> integer NOT NULL, "weight" double precision NOT NULL)
>
> SELECT * FROM syscolumns WHERE tabid = (SELECT tabid FROM systables WHERE
> tabname = ?)
> -> (u'pink', 340, 2, 258, 4, None, None, 0, 0, 0)>
> ALTER TABLE "test_alfl_pony" MODIFY "pink" integer NULL
>
> SELECT * FROM syscolumns WHERE tabid = (SELECT tabid FROM systables WHERE
> tabname = ?)
> -> (u'pink', 340, 2, 2, 4, None, None, 0, 0, 0)>
> As you can see the coltype (item 4 in the resultset) changed from 258 to 2
> (ie
> from "integer not null" to "integer").
>
> No if I run:
>
> ALTER TABLE "test_alfl_pony" MODIFY "pink" integer DEFAULT 3
> UPDATE "test_alfl_pony" SET "pink" = 3 WHERE "pink" IS NULL
> ALTER TABLE "test_alfl_pony" MODIFY "pink" integer NOT NULL
> ALTER TABLE "test_alfl_pony" MODIFY "pink" integer DEFAULT NULL
>
> SELECT * FROM syscolumns WHERE tabid = (SELECT tabid FROM systables WHERE
> tabname = ?)
> -> (u'pink', 340, 2, 2, 4, None, None, 0, 0, 0)>
> The table still is nullable as seen by the 2 in the syscolumns. Do I
> somehow
> need to flush something or similar? Any idea what I am missing?
>
> Thanks,
> Florian
>
>
> ************************************************************
> *******************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--94eb2c1be85043fbe5054f15fc6c
Hi Art & Andreas, > The result of a MODIFY will be exactly what you specified in the MODIFY clause, it does not just add or remove properties. That makes sense. I thought I could just add/remove with that -- I will see if I can alter my backend code :/ Thanks, Florian
Don't miss the point that you can't have a NOT NULL column with a DEFAULT NULL constraint on it. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, May 9, 2017 at 8:43 AM, FLORIAN APOLLONER <florian.apolloner@bap.at> wrote: > Hi Art & Andreas, > > > The result of a MODIFY will be exactly what you specified in the MODIFY > clause, it does not just add or remove properties. > > That makes sense. I thought I could just add/remove with that -- I will > see if > I can alter my backend code :/ > > Thanks, > Florian > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --94eb2c1be850a37e65054f172840
Jupp, that was a result of me porting over Oracleisms where "DEFAULT NULL" drops the default (more or less at least).