Foreign key notnull constraint

[vihang]

Hi,

The following does not make the foreign key field notnull (in
postgres)

db.define_table("Purchase_Invoice_Details",
db.Field("id_Purchase_Invoice", db.Purchase_Invoice,
notnull=True, ondelete='CASCADE'),
db.Field("Part_Number","string"))

any help.

Thanks
Vihang

[mdipierro]

What do you mean it does not make it nonull?

[vihang]

the particular column created in postgres does not have the NOT NULL
set to true.

[mdipierro]

Did you add the NOT NULL later?

[annet....@gmail.com]

Massimo,

Vihang is right. In my model I defined the following table:

db.define_table('adres',
SQLField('bedrijf', db.bedrijf, default='', notnull=True),
SQLField('adressoort', length=30, default='Vestigingsadres',
notnull=True),
SQLField('straat', length=42, default='', notnull=True),
SQLField('huisnummer', length=6, default='', notnull=True),
SQLField('huisnummerextensie', length=6),
SQLField('postcode_cijferdeel', type='integer'),
SQLField('postcode_letterdeel'),
SQLField('plaats', length=42, default='', notnull=True),
migrate='adres.table')

db.adres.bedrijf.requires=IS_IN_DB(db, db.bedrijf.id, '%(bedrijfsnaam)
s')
db.adres.adressoort.requires=IS_IN_SET(['Statutair
vestigingsadres','Vestigingsadres','Postadres'])
db.adres.straat.requires=[IS_LENGTH(42,error_message=T('length exceeds
42')), IS_NOT_EMPTY()]
db.adres.huisnummer.requires=[IS_LENGTH(6,error_message=T('length
exceeds 6')), IS_NOT_EMPTY()]
db.adres.huisnummerextensie.requires=IS_LENGTH(6,error_message=T
('length exceeds 6'))
db.adres.postcode_cijferdeel.requires=IS_NULL_OR(IS_MATCH('\d
{4}',error_message=T('no match 4 digits')))
db.adres.postcode_letterdeel.requires=IS_NULL_OR(IS_MATCH('[A-Z]
{2}',error_message=T('no match 2 capitals')))
db.adres.plaats.requires=IS_IN_DB(db, db.plaats.plaats, '%(plaats)s')


In pgAdmin the SQL reads like:


CREATE TABLE adres
(
id serial NOT NULL,
bedrijf integer,
adressoort character varying(30) NOT NULL,
straat character varying(42) NOT NULL,
huisnummer character varying(6) NOT NULL,
huisnummerextensie character varying(6),
postcode_cijferdeel integer,
postcode_letterdeel character varying(32),
plaats character varying(42) NOT NULL,
CONSTRAINT adres_pkey PRIMARY KEY (id),
CONSTRAINT adres_bedrijf_fkey FOREIGN KEY (bedrijf)
REFERENCES bedrijf (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE
)
WITH (OIDS=FALSE);


I didn't add the notnull=True later. In my applications this doesn't
raise any problems, the IS_IN_DB() validator prevents the field from
being empty. Furthermore, since the field is a foreign key I set
writable=False, and it gets its value programatically.

db.adres.bedrijf.writable=False
db.adres.bedrijf.default=auth.user.bedrijf


This is not a generic solution but it works for me. However, when you
have other applications connecting to the database this is an issue,
but why not set NOT NULL in pgAdmin using the columns properties
option?



Vihang,

When you set a notnull=True, you should also set a default='' or
whatever value. See this thread:

http://groups.google.com/group/web2py/browse_thread/thread/930401c5e2b2d2e8/89270687601e8d49?lnk=gst&q=annet+dal#89270687601e8d49



Kind regards,

Annet.

[vihang]

Thanks Annet for suitably describing my issue. The issue is that, I
cannot have a default value in the column. I would prefer an error is
raised if the value is not provided... Nevertheless I will try to use
the default for now.

@Massimo, At first I did add the NOT NULL later. But then i tried
afresh with a new database, but that too did not work. I was wondering
whether a default is essential with a notnull, since the databases do
not really need the default.

Thanks

> http://groups.google.com/group/web2py/browse_thread/thread/930401c5e2...
>
> Kind regards,
>
> Annet.

[mdipierro]

yes, for some backends, a default is necessary for notnull.

[annet....@gmail.com]

Vihang,

> I would prefer an error is raised if the value is not provided...

Defining a validator IS_NOT_EMPTY() or IS_IN_DB(...) solves this
problem.

> I was wondering whether a default is essential with a notnull, since the databases do
> not really need the default.

default='' doesn't hurt;-) I did not need a default either and at
first I regarded the default='' as an unsatisfactory solution, but it
solved my problem!?!


Annet

[vihang]

Thanks again.

I will be revising my models to include the default for the foreign
key.