Create table with SQL in OpenEdge

Posted on by

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.

CREATE TABLE asset (
	id integer,
	assetname varchar(100),
	location varchar(100)
);

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.

CREATE TABLE asset (
 id integer pro_description 'Primary key of the table',
 assetname varchar(100) pro_description 'Name of the asset such as HP 1212 printer',
 location varchar(100) pro_description 'Building 1 floor 2 NE port 111'
)
pro_description 'All computer assets within the company';

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.

-- create primary key with the field
CREATE TABLE asset (
 id integer PRIMARY KEY,
 assetname varchar(100),
 location varchar(100)
);

-- create primary key using named constraint at the bottom of the table
CREATE TABLE asset (
 id integer,
 assetname varchar(100),
 location varchar(100),
 CONSTRAINT pk_asset_id PRIMARY KEY (id)
);

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.

-- create unique key with the field
CREATE TABLE asset (
 id INTEGER PRIMARY KEY,
 assetname varchar(100) UNIQUE,
 location varchar(100)
);

-- create unique key using named constraint at the bottom of the table
CREATE TABLE asset (
 id INTEGER,
 assetname varchar(100),
 location varchar(100),
 CONSTRAINT pk_asset_id PRIMARY KEY (id),
 CONSTRAINT uk_asset_name UNIQUE (assetname)
);

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:

create table test (id int, locations varchar(45) array[4]);
insert into test values (1, 'test;best;hello;sad');
select id, locations[2] from pub.test; -- returns best

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

ADD FIELD "ID" OF "TEST" AS integer 
  FORMAT "->,>>>,>>9"
  INITIAL "?"
  POSITION 2
  MAX-WIDTH 4
  ORDER 10

ADD FIELD "LOCATIONS" OF "TEST" AS character 
  FORMAT "x(8)"
  INITIAL "?"
  POSITION 3
  MAX-WIDTH 188
  EXTENT 4
  ORDER 20
  CASE-SENSITIVE

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.

CREATE TABLE asset (
 id integer,
 assetname varchar(100),
 location varchar(100),
 created DATE DEFAULT SYSDATE,
 created1 TIME,
 created3 TIMESTAMP DEFAULT SYSTIMESTAMP,
 created4 TIMESTAMP WITH TIME ZONE DEFAULT systimestamp_tz,
 CONSTRAINT pk_asset_id PRIMARY KEY (id),
 CONSTRAINT uk_asset_name UNIQUE (assetname)
);

INSERT INTO asset (id, assetname, location, created1) VALUES (1, 'printer1', 'building1', systime);
SELECT * FROM asset;

ID |ASSETNAME |LOCATION  |CREATED    |CREATED1 |CREATED3            |CREATED4                        |
---|----------|----------|-----------|---------|--------------------|--------------------------------|
1  |printer1  |building1 |2017-12-02 |19:53:54 |2017-12-02 19:53:54 |2017-12-02 19:53:54:445 - 06:00 |

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

CREATE TABLE asset (
	id integer,
	assetname varchar(100),
	location varchar(100),
	has_lock char(1) DEFAULT 'N',
	created DATE DEFAULT SYSDATE,
	created1 TIME,
	created3 TIMESTAMP DEFAULT SYSTIMESTAMP,
	created4 TIMESTAMP WITH TIME ZONE DEFAULT systimestamp_tz,	
	CONSTRAINT pk_asset_id PRIMARY KEY (id),
	CONSTRAINT uk_asset_name UNIQUE (assetname),
	CONSTRAINT ck_asset_lock CHECK (has_lock IN ('Y', 'N'))
);

-- this will fail with constraint violation
INSERT INTO asset (id, assetname, location, created1, has_lock) VALUES (1, 'printer1', 'building1', SYSTIME, 'T');

-- this will succeed
INSERT INTO asset (id, assetname, location, created1, has_lock) VALUES (1, 'printer1', 'building1', SYSTIME, 'Y');

SELECT * FROM asset;

ID |ASSETNAME |LOCATION  |HAS_LOCK |CREATED    |CREATED1 |CREATED3            |CREATED4                        |
---|----------|----------|---------|-----------|---------|--------------------|--------------------------------|
1  |printer1  |building1 |Y        |2017-12-02 |20:03:39 |2017-12-02 20:03:39 |2017-12-02 20:03:39:165 - 06:00 |

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.

CREATE TABLE hospital (
 id integer PRIMARY KEY,
 name varchar(100) unique
);

CREATE TABLE hospital_address (
 id integer PRIMARY KEY, 
 address varchar(500),
 hospital_id integer NOT NULL,
 CONSTRAINT fk_hospital_address FOREIGN KEY (hospital_id) REFERENCES hospital(id)
);
CREATE INDEX ix_hospital_address_id ON hospital_address(id);

-- these 2 will work
INSERT INTO hospital VALUES (1, 'John Hopkins');
INSERT INTO hospital_address VALUES (1, 'somewhere address', 1);

-- this will fail because hospital table doesn't have ID 2
INSERT INTO hospital_address VALUES (2, 'somewhere address', 2);

-- trying to delete parent that has child records will fail
DELETE FROM hospital WHERE id = 1;

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.