Can not add a NOT NULL column with default value NONE

Question

I am trying to create a table named company in the default sqlite3 database in web2py:

db.define_table('company',
    Field('company_name', notnull=True, unique=True),
    Field('email'),
    Field('phone', notnull=True),
    Field('url3'),
    format = '%(company_name)s'
)

db.company.email.requires = IS_EMAIL()
db.company.url3.requires = IS_EMPTY_OR(IS_URL())

I get an error:

<class 'sqlite3.OperationalError'> Cannot add a NOT NULL column with default value NULL

It comes from this line:

format = '%(company_name)s'

How to solve this error?

score 1 · Answer 1 · edited May 23 '17 at 12:14

1

If this is an existing table, and you are adding/updating the company_name column, sqlite will complain when adding a NOT NULL column with no default (see here).

If there are no records in the table, you can simply drop the table and create it from scratch. If there are records, you can first export the existing data, then re-create the table, and then import the original data into the new table.

Also, note that web2py does not migrate changes in field attributes such as notnull and unique.

edited May 23 '17 at 12:14

Community

1
1

answered Sep 04 '15 at 12:07

Anthony

25,466
3
28
57

Sorry for late reply. Thanks for the answer. I understand the problem now. I have tried to drop the table "db.company.drop()" and than re-create it right after the drop. But apparently, according to the doc: "web2py will not re-create the dropped table until you navigate the file system to the databases directory of your app, and delete the file associated with the dropped table." i have navigated to the database directory of my app. But i couldn't find the exact db file. It seems, sqlite3 sucks. – ni8mr Sep 07 '15 at 14:24
In /web2py/applications/yourapp/databases, you don't see a file named [long_random_string]_company.table? If not, what happens when you run the app -- does it try to re-create the "company" table? Make sure migrations are enabled. – Anthony Sep 07 '15 at 14:33
Thanks, for the naming clarification. I have found the .........company.table file and deleted it. But another weird problem has appeared. I erased the drop table line and after running, now it is showing that " table company already exists". It is weird because i have refreshed my directories but couldn't see any '..company.table' now. – ni8mr Sep 07 '15 at 14:39
1

Make sure the table has actually been dropped from the database. – Anthony Sep 07 '15 at 14:48

score 0 · Answer 2 · edited Nov 07 '22 at 18:54

I added a line which uses only data type String : address = db.Column(db.String(100)).

class Users(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(20), nullable=False)
    email = db.Column(db.String(50), nullable=False, unique=True)
    # we add line with use only data type String
    address = db.Column(db.String(100))
    date_added = db.Column(db.DateTime, default=datetime.utcnow)

Can not add a NOT NULL column with default value NONE

2 Answers2