Odoo Help

Welcome!

This community is for beginners and experts willing to share their Odoo knowledge. It's not a forum to discuss ideas, but a knowledge base of questions and their answers.

0

SQL CONSTRAINT ON STOCK.PICKING FIELD OPENERP 7 [SOLVED]

By
Yassine TEIMI
on 12/1/14, 11:42 AM 2,451 views

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 ?

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.

--



--

Cordialement

Yassine TEIMI

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

TEL : 06-56 15 31 47
Yassine TEIMI
on 12/1/14, 1:11 PM

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.

Dhinesh - Technical Consultant, Sodexis Inc
on 12/2/14, 12:07 AM

@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

Yassine TEIMI
on 12/2/14, 5:04 AM

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

Med Said BARA
on 12/2/14, 5:17 AM

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

Yassine TEIMI
on 12/2/14, 5:22 AM

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
on 12/2/14, 5:33 AM

MAKE A BACKUP OF YOUR DATABASE FIRST !!!

Med Said BARA
on 12/2/14, 5:35 AM

@ 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 ?

Yassine TEIMI
on 12/2/14, 5:41 AM

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/

Med Said BARA
on 12/2/14, 5:53 AM

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.

Yassine TEIMI
on 12/2/14, 10:59 AM
0

Ivan

--Ivan--
3210
| 5 3 6
Jakarta, Indonesia
--Ivan--
Ivan
On 12/1/14, 8:10 PM

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.

0
Yassine TEIMI
On 12/1/14, 12:13 PM

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 ?

Change: bl_uniq to num_bl_uniq

Med Said BARA
on 12/1/14, 12:28 PM

How did you defined your field "num_bl" ?

Med Said BARA
on 12/1/14, 12:34 PM

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

Med Said BARA
on 12/1/14, 12:42 PM

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

Med Said BARA
on 12/1/14, 12:57 PM

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.

Yassine TEIMI
on 12/1/14, 1:08 PM

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

Med Said BARA
on 12/1/14, 1:31 PM

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 ?

Yassine TEIMI
on 12/1/14, 1:38 PM

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.

Yassine TEIMI
on 12/2/14, 10:56 AM
0

Med Said BARA

--Med Said BARA--
2664
| 5 5 7
Algeria
--Med Said BARA--
Med Said BARA
On 12/1/14, 12:08 PM

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

            

Your Answer

Please try to give a substantial answer. If you wanted to comment on the question or answer, just use the commenting tool. Please remember that you can always revise your answers - no need to answer the same question twice. Also, please don't forget to vote - it really helps to select the best questions and answers!

About This Community

This community is for professionals and enthusiasts of our products and services. Read Guidelines

Question tools

3 follower(s)

Stats

Asked: 12/1/14, 11:42 AM
Seen: 2451 times
Last updated: 3/16/15, 8:10 AM