not null and reference field

[Paolo Valleri]

Hi, 

I tested this issue: https://github.com/web2py/web2py/issues/653

The following code works instead of raising an exception

db=DAL(url)

db.define_table('tt', 

    Field('value', 'integer'),

)

db.define_table('ttt', 

    Field('value', 'integer', notnull=True),

    Field('tt_id', 'reference tt', notnull=True),

)

db.ttt.insert(value=1)

print db(db.ttt).select()

db.close()

Tested on both postgres and sqlite, it works, because the field tt_id of ttt doesn't have NOT NULL as sjown by the sql below

CREATE TABLE ttt

(

  id serial NOT NULL,

  value integer NOT NULL,

  tt_id integer,

  CONSTRAINT ttt_pkey PRIMARY KEY (id),

  CONSTRAINT ttt_tt_id_fkey FOREIGN KEY (tt_id)

      REFERENCES tt (id) MATCH SIMPLE

      ON UPDATE NO ACTION ON DELETE CASCADE

)

The problem is that line https://github.com/web2py/pydal/blob/master/pydal/adapters/base.py#L372

Do you remember why has been introduced ?

Paolo

[Niphlod]

seems that it's sitting there even on 2.0.2 (august 2012) ...
BTW: that line is "to blame" also for FK and unique=True .

I think we'd need tests but AFAIK every FK should be able to accept either NOT NULL and/or UNIQUE . This opens up a nifty pandora's box... in some backends, you can have a unique column with multiple rows holding NULL values, other instead only accept at most 1 NULL value... but that's another story.

reassuming (IMHO)
1) FK can be unique (useful 1:1 relationship instead of 1:M)  

2) FK can be nullable (i.e. the rare-case scenario where you need to store pre-set records that will be matched against the FK at a later time )
3) FK can be unique and not null (the case where you NEED a valid value of the referenced table to be inserted in the referencing. If the 'referenced' has NULLs in it, the previously mentioned pandora's box opens. In web2py a reference is made against a "sequence PK" that it's not null by default, so the pandora's box, at least without taking into consideration legacy tables, doesn't need to be evaluated)

[Paolo Valleri]

ok, then I'll write few tests to see how the different back-end works

 Paolo

--
-- mail from:GoogleGroups "web2py-developers" mailing list
make speech: web2py-d...@googlegroups.com
unsubscribe: web2py-develop...@googlegroups.com
details : http://groups.google.com/group/web2py-developers
the project: http://code.google.com/p/web2py/
official : http://www.web2py.com/
---
You received this message because you are subscribed to the Google Groups "web2py-developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to web2py-develop...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Paolo Valleri]

Beside nosql backends, all backends I've checked support NOT NULL/UNIQUE for FK.

However simply enabling it on pydal, worked for all backends but mysql (maybe this is the reason because it was disabled?)

I've rewritten a bit the 'reference' type of few adapters and now it passes all tests we have plus the ones I wrote.

see https://github.com/ilvalle/pydal/tree/notnull

 Paolo

[Giovanni Barillari]

Any updates about this? Can be included in 15.05?

[Paolo Valleri]

I'd include this after 15.05

 Paolo

[Giovanni Barillari]

Ok!