This question has been flagged
3 Replies
6461 Views
Avatar
TEIMI Yassine

The objects stock.picking.out (Delivery orders) and stock.picking.in (Incomings Shipments), inherits from stock.picking by prototype inheritance, So as to customize the stock.picking.out form view, I added a field on stock.picking, and made it visible juste for stock.picking.out, but now I want to add a sql_constraints unique, to that field, I wrote on stock.picking where the field is decalred : 

_sql_constraints = [('bl_uniq','unique(num_bl)','Physical Delivery order number is unique !')].

But this sql constraint doesn't work, is it due to the prototype inheritance ? if yes what is the solution to overcome this issue ?

Avatar
Discard
OdooBot
I have tried this , but without sucess ...

2014-12-01 17:37 GMT+00:00 Dhinesh <dvdhinesh.mail@gmail.com>:

A new answer for SQL CONSTRAINT ON STOCK.PICKING FIELD OPENERP 7 has been posted. Click here to access the post.

 --

Dhinesh
Sent by Odoo S.A. using Odoo about Forum Post SQL CONSTRAINT ON STOCK.PICKING FIELD OPENERP 7



--

Cordialement

Yassine TEIMI

Ingénieur SI-BI & Consultant technico-fonctionnel ERP 

TEL : 06-56 15 31 47
Dhinesh

Hi, 1) Check whether your table already has any null or duplicated value in it. Constraint will not be applied if you have null or duplicated value in column. 2) Check whether your constraint is applied to the table or not. Pgadmin -> Db -> Table -> Constraints -> Your Constraints.

TEIMI Yassine
Author

@Dhinesh : I've done a select on "num_bl", the field I want to be unique, it doesn't display any data, while, I am sure I supplied data to this field using the webclient. Is this means that the columns is NULL ? How to access constraints of a table on command line, i'm on ubuntu. @ John: Sorry I don't have enough karma to comment your answer. I performed the sql query that you gave me, but it did'nt work, taking in consideration that the relation is : stock_picking ( the sql table ), the error displayed is : column "stock_picking.num_bl" must appear in the GROUP BY clause or be used in an aggregate function

Med Said BARA

You should read this:https://www.odoo.com/forum/help-1/question/why-sql-constraints-not-working-39549 especially the comment from Ray CARNES

TEIMI Yassine
Author

@Said : Yes, I have alreadu duplicated data for the field num_bl, should I remove all duplicated data and apply the unique constraint ?

Med Said BARA

Yes, but use it carefully (to avoid any loss of data). The best way to do it is to use a tool like PgAdmin3, and change (or delete) the content of your records. MAKE A BACKUP OF YOUR DATABASE FIRST !!!

Med Said BARA

MAKE A BACKUP OF YOUR DATABASE FIRST !!!

TEIMI Yassine
Author

@ said : I've already done a backup, I'm working on a test database, one last question , I have also empty values for that field num_bl, according to the advice of Dhinesh, it will cause the same problem no ?

Med Said BARA

I think NO: http://www.postgresql.org/docs/9.0/static/indexes-unique.html and https://www.pgrs.net/2008/01/11/postgresql-allows-duplicate-nulls-in-unique-columns/

TEIMI Yassine
Author

Yes you're right, thank you guys : @said @john @dhinesh, it worked fine. the cause was the duplicated data on the unique field, null duplicated values don't affect the work of constraint unique. adding to that I was struggling with the field, That i resolved with john in another topic. Thanks.

Avatar
Ivan
Best Answer

If unique constraint is to be applied to a table with data, you need to ensure that the table indeed have that column unique (NULL values are not considered).  Try to check the following SQL: SELECT num_bl, COUNT(*) FROM stock.picking HAVING COUNT(*) > 1 and see if there are records aside from NULL value.

Avatar
Discard
Avatar
Med Said BARA
Best Answer

Try with:  _sql_constraints = [('bl_uniq', 'unique (num_bl)', 'Physical Delivery order number is unique !')]

            

Avatar
Discard
Avatar
TEIMI Yassine
Author Best Answer

This is what I tried at first, I just made a mistake while posting my question, I corrected it. do you have any idea how this can be done in my case ?

Avatar
Discard
Med Said BARA

Change: bl_uniq to num_bl_uniq

Med Said BARA

How did you defined your field "num_bl" ?

Med Said BARA

First check if the field "num_bl" is created in your table.

Med Said BARA

Second, Check for related errors to sql_constraint violation in your openerp-server.log.

TEIMI Yassine
Author

I created the field on the top class, "stock.picking", because the inheritance is of type prototype, I changed to num_bl_uniq without any change, I'll have to check the stock.picking table on postgres, if the field is there, it appears on the interface anyway.

Med Said BARA

Yassine, check for any errors message related to sql_constraint violation in your .log file

TEIMI Yassine
Author

I have a problem with my log file, it's on /var/log/openerp/openerp-server.log, it does'nt display any error, maybe I have to run the openerp server to check the error ?

TEIMI Yassine
Author

Yes you're right, thank you guys : @said @john @dhinesh, it worked fine. the cause was the duplicated data on the unique field, null duplicated values don't affect the work of constraint unique. adding to that I was struggling with the field, That i resolved with john in another topic. Thanks.