Writing SQL Queries

Primary Key

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:

• Integer Datatype: The datatype of the key column is typically an integer, and not a varchar. The primary key is only an identifier to a specific row in a table, so an integer datatype can easily be used. Some databases support a UID or a UUID (Universally Unique IDentifier) that looks like a character string, but is something specially made for primary keys.
• Automatically Incrementing: The value of the primary key increments as rows are added. The key is usually configured to automatically increment in the database so that external applications (such as Ignition) don't have to figure out the next available value when inserting a new row. 
• Statically Defined: Any row that is inserted must fill in these value(s) without creating duplicates. Configuring the primary key as automatically incrementing means that the database will automatically handle this criteria.
• Non-NULL: NULL (empty) values should not be present in the primary key. This column (or columns) will usually not allow NULL values.

Index

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.

Foreign Key

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.

Example

Supplier (SupplierNumber, Name, Address, Type)
Invoices (InvoiceNumber, SupplierNumber, Text, Cost)

Null Value

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

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

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.

Select Command

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

Select Examples

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

Where Clause

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:

Where Examples

Only return customers from CA.

SELECT * FROM Customers WHERE State = 'CA'

Only return users over a specified age.

SELECT * FROM Users WHERE Age > 25

Insert Into Command

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 Examples

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')

Update Command

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 Examples

UPDATE Customers SET Name = 'Inductive Automation'

UPDATE Customers SET Address = '2110 21st Street' WHERE ID = 1

Delete Command

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 Examples

DELETE FROM Customers WHERE Name = 'Inductive Automation'

DELETE FROM Customers WHERE id < 538