Mitsubishi Electric MELSEC iQ-R-R00CPU - Appendix 14 List of Available SQL Commands for CPU Module Database Access Function

996
APPX
Appendix 14 List of Available SQL Commands for CPU Module Database Access Function
Appendix 14List of Available SQL Commands for CPU
Module Database Access Function
This section describes the available SQL commands for the CPU module database access function.
Data Definition
CREATE TABLE
This command creates a table in the database.
■Option
■Application example
• To create table1 (fld1 (integer type, primary key (key name: pk1)), fld2 (integer type), fld3 (character string type (120
characters, NOT NULL)))
CREATE TABLE "table1" ("fld1" INT PRIMARY KEY "pk1", "fld2" INT, "fld3" NLSCHAR (120) NOT NULL);
• To create table2 (fld1 (integer type, foreign key (key name: fk1, reference: table1 fld1)), fld2 (BOOLEAN type), fld3 (single-
precision real number type))
CREATE TABLE "table2" ("fld1" INT FOREIGN KEY "fk1" REFERENCES "table1" ("fld1"), "fld2" BOOLEAN, "fld3" REAL);
■Precautions
• To define the NLSCHAR field, check the data size of the field.
• Define the NLSCHAR type with the size specified in NLSCHAR = (n+1)  3
*1
since it needs to be defined with the data
size. For example, the definition is 39 characters for "fld3" NLSCHAR (120) of table1 in the application example.
*1 n indicates the number of characters.
DROP TABLE
This command deletes the specified table in the database.
■Application example
• To delete table 1
DROP TABLE "table1";
Syntax
CREATE TABLE [table name] ([field name] [data type] [option], [field name] [data type] [option], , [field name] [data type] [option]);
Item Description Syntax
Primary key
constraint
Maintains the uniqueness of a value stored in the field. (NULL is not
available.) Only one primary key constraint can be set per table.
Key names must be up to 16 single-byte alphabetical characters and
are case sensitive.
PRIMARY KEY [key name]
Foreign key
constraint
Refers to the value of a field of another table. Only the field with the
name where the primary key constraint of another table is set can be
set as the reference field. (Always set the same field name to the
reference field and own field.)
Key names must be up to 16 single-byte alphabetical characters and
are case sensitive.
FOREIGN KEY [key name] REFERENCES [reference table
name] ([reference field name])
NOT NULL
constraint
Disables storing NULL values. (Always store a value other than NULL.) NOT NULL
Syntax
DROP TABLE [ table name];