CE Query Analyzer Please Help!

Hi,

I am having a frustrating problem with SQL CE query analyzer.

I cannot add a NOT NULL column to an existing table, using either the GUI or
an SQL statement "ALTER TABLE Sites ADD status bit NOT NULL". I tried the
same with all my existing tables, and with different data types, and no luck.
I can only add these columns when I originally design the table. All my
tables have PKs and are empty.

Is this a bug or is it supposed to operate this way? I really don't want to
re-create all my tables just to add one non-nullable column to each.

Any help would be GREATLY appreciated.

Mike
Thu Feb 17 18:01:41 CST 2005

Unlike the full version of SQL Server, you can't have a column that neither
accepts nulls nor has a default value. I imagine that's to cut down on
handling errors if the column isn't populated, but I'm just guessing. If
you add a default value, the column should add fine (as long as the table is
empty).

Mike


"CJ" <CJ@discussions.microsoft.com> wrote in message
news:9111DF65-40A1-44E9-A2F2-5A51A24F0017@microsoft.com...
> Hi,
>
> I am having a frustrating problem with SQL CE query analyzer.
>
> I cannot add a NOT NULL column to an existing table, using either the GUI
> or
> an SQL statement "ALTER TABLE Sites ADD status bit NOT NULL". I tried the
> same with all my existing tables, and with different data types, and no
> luck.
> I can only add these columns when I originally design the table. All my
> tables have PKs and are empty.
>
> Is this a bug or is it supposed to operate this way? I really don't want
> to
> re-create all my tables just to add one non-nullable column to each.
>
> Any help would be GREATLY appreciated.
>
>

Paul
Fri Feb 18 07:04:34 CST 2005

I confess that I am not a SQL Server or SQL CE professional, but I am
quite familiar with DB2 on z/OS. In DB2 it is not possible to ALTER a
table to ADD a column that is NOT NULL. It is supposed to work that
way, 'cuz the RDBMS has no way of knowing what value to add to the
existing rows.

Two possible solutions:
(a) ALTER TABLE Sites ADD statusBit NOT NULL WITH DEFAULT
(b) Unload table, DROP, re-CREATE it.

OK, I just noticed that all your tables are empty, so thereby we come to
option (c) DROP, re-CREATE it.

Anyhow, that's WHY it won't let you. I suppose it seems bizarre (and
certainly frustrating) to have to DROP an empty table, but the DDL
processor does not look at existing (or not) data.

HTH,
Paul


CJ said the following on 2/17/2005 4:37 PM:
> Hi,
>
> I am having a frustrating problem with SQL CE query analyzer.
>
> I cannot add a NOT NULL column to an existing table, using either the GUI or
> an SQL statement "ALTER TABLE Sites ADD status bit NOT NULL". I tried the
> same with all my existing tables, and with different data types, and no luck.
> I can only add these columns when I originally design the table. All my
> tables have PKs and are empty.
>
> Is this a bug or is it supposed to operate this way? I really don't want to
> re-create all my tables just to add one non-nullable column to each.
>
> Any help would be GREATLY appreciated.
>
>

CJ
Fri Feb 18 10:19:02 CST 2005

Thanks for the help guys, makes sense.

Once again the need for complete and thorough database design, BEFORE
creating a database, is reaffirmed.


Cheers

"Paul Bruesch" wrote:

> I confess that I am not a SQL Server or SQL CE professional, but I am
> quite familiar with DB2 on z/OS. In DB2 it is not possible to ALTER a
> table to ADD a column that is NOT NULL. It is supposed to work that
> way, 'cuz the RDBMS has no way of knowing what value to add to the
> existing rows.
>
> Two possible solutions:
> (a) ALTER TABLE Sites ADD statusBit NOT NULL WITH DEFAULT
> (b) Unload table, DROP, re-CREATE it.
>
> OK, I just noticed that all your tables are empty, so thereby we come to
> option (c) DROP, re-CREATE it.
>
> Anyhow, that's WHY it won't let you. I suppose it seems bizarre (and
> certainly frustrating) to have to DROP an empty table, but the DDL
> processor does not look at existing (or not) data.
>
> HTH,
> Paul
>
>
> CJ said the following on 2/17/2005 4:37 PM:
> > Hi,
> >
> > I am having a frustrating problem with SQL CE query analyzer.
> >
> > I cannot add a NOT NULL column to an existing table, using either the GUI or
> > an SQL statement "ALTER TABLE Sites ADD status bit NOT NULL". I tried the
> > same with all my existing tables, and with different data types, and no luck.
> > I can only add these columns when I originally design the table. All my
> > tables have PKs and are empty.
> >
> > Is this a bug or is it supposed to operate this way? I really don't want to
> > re-create all my tables just to add one non-nullable column to each.
> >
> > Any help would be GREATLY appreciated.
> >
> >
>