Maintaining Tables and Other SQL Schema Elements

NOTE: Building IDOs over tables in schemas other than dbo is not currently supported.

This topic includes these procedures:

Creating Tables
Maintaining Columns on Tables
Specifying Primary Keys and Other Constraints for a Table
Updating Existing Tables
Editing SQL User-Defined Data Types
Executing SQL Statements

Creating Tables

To create a SQL table:

In the SQL Tables form, select Actions > New and specify the table name
Verify that the schema is dbo.
Indicate whether the table includes a multi-site column.
Save the record.
Click Columns.
In the SQL Columns form, add columns for the new table and define metadata about the columns such as the data type, length, and default value (when applicable to the data type).
Save the columns and return to the SQL Tables form.
Click New Constraint to open the SQL Tables Constraint form and define one or more primary keys or other constraints for the table, as described below.
To save the constraint and return to the SQL Tables form, click OK.

After you create tables or columns, you can create IDOs, IDO extension classes, or events that use the tables and columns. You can also filter for a table in the SQL Tables form, and alter the columns and other attributes.

NOTE: SyteLine requires certain columns on tables that it uses. If you import a table into your database, you can filter for it in the SQL Tables form, and then click Update Current Table to add those required columns.

Maintaining Columns on Tables

Use the SQL Columns form to add, delete, or modify columns on tables. However, you cannot make changes to certain restricted tables.

WARNING: Do not delete or modify columns on base application tables. Doing so can cause system instability.

You can also change the definition of an existing column, for example, the data type.

Specifying Primary Keys and Other Constraints for a Table

To define one or more primary keys or other constraints:

On the SQL Tables form, click one of these buttons:
- To specify a new constraint, click New Constraint. Then specify a Constraint Type and related information:
  - Primary Key: Specify whether the constraint should be implemented with the clustered attribute.
  - Index: Specify whether the constraint is unique (that is, only one unique combination of the columns contained in the constraint is permitted in the table) and whether the constraint should be implemented with the clustered attribute.
  - Foreign Key: Specify the name of the table to which the current table refers.
- To change an existing constraint, select the constraint from the grid and click Modify Constraint.
  The data type and constraint name display.
Click Next.
In the left pane, select the column or columns you want to be constraints on the table. To add them to the Keys pane, click Add.
Click the Move Up or Move Down buttons to change the order of the columns on the constraint.
To delete an existing constraint, remove all columns in the right pane.
To save your changes, click Finish.

Updating Existing Tables

On the SQL Tables form, use the Update Current Table button to prepare an existing table for access by SyteLine. Select the table and click the button to perform the following on the table:

Add the standard Mongoose application columns such as Create Date, Updated By, and so on
Create the Delete, Insert and Updatepenultimate triggers
Add application schema table metadata

This is useful when you have a table that you imported into the application database (that is, a table not created with the SQL Tables form).

Editing SQL User-Defined Data Types

To create or edit data types from the SQL Tables form, click SQL Data Types and then:

Select Actions > New.
Specify the name, base data type, length and precision (if applicable for your base data type) and nullability
Save the record.

To change a data type, SQL Server requires that the data type be dropped and recreated. To drop a data type, it must not be in use by a table, stored procedure, or function.

Filter for the data type and verify that the Where Type Used grid is empty.
Select Actions > Delete and save the record to drop the data type.
Select Actions > New, specify the information again, and save the data type record to recreate it.

Executing SQL Statements

From the SQL Tables form, click Apply Database to display a form where you can execute any SQL statement into the application database. Specify the SQL statement to execute and click Submit. If your statement includes a "GO" on a line by itself, everything above it is submitted separately to the database.

The Apply Database form must be used only by experts.