Create table with SQL in OpenEdge

Create table with SQL in OpenEdge

One of the most basic and important database objects is tables. We can create tables with either ABL or SQL. This article discusses how to create table with SQL and the various datatypes and constraints that can be used to create them. Documentation of create table is covered quite well on https://documentation.progress.com/output/ua/OpenEdge_latest/index.html#page/dmsrf/create-table.html.

create table with sql in openedge

Primary key and unique key in OpenEdge

Progress OpenEdge has primary and unique key constraints. SQL primary key are unique and not null, similar to many other modern databases.

Basic table without any keys or constraints

Let’s create a table with no keys or constraints. This table will hold asset information in a company.

Notice the similarity in syntax compared to create table in MySQL, SQL Server, Oracle and PostgreSQL. Developers used to these modern databases will find it really easy to build tables in OpenEdge.

The database creates a rowid automatically in the background. Each row gets a rowid.

Adding comments to tables and fields

Now, let’s drop and create the table again but with description.

Notice the pro_description keyword used to provide description for the table and each of the columns. There are PRO keywords that can be used in ABL and SQL alike. These OpenEdge SQL keywords for ABL table attributes are well documented.

Add primary key to the table

It is very easy to add a primary key to the table during creation.

Adding primary key after the datatype tells OpenEdge to make that field a primary key. Alternately, named constraint can be provided at the bottom of the field declaration like the 2nd example above.

Add unique key to to the table

Similar to primary key, it is easy to add a unique key to the table during creation.

Adding unique after the datatype tells OpenEdge to make that field a unique key. Alternately, named constraint can be provided at the bottom of the field declaration like the 2nd example above.

Array datatype or Extent datatype in OpenEdge

OpenEdge has a datatype called array. You can store semi-colon delimited data in it. Data can be extracted using integer position. Let’s take an example:

ABL calls array datatype extent. Here’s how ABL declares array datatype:

An array or extent datatype helps in storing CSV-like structure. For example, user preferences can be stored in such field or monthly sales can be stored.

Default value for a column

You can provide a default value for a column. Doing so might be really useful for columns such as created, updated etc. So, let’s try to examine how we can add default constraints.

Notice that default for time cannot be provided. An error is reported when default of systime is provided for time datatype. As a workaround, provide systime during the insert process itself.

You’d think that primary key column could be provided a default auto_increment or sequence_name.nextval. But, that’s not possible with OpenEdge. Primary key’s value is best provided in the insert statement.

Check constraint

You can also provide a check constraint on a table level or column level

Having a check constraint allows only reasonable data to be inserted, especially when the datatype allows a user to enter versatile data. In the above example, though we have a char(1) column datatype that can hold many different characters, we are restricting the data entry to either Y or N.

Note that has_lock char(1) check (has_lock in ('Y', 'N')) will also work instead of explicitly providing a constraint at a table level.

Referential integrity constraint

It is really important to protect parent-child relationship within a database with referential integrity constraints. When declaring a child table, create a foreign key relationship with the parent table to prevent orphaned records. Let’s take an example of that.

You’ll notice that referential integrity constraints eliminates the changes or orphaned records.

OpenEdge can be used effectively with SQL, especially when your staff is much more SQL-centric than ABL-centric. ABL does not honor check constraints and referential integrity. So, if you have a mix of SQL and ABL know that your data may not stay pristine if ABL chooses to violate constraints deliberately.

Be sure to see other topics listed on Modern web applications and API with OpenEdge, SQL, ODBC and PHP.