SQL Constraints in Odoo 17 - Odoo 17 Slides

SQL constraints are fundamental for maintaining data integrity and enforcing business rules. Since Odoo utilizes PostgreSQL as its default database engine, SQL constraints can be implemented directly in PostgreSQL through Odoo's ORM (Object-Relational Mapping) layer.

1. SQL Constraints in Odoo 17 Enterprise

2. Introduction Enterpr ise SQL constraints are fundamental for maintaining data integrity and enforcing business rules. Since Odoo utiliz es PostgreSQL as its default database engine, SQL constraints can be implemented directly in PostgreSQL through Odoo's ORM (Object-Relational Mapping) layer. These constraints primarily serve the purpose of validating record data before it is persisted in the database.

3. Enterprise SQL constraints are defined using the Model attribute _sql_constraints . This attribute is assigned as a list of triples containing A string, that is the name The SQL definition The message _sql_constraints = [name, sql_def,message] The name is the valid SQL constraint name, SQL constraint is a table constraint and the message is the error message which is needed to get popped out.

4. Enterprise unique prohibits data duplication for a specified field. This constraint is associated with a field name, preventing duplicate entries for that particular field. We have a module in which there is a model which stores the details of college students for Admission. Let’s define a field student_code which must be set as unique. unique

5. Enterprise Here, to ensure that each record has unique student_code, we can specify that field as unique and the code snippet will look like _sql_constraints = [ ('code_uniq', 'unique (student_code)', "The Student ID must be unique,this one is already assigned to another student."), ]

6. Enterprise When we try to create a new record with same value for the Student code field, the error will pop out like

7. Enterprise check constraint c onstraint within _sql_constraints examines SQL expressions on the data to ensure adherence to specified conditions. In our previous example, let’s make a check to set the Age to have only positive numbers. The code will be as check _sql_constraints = [ ('code_uniq', 'unique (student_code)', "The Student ID must be unique, this one is already assigned to another student."), ('positive_age', 'CHECK(age > 0)', 'The age should be greater than 0.') ] All the constraints can be added by separating with commas as this.

8. Enterprise When we try to create a new record with value for Age as zero, the error will be generated as

9. For More Info. Check our company website for related blogs and Odoo book. Check our YouTube channel for functional and technical videos in Odoo. Enterprise www.cybrosys.com