I want to have a non-null foreign key reference, e.g.
owner = db.define_table('owner'', Field('name'))
package = db.define_table('package', Field('owner_id', owner,
notnull=True), Field('name'))
SQLite for example has no problem with this:
sqlite> create table owner(id int primary key, name text);
sqlite> create table package(id int primary key, owner_id references owner
not null, name text);
sqlite> insert into owner values(1, "web2py");
sqlite> insert into package values(1, 1, "markmin");
sqlite> insert into package values(2, null, "hello");
Error: package.owner_id may not be NULL
However, web2py will not put a "not null" qualifier on the foreign key
reference; there is a specific check to NOT add a "not null" if the field
is an id field or it is a reference field. Why is that?
A bug in my app relating to this was just discovered by a user; I was
relying on the database to signal an IntegrityError and fail to insert
records that have a null reference, but they got in anyway.
validate_and_insert will actually catch this -- but, why doesn't web2py
append the NOT NULL?
