[V6.0] restrict, cascade and set null (ondelete options) don't seems to work

[Juan Márquez]

In my db, the foreign keys (ondelete) cascade, set null or restrict isn't working.

I create a field in model tcv_bank_config_detail:

'detail_id':fields.many2one('tcv.bank.config', 'Bank config', required=True, ondelete='cascade'),

I get the table structure from pgadmin, I found:

----------------------------------------------------
-- Table: tcv_bank_config_detail

-- DROP TABLE tcv_bank_config_detail;

CREATE TABLE tcv_bank_config_detail
(
  id serial NOT NULL,
  create_uid integer,
  create_date timestamp without time zone,
  write_date timestamp without time zone,
  write_uid integer,
  bank_journal_id integer, -- Bank journal
  "name" character varying(64), -- Reference
  journal_id integer NOT NULL, -- Journal
  prepaid_tax numeric, -- Prepaid tax (%)
  detail_id integer NOT NULL, -- Bank config
  bank_comission numeric, -- Bank comission (%)
  active boolean NOT NULL, -- Active
  force_detail boolean, -- Force detail
  "type" character varying(16) NOT NULL, -- Type
  use_bounced_cheq boolean NOT NULL, -- Use Bounced cheq
  CONSTRAINT tcv_bank_config_detail_pkey PRIMARY KEY (id),
  CONSTRAINT tcv_bank_config_detail_journal_id_uniq UNIQUE (journal_id),
  CONSTRAINT tcv_bank_config_detail_bank_comission_range CHECK (bank_comission >= 0::numeric AND bank_comission <= 100::numeric)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE tcv_bank_config_detail OWNER TO operp;
COMMENT ON TABLE tcv_bank_config_detail IS 'Details of the deposit bank';
COMMENT ON COLUMN tcv_bank_config_detail.bank_journal_id IS 'Bank journal';
COMMENT ON COLUMN tcv_bank_config_detail."name" IS 'Reference';
COMMENT ON COLUMN tcv_bank_config_detail.journal_id IS 'Journal';
COMMENT ON COLUMN tcv_bank_config_detail.prepaid_tax IS 'Prepaid tax (%)';
COMMENT ON COLUMN tcv_bank_config_detail.detail_id IS 'Bank config';
COMMENT ON COLUMN tcv_bank_config_detail.bank_comission IS 'Bank comission (%)';
COMMENT ON COLUMN tcv_bank_config_detail.active IS 'Active';
COMMENT ON COLUMN tcv_bank_config_detail.force_detail IS 'Force detail';
COMMENT ON COLUMN tcv_bank_config_detail."type" IS 'Type';
COMMENT ON COLUMN tcv_bank_config_detail.use_bounced_cheq IS 'Use Bounced cheq';

--------------------------------------------------------
As you can see, no foreign key is created, then i I delete a tcv_bank_config record his 'detail' isn't deleted.

I have this problem on my db and try to test it on http://runbot.openerp.com/ (6.0):

Found in runbot:

Few field objects (29) have a ir_model_fields.on_delete assigned (only with 'set null' value, none with 'cascade' value),
This field (select) only have 'cascade' and 'set null' options. 'restrict' feature don't exist.

./server/bin/addons/base/ir/ir_model.py: 'on_delete': fields.selection([('cascade','Cascade'),('set null','Set NULL')], 'On delete', help='On delete property for many2one fields'),

Is this field deprecated? I don't have permissions to see this at postgres level (in runbot)

--------------------------------------------------
Back to my db

Now, I look in many other models an found similar situation (no foreign key created) also fee days back, I note a lot of tables without primary key (all from my own modules),I assumed responsibility for the problem and recreate all primary key index by hand. But now I think the problem is much greater and it is in a more internal level my implementation or OpenERP.

Is there any way to "force" foreing keys to be created?

[Juan Márquez (jmarquez)]

Look this bugs too:
https://bugs.launchpad.net/openobject-addons/+bug/1193393

[Juan Márquez (jmarquez)]

https://bugs.launchpad.net/openobject-server/+bug/604347

[Juan Márquez (jmarquez)]

https://bugs.launchpad.net/openobject-server/+bug/733252

[Juan Márquez (jmarquez)]

https://bugs.launchpad.net/openobject-server/+bug/897098

[Juan Márquez (jmarquez)]

I create a module to "FIX" this issue (look in linked branch), but this solution maybe don't work for any other. USE AT YOUR OWN RISK.

[Atul Patel(OpenERP) (atp-openerp)]

Hello,

I have checked this issue with your scenario at my end with latest trunk server
with revno: 4919
i have added one field in test_product model :
        'partner_id': fields.many2one('res.partner', 'Partner', ondelete='cascade')
and you can also use ondelete='restrict' it donot allow parent record to remove if reference is exist in child record.

see below is my pgadmin structure for this table. partner_id 'foreign keys' is created. and when you remove partner id record, this table record is remove also..

-- Table: test_product

-- DROP TABLE test_product;

CREATE TABLE test_product
(
  id serial NOT NULL,
  create_uid integer,
  create_date timestamp without time zone,
  write_date timestamp without time zone,
  write_uid integer,
  date date, -- Date
  prod_qty double precision, -- Product QTY
  partner_id integer, -- Partner
  reject_qty double precision, -- Reject QTY
  qty numeric, -- Quantity
  CONSTRAINT test_product_pkey PRIMARY KEY (id ),
  CONSTRAINT test_product_create_uid_fkey FOREIGN KEY (create_uid)
      REFERENCES res_users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT test_product_partner_id_fkey FOREIGN KEY (partner_id)
      REFERENCES res_partner (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT test_product_write_uid_fkey FOREIGN KEY (write_uid)
      REFERENCES res_users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL
)
WITH (
  OIDS=FALSE
);
ALTER TABLE test_product
  OWNER TO atp;
COMMENT ON TABLE test_product
  IS 'test.product';
COMMENT ON COLUMN test_product.date IS 'Date';
COMMENT ON COLUMN test_product.prod_qty IS 'Product QTY';
COMMENT ON COLUMN test_product.partner_id IS 'Partner';
COMMENT ON COLUMN test_product.reject_qty IS 'Reject QTY';
COMMENT ON COLUMN test_product.qty IS 'Quantity';

so it's working fine in my side.
so I am closing this issue if you still face the problem then
reopen this issue.

Thanks

[Atul Patel(OpenERP) (atp-openerp)]

Hello,

it's working fine in v6.0 also with server6.0 revno:3665.
i have tested with same above scenario and it's generate same pgadmin structure which i mentioned on comment #6.
FOREIGN KEY is created for partner_id field.

so I am closing this issue if you still face the problem then
reopen this issue.

Thanks

[Juan Márquez (jmarquez)]

This is caused by problem in my database, I manually fix it.

[Juan Márquez (jmarquez)]

This bug is closed (for me).

[Twinkle Christian(OpenERP) (tch-openerp)]

Hello Juan,

Thanks for your precious answer.

As per your comment #8 & #9, I am closing this issue.

Regards,