Contents
Strategic Partner Links
Sepasoft - MES Modules
Cirrus Link - MQTT Modules
Resources
Knowledge Base Articles
Inductive University
Forum
IA Support
SDK Documentation
SDK Examples
A primary key is a way to uniquely identify each row in a table. While it is possible to create a database table without a primary key, it is highly recommended to configure one for each table. A primary key is comprised of either a single column, or set of columns. When multiple columns are specified as a primary key, this is known as a composite primary key. No two distinct rows in a table can have the same value (or combination of values) in those columns.
While Primary Keys can be configured in several ways, they typically meet the following criteria:
Indexes speed up the querying process by providing swift access to rows in the data tables, similarly to the way a book’s index helps you find information quickly within that book. Indexes are extremely important when querying large sets of data. You should create an index for the set of columns you use commonly in a WHERE
clause. For example, you should add an index on the timestamp column of a historical table when querying the table by a start and end date. Ignition does this automatically when it creates tables for Tag History or Transaction Groups.
A Foreign Key is a referential constraint between two tables. The foreign key identifies a column or a set of columns in one (referencing) table that refers to a column or set of columns in another (referenced) table. The columns in the referencing table must be the primary key in the referenced table. For example, you might have a list of suppliers with an integer ID column. Then your invoices would use the supplier's ID instead of the name. These linked tables can save a lot of space because you don't have to include everything about the supplier in each invoice.
Supplier (SupplierNumber, Name, Address, Type)
Invoices (InvoiceNumber, SupplierNumber, Text, Cost)
NULL is a special marker used in SQL to indicate that a data value does not exist in the database. This way it is clear that there is no data, instead of guessing if a value of 0 is correct or just missing data. By definition, NULL is not equal to anything, even other NULL values. Every database has a function to identify if a value is NULL, usually called isNULL() or something similar.
Comments can be added to any SQL query just like with scripting. Single line comments are done with two dashes and a space: '-- '
-- This is a single line comment in a SQL query. SELECT * FROM my_table
You can also do multi line comments by wrapping text within a forward slash and an asterisk: /* text */
/* A multi line comment can span multiple lines. The comment will stop when it is closed with another asterisk and forward slash.*/
SQL queries (or statements) are used to create, maintain, and view relational databases like MySQL, SQLServer, Oracle, etc. They follow a specific format and use just key words to determine the structure of the query. Unlike most coding languages, SQL does not rely on newlines or start/end markers for it's format, each query is a single line of code. You will often see SQL queries split over several lines, but that is just to make them easier to read.
The SELECT statement is used to select data from a database. The result is returned as a data set, called the result set. This is true even if there is only one value returned. The syntax for a SELECT statement is as follows:
SELECT myColumn FROM myTable
The "*" character can be used to specify all columns from a database table. While this is the easiest way to retrieve results from a table, this is not the recommended approach.
SELECT * FROM Customers
The recommended approach is to instead specify only the columns that are required for a query. There are several reasons for this, but performance would be the main one: less columns in a statement means less work for the database, and the resulting data set in Ignition will use less memory.
SELECT Name FROM Customers
SELECT Name, Address FROM Customers
The WHERE clause is used in conjunction with other commands to extract only those records that fulfill a specified criterion. The WHERE clause usually goes near the end of the query followed by a condition that the values must meet to be returned.
SELECT myColumn FROM myTable WHERE condition
The WHERE clause can use various operators for its condition, with the basic operators being:
Operator | Description |
---|---|
= | Equal to. |
<> | Not equal to. |
> | Greater than. |
< | Less than. |
>= | Greater than or equal to. |
<= | Less than or equal to. |
Only return customers from CA.
SELECT * FROM Customers WHERE State = 'CA'
Only return users over a specified age.
SELECT * FROM Users WHERE Age > 25
The INSERT INTO statement is used to insert a new row in a table. If any columns have default values or are auto-incrementing, they can be omitted from the INSERT query.
INSERT INTO myTable (column1, column2) VALUES ('Value1', 'Value2')
INSERT INTO Customers (Name, Address, City, State, Zip, Country, Phone) VALUES ('Inductive Automation', '90 Blue Ravine', 'Folsom', 'CA', '95630', United States, '1-800-266-7798')
If inserting a value into every column of the table, the columns do not need to be listed on the INSERT INTO statement. The values just need to be listed in the same order as the columns in the table. The table in the query below has four columns: id, first name, last name, and title.
INSERT INTO Users VALUES (5628, 'Bob', 'Smith', 'Project Manager')
The UPDATE statement is used to update existing records in a table. If a WHERE clause is not used, all rows in the table will be updated. As a result, the UPDATE statement should be used in conjunction with a WHERE clause in most cases. Many official management tools like SQLServer's Management studio will not allow UPDATE commands without a WHERE clause.
UPDATE myTable SET myColumn = 'myValue'
UPDATE Customers SET Name = 'Inductive Automation'
UPDATE Customers SET Address = '2110 21st Street' WHERE ID = 1
The DELETE statement is used to delete records in a table. NEVER run a delete command without a WHERE clause. It will delete ALL records from that table. Many official management tools like SQLServer's Management studio will not allow DELETE commands without a WHERE clause.
DELETE FROM myTable WHERE myColumn = value
DELETE FROM Customers WHERE Name = 'Inductive Automation'
DELETE FROM Customers WHERE id < 538
In This Section ...