Columns

The Schema View page of the table displays the columns that are created for the table. As mentioned in the previous section, when a table is created, a set of columns are automatically created for it by default.


Data Store- Schema View

Default Columns

The four default columns that exist in every table are:

  • ROWID: Represents the unique ID or the Primary Key of a particular record in the table. When you add a new record, the ROWID value for the record is created automatically.
  • CREATORID: Represents the unique ID of the Catalyst account user.
  • CREATEDTIME: Specifies the time a particular record was created.
  • MODIFIEDTIME: Specifies the time a record was last modified.

In addition to these four default columns, any new columns you create will be listed in the Schema View page under the default columns.

Note: You cannot modify the values in the ROWID and CREATORID columns.

Column Characteristics

Every column in the Catalyst Data Store contains a set of characteristics that define the column’s structure and behavior. The seven column characteristics are:

  1. Column ID: The unique ID of a particular column in the table. When you add a new column, the Column ID value for the record is created automatically. You cannot modify the Column ID value.
  2. Column Name: The name of the column.
  3. Data Type: Catalyst supports the following data types:
    • Text: Long-form text data type that accommodates more characters than the Var Char data type, with a maximum length of 10,000 characters.
    • Var Char: Variable-length character data type that can hold letters and numbers, with a maximum length of 255 characters.
    • Date: Date value in the format YYYY-MM-DD
    • DateTime: Date and time values in the format YYYY-MM-DD and HH:MM:SS
    • Int: 4-byte integer data type, with a maximum length of 10 digits.
    • Double: Double precision floating-point number, with a maximum of 17 digits including the decimal part
    • Boolean: Boolean values can be either ’true’ or ‘false’
    • BigInt: 8-byte integer data type, with a maximum of 19 digits
    • Foreign Key: Refers to the Primary Key of another table
    • Encrypted text : Encrypted long-form text data type, with a maximum length of 10,000 characters.
  4. Default Value: If the value for a column is left empty by the user, a default value is set for the column.
  5. Search Index Constraint: If the Search Index constraint is enabled for a column, it will create a full-text search index for the column in the table. This allows the column to be search-enabled while using Catalyst Search: integration in your application.
  6. IsUnique Constraint: If the IsUnique constraint is enabled for a column, it will mark the column as containing a unique value, and eliminate the possibility of duplicate values for the same column in the table.
  7. IsMandatory Validator: If the IsMandatory validator is enabled for a column, the column will need to contain a non-empty data value. If you attempt to skip adding a value to the column where the IsMandatory validator is enabled, it will generate an error message.
  8. PII/ePHI Validator: The Personal Identifiable Information (PII)/Electronic Protected Health Validator (ePHI) indicates sensitive user data that needs to be stored and managed in compliance with all relevant data and privacy protection laws, such as HIPAA and GDPR guidelines. Examples of such sensitive data include Identifiable Health Information (IIHI), personal and contact information such as name, address, and telephone numbers, medical history and information including Medical Record Number (MRN) and Medicare Beneficiary Identifier (MBI), identity information such as Social Security Number and vehicle identifiers and any other user data that falls under this scope. You must enable the PII/ePHI validator for columns that contain sensitive user data.

    If this validator is enabled for a column, Catalyst account admins and project members with the necessary permissions will be able to view all activities performed in these columns, in the Application Logs section of Audit Logs in the console. The activities include creation, updation, or deletion of rows in the sensitive columns.

Some of these column characteristics, such as the IsUnique and the IsMandatory are available for multiple data types. The Search Index constraint and Default Value is available for all data types except Text. The PII/ePHI validator is available for all data types.

The Var Char data type includes an additional characteristic called Max Length which lets you define the maximum length of characters allowed to be entered. The highest value for Max Length supported by Catalyst is 255 characters. You will not be able to provide a higher value. When you edit this value, you must always provide a greater value for Max Length than the previous Max Length value.

Likewise, the Foreign Key data type includes two additional characteristics:

  1. Parent Table: The table whose primary keys should be referred
  2. On Delete: The action that must be performed when the primary key column in the parent table is deleted. It includes two options: Null and Cascade.
    If you select Null, the foreign key’s value will be set to null, upon the deletion of the primary key column in the parent table. If you select Cascade, the foreign key column will be deleted entirely upon the deletion of the primary key column in the parent table.

The values of all the column characteristics are pre-populated in the four default columns in every table (ROWID, CREATORID, CREATEDTIME, MODIFIED TIME), except for the Default Value characteristic. The Column ID values are automatically created for the default columns and the Default Value characteristic is null.

The pre-populated values for the other column characteristics for the default columns are:

Column Name Data Type Search Indexed IsUnique IsMandatory PII/ePHI
ROWID BigInt False False False False
CREATORID BigInt True False False False
CREATEDTIME DateTime True False False False
MODIFIEDTIME DateTime True False False False

Create a New Column

Note: Catalyst allows you to create upto 100 columns per table in the development environment. You can request Catalyst for an increase in this limit by contacting our support at support@zohocatalyst.com. We will address each request on a case-by-case basis. There are no upper limits for column creation in the production environment.

To create a new column for a table in the Data Store:

  1. In the Schema View section of the table, click [+New Column].
    Data Store- Create a Column
  2. Enter the Column Name and choose the Data Type from the drop-down list.
    Data Store- Create a Column
  3. Enter the required details for the data type and enable the required constraints and validators.
    Create a Column
Note: The column name can only contain alphanumeric characters and certain specific characters. Do not include white spaces in it.
4. Click Create.

The column is now created and listed in the Schema View page of the table along with its characteristics. You can use the search box to search for a particular column by its name.

Data Store- Column Created

Edit a Column

You can edit certain characteristics of a column in a table from the Schema View page. The characteristics that cannot be edited include the Column ID, the Data Type and the IsUnique constraint once the column has been created.

To edit a column in a table in the Data Store:

  1. In the Schema View section of the table, click the ellipsis icon for the column that needs to be edited, then click Edit.
    Data Store- Edit a Column
  2. Make the necessary changes and click Update.
    Data Store- Edit a Column

Delete a Column

To delete a column in the Data Store:

  1. In the Schema View section of the table, click the ellipsis icon for the column that needs to be deleted, then click Delete.
    Data Store- Delete a Column
  2. Click Yes, Proceed in the confirmation window pop-up.
    Data Store- Delete a Column

The column is now deleted from the table.


Last Updated 2023-06-13 12:35:44 +0530 +0530