[sqlite] NOT NULL asymetry between CREATE TABLE and ALTER TABLE, why?
Hi all, Have a look at the following short sqlite shell session: SQLite version 3.6.4 Enter .help for instructions Enter SQL statements terminated with a ; sqlite create table foo (a text(5) NOT NULL); sqlite alter table foo add column b text(5) NOT NULL; SQL error: Cannot add a NOT NULL column with default value NULL Is there a reason for this asymetric behaviour of ALTER TABLE and CREATE TABLE? Its a bit dated version, but current version has doc'ed the limitation that for ALTER TABLE still. Michael -- Michael Schlenker Software Architect CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: m...@contact.de Sitz der Gesellschaft: Bremen Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] NOT NULL asymetry between CREATE TABLE and ALTER TABLE, why?
The difference is that when you issue an ALTER TABLE, the table already exists. When you create a new table, it is known that there are no rows in it. But you can issue an ALTER TABLE to a table with rows in it. If so, what value should you put for the existing rows? Granted, in this specific case, there *are* no rows, so it's theoretically possible. But SQLite won't accept it. If you really want to add the column, you can just drop and recreate the table. On Tue, Aug 17, 2010 at 10:23 AM, Michael Schlenker m...@contact.de wrote: Hi all, Have a look at the following short sqlite shell session: SQLite version 3.6.4 Enter .help for instructions Enter SQL statements terminated with a ; sqlite create table foo (a text(5) NOT NULL); sqlite alter table foo add column b text(5) NOT NULL; SQL error: Cannot add a NOT NULL column with default value NULL Is there a reason for this asymetric behaviour of ALTER TABLE and CREATE TABLE? Its a bit dated version, but current version has doc'ed the limitation that for ALTER TABLE still. Michael -- Michael Schlenker Software Architect CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax: +49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: m...@contact.de Sitz der Gesellschaft: Bremen Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- -- Stevie-O Real programmers use COPY CON PROGRAM.EXE ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] NOT NULL asymetry between CREATE TABLE and ALTER TABLE, why?
Michael Schlenker msc-ynu+nt5fez2elga04la...@public.gmane.org writes: Hi all, Have a look at the following short sqlite shell session: SQLite version 3.6.4 Enter .help for instructions Enter SQL statements terminated with a ; sqlite create table foo (a text(5) NOT NULL); sqlite alter table foo add column b text(5) NOT NULL; SQL error: Cannot add a NOT NULL column with default value NULL Is there a reason for this asymetric behaviour of ALTER TABLE and CREATE TABLE? What values do you want the existing rows to have in the new column when you declare it as NOT NULL? You have to specify that, otherwise SQLite doesn't know what to do. Best, -Nikolaus -- »Time flies like an arrow, fruit flies like a Banana.« PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6 02CF A9AD B7F8 AE4E 425C ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
