Sugarcrm version 9 and 10 not allowing to Add a Not Null Column in Oracle DB

Hi all,

I am facing a strange issue. The issue is I added a new flex relate field in the Vardefs. After adding it when I am trying to deploy this package on a Sugar Instance of version 9 and 10 which is configured with Oracle DB on Repair and Build it is generating a query "Alter table myTable ADD myColumn varchar(255) NOT NULL" and it is throwing error on "Not Null" property. It is not allowing to add a "Not Null" type constraint.

It is working fine on all the remaining databases i.e MYSQL, SQL and DB2 even with the Oracle DB with Sugar 8 version but it is not supporting this constraint for other Sugar Versions during the alter table. I have double-checked all the possible configurations and all have the same.

Can anyone please guide me on how I can resolve this issue that this package will ultimately support all versions of Sugar with all type of databases?

0 Junaid Usman over 4 years ago

Hi Maryam,

How did you added this flex relate field in the Vardefs? Did you created this field in a local SugarCRM instance with the MySQL database?

In Oracle DB you can not create a new column with a "Not Null" constraint if rows exist in the table. It will through the following standard error message.

ORA-01758: table must be empty to add mandatory (NOT NULL) column

I would suggest creating this field in the CRM instance using oracle DB so that sugar generates a proper alter query as per the oracle DB standards.

Kind Regards,

Junaid
0 Maryam Aslam over 4 years ago in reply to Junaid Usman

Hi, Junaid Thank you for the response.

I generated this field via a YML file and this flex relate field is a required field that is why the "NOT NULL" constraint was generated at the DB level which you right that Oracle DB is not supported.

So now I have modified the approach for making it required and now it is working fine :)