Best Practices

Adding a Default Value to a Column

When you are customizing a column in a table schema of your customization project by using the Database Scripts page of the Customization Project Editor or a custom SQL script, we recommend that you not specify a default value for this new column by using the approach described in To Add a Custom Column to an Existing Table. The database engine does not consistently enforce default values at the database level, so adding a default value to a column of an existing table schema does not guarantee that it will be applied when the database is created or upgraded.

We recommend that you specify the default value for a column in a DAC by using the PXDefaultAttribute or PXDBDefaultAttribute attribute instead. (For usage examples of these attributes, click their respective links.)

If you want to add a new column with a default value to a table schema that has existing records, you need to write a custom SQL script that adds this new column with the specified default value, and updates this default value for all existing records.

The data for some entities in MYOB Acumatica, such as generic inquiries and reports, can be defined and stored in XML format. These entities are described with XML definitions in the /App_Data/XmlExportDefinitions folder of your application. If you define a new column that does not accept Null values for such an entity, you will need to create a C# class that implements the XMLEntityUpgrader interface. This class should define the upgrade logic to update all existing records with the specified default value for the new column.