Check out helpful pointers when dealing with SQL.
Does Tableland support foreign keys?
No, not at this time as there are potential determinism issues involved.
Can I use subqueries in write statements?
Only "flattened" subqueries are supported within
INSERT statements but are not possible in
UPDATES. For read queries, nesting subqueries is perfectly acceptable. See the docs for more info.
Does Tableland support numbers with decimals values?
No, because floating points are hard to deal with in decentralized systems due to non-deterministic behavior. So, blockchains and related infrastructure have to adopt whole integer values and arithmetic, instead.
Does Tableland support types for dates or times?
No, because these lead to non-deterministic behavior. But, you are free to store these values as
INTEGER types, and the custom
TXN_HASH() functions can help a bit here, too.
Can I update more than one table in a single transaction?
Yes, mutating queries (inserts, updates, deletes) can touch one or more tables at a time. Keep in mind that if one of the statements fail, they all fail.
Are all SQLite functions supported?
No—and read the SQL functions docs for details!
How do I escape single quotes within a SQL string literal?
SQL string literals (e.g., a table with a TEXT value) are wrapped single quotes. In order to use a single quote within the string literal, double the single quote that must be escaped:
INSERT INTO escape_table (id, VALUES (0, 'My string''s escaping!')
What's the difference between single and double quotes in SQL?
A simple rule to remember which to use and when:
- [S]ingle quotes are for [S]tring Literals
- [D]ouble quotes are for [D]atabase IdentifiersSourced from StackOverflow
"my_table" ("id", "val")
(0, 'Bobby Tables');
Can I use lowercase letters for keywords?
It doesn't matter if you use all caps or all lowercase—either way is perfectly acceptable! For example,
select * from my_table; is further parsed and works just the same as
SELECT * FROM my_table;.
What are some useful tools and resources to learn and work with SQL?
The following is a list of cool and useful SQL-related tools. These are not affiliated with Tableland but simply being shared to help developers using the protocol:
- Mock SQL data ⇒ https://www.mockaroo.com/