Following the IBM i GYMN activities ( see the main post: https://linkedibmi.com/2018/01/18/ibm-gymn-come-train/ ), I started with details about the database created by DDL and SQL instructions and by the use of “System i Navigator” ( if you need to install it follow this post: https://linkedibmi.com/2018/01/19/ibm-gymn-system-navigator-installation/ ).
In the previous post:
I discussed a bit about the the base modalities to create and elaborate tables on our schema. Usually we use the following commands:
CREATE TABLE – LABEL ON – ALTER TABLE – COMMENT ON – CREATE ALIAS – DROP TABLE
for example by using STRSQL on native IBM i screen but in the post I showed you the same operations by the use of “System i Navigator”.
In this post I will discuss about the way to add keys, constraints and execute query on our table.
If you open the table recently created:
A) You could access to the third tabbed: “Vincoli chiave” in this image. This tabbed let syou define primary and unique key on your table, by using the checkbox “Tipo di vincolo”. You could select the field and add it on the right side, under the “selected column” area:
Or you could remove the key on the field:
If you save, the list of defined keys will be showed:
And you could view the SQL instruction that could be used for this operation:
B) If you access into the fourth tabbed (“Vincoli chiave esterna”), you could define external keys on your table:
After you save, the list of the keys added will be showed:
and as the usual you could see the corresponding SQL instruction:
You should note than ON DELETE and ON UPDATE of your table you could add constraints on the depending tables by using the following combobox (see the help for detail about each case):
The following list will be showed after the save:
C) The next tabbed (“Vincoli di controllo”) lets you define constraints for the fields you defined on your table. You could use both Operators and Functions to define the best control condition you could use:
So, after you select the field and the operators, your control condition is showed entirely and listed after the save:
D) The last tabbed I want to analyze is “Query materializzata”. It lets you define a custom query on your table and execute it. As alternative you could use an SQL Assistant (see on the right) that will help you to define your query:
The assistant lets you select the SQL instruction to use:
and lets you select the tables available:
At the end the SQL instruction is showed in the previous panel, and you could execute it or check it:
The execution will show you the defined fields with loaded data: